FuelPHP's ORM Package isn't ordering items correctly

Question

I have a table with a 'title' field, I am trying to get all rows in this table, and order them by the title ASC. The four records I am testing with have the titles:

Another test

My New Page

NEW

aaa

This is also the order the rows are returned which is incorrect. 'aaa' should be at the top of the list. I assume the problem is with the lower and upper case characters.

I want to order by LOWER(title) rather then just title however I am having issues making this work in FuelPHP.

The code I'm using to select the records is:

$pages = Model_pages::find('all', array(
    'order_by' => array('title' => 'asc')
));

How do I make the code above order items regardless of their 'case'?

EDIT:

@Uru, thanks for the feedback :) I have tried the following code, all variations result in SQL or PHP errors:

$pages = Model_pages::find('all', array(
    'order_by' => \DB::expr('LOWER(title) ASC')
));

$pages = Model_pages::find('all', array(
    'order_by' => \DB::expr('LOWER(title)')
));

$pages = Model_pages::find('all', array(
    'order_by' => \DB::expr('LOWER(title) ASC')
));

$pages = Model_pages::find('all', array(
    'order_by' => array( \DB::expr('LOWER(title)') )
));

$pages = Model_pages::find('all', array(
    'order_by' => array( \DB::expr('LOWER(title) ASC') )
));

$pages = Model_pages::find('all', array(
    'order_by' => array( \DB::expr('LOWER(title)') => 'ASC' )
));

Answer 1

The issue was with the database collation in MySQL, changing it to utf8_general_ci resolved the issue. Thanks to Harro over at the FuelPHP Forums for answering this one:

Are you using MySQL? And if so, what collating sequence do you use for your table?

MySQL as collating sequence with "ci" at the end (like utf8_general_ci), which are "case insensitive". Which means "aaa" and "AAA" are sorted equally.

If you want to know what SQL is executed exactly, set 'profiling' to true in your config.php, and set it to true in your database definition, in /db.php.

You will get a little black profiler in the lower right corner of your page, and if you click on it, the profiler will open, and it will show you all SQL queries executed.

There is nothing wrong with you ORM code as far as I can see, but I never use array notation. I would have used (which should generate the same SQL):

$pages = Model_pages::query()->order_by('title', 'asc')->get();

The full thread can be found here for anyone running into the same issue:

http://fuelphp.com/forums/discussion/13226/fuelphp039s-orm-isn039t-ordering-items-correctly

Answer 2

You should be able to use DB::expr('LOWER(title)') to escape expressions and use that instead of 'title'.

If this does not work and there is nothing in the documentation then this feature is currently not supported.