sqlite3 and result ordering according to non-ASCII collations

Try:

PHP Code:

        $db->connID->createCollation('LT', function ($a, $b) {
            return strcoll($a, $b);
        }); 


Voila!

so I added these three calls to BaseController:

PHP Code:

        setlocale(LC_ALL, 'lt_LT.UTF-8'); 
        // before I run this the locale was 'lt' and the ordering did not work

        db_connect()->connID->createCollation('LT', function ($a, $b) {
            return strcoll($a, $b);
        }); 

        db_connect()->connID->createFunction('LT_LOWER', function ($str) {
            return mb_strtolower($str);
        }); 


And then the controller method to test:

PHP Code:

       public function collate()
    {
        echo '<pre>';
        $db = db_connect();


        echo 'ORDERING: ' . PHP_EOL;
        $query = $db->query('SELECT last_name FROM users ORDER BY last_name COLLATE LT DESC');
        $users = $query->getResultArray();
        foreach ($users as $user) {
            echo $user['last_name'] . PHP_EOL;
        }

        echo 'CASE-INSENSITIVE LIKE: '  . PHP_EOL . PHP_EOL;
        $query = $db->query('SELECT last_name FROM users WHERE LT_LOWER(last_name) LIKE "%žič%"');
        $users = $query->getResultArray();
        foreach ($users as $user) {
            echo $user['last_name'] . PHP_EOL;
        }

        echo ' PHP_EOL . SAME USING QUERY BUILDER, NOT WORKING: '  . PHP_EOL . PHP_EOL;
        $builder = $db->table('users');
        $builder->select('last_name');
        $builder->orderBy('last_name', 'COLLATE LT DESC'); 
        $query = $builder->get(); 
        $users = $query->getResultArray();

        foreach ($users as $user) {
            echo $user['last_name'] . PHP_EOL;
        }



        echo PHP_EOL . 'CASE-INSENSITIVE LIKE WITH BUILDER: '  . PHP_EOL . PHP_EOL;
        $builder = $db->table('users');
        $builder->select('last_name');
        $builder->like('LT_LOWER(last_name)', 'žič', 'both'); 
        $query = $builder->get(); 

        $users = $query->getResultArray();

        foreach ($users as $user) {
            echo $user['last_name'] . PHP_EOL;
        }

    } 


And I see that both ordering and case - insensitive search with utf8 characters is working.

The last question would be - how do I integrate COLLATE LT into query builder. So far I failed (see the third code block, which does not order).

==

Donatas G.