FuelPHP Forums

webdev25

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')
));

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' )
));

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

Harro

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 <environment>/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();

Harro

Edit: re-reading your post again, it doesn't seem to be a collating sequence issue, it's simply not ordering at all, so I assume your array syntax is wrong.

webdev25

Thank you so much Harro!!

The collating for the DB was utf8_bin, i've changed it to utf8_general_ci (i normally use this, i must have missed it when i setup my DB in this case though) and everything is ordering how i expected it to.

I did not know the _ci meant case insensitive, so i've learnt something new today :)

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion