Does CodeIgniter Datamapper ORM support a MySQL CASE statement in ORDER BY clauses?

Question

Using Datamapper 1.8.1-dev for PHP's CodeIgniter, I'm trying to build this query to prioritize one country name at top of the list:

SELECT * FROM countries
ORDER BY CASE name WHEN 'Australia' THEN 1 ELSE 2 END, name

PHP on my Country model (which extends Datamapper):

$countries = new Country();
$countries->order_by("CASE name WHEN 'Australia' THEN 1 ELSE 2 END, name");
$countries->get_iterated();

Datamapper instead parses "CASE" as a table name, building this syntax error query:

SELECT * FROM (`countries`)
ORDER BY `CASE` name WHEN 'Australia' THEN 1 ELSE 2 END, `countries`.`name`

I'm assuming the CASE case isn't being handled since this flow control statement was just added in MySQL 5? Are my only alternatives to either add an order_by column to the countries table or use a PHP sort function after database retrieval?

score 5 · Answer 1 · answered Dec 19 '12 at 15:27

I was able to use a case statement in an order by. You just need to wrap the case statement in parentheses. If you don't the word case is treated as a column name and back quoted.

$this->db->distinct(); 
$this->db->select('country');         
$this->db->from('distributors');
$this->db->where('country  != ', '');
$this->db->order_by("(CASE country WHEN 'UNITED STATES OF AMERICA' THEN '1' ELSE country END)");

score 1 · Accepted Answer · answered Aug 06 '12 at 02:27

Datamapper’s order_by() is just an alias for $this->db->order_by().

Unfortunately, as per CodeIgniter Documentation, the Database classes are the only ones that are non-extendable. You're going to have to get your hands dirty by modifying the core.

Head into system/database/DB_active_rec.php and search for the order_by() function. Add a third parameter to the function's function definition:

function order_by($orderby, $direction = '', $case = null)

Scroll to the bottom of the function... Just before assigning the $orderby_statement to $this->ar_orderby[] write:

if($case===true) $orderby_statement = "CASE " . $orderby_statement;

Save the file and head into application/libraries/datamapper.php and search for the order_by() function. Notice, it's just a wrapper!

Replace it with:

public function order_by($orderby, $direction = '', $case = null)
{
    $this->db->order_by($this->add_table_name($orderby), $direction, $case);
    //For method chaining
    return $this;
}

Now, in your controller, if you pass true as the third parameter to order_by(), and drop the CASE keyword from the first string param, you should get the correct syntax.

Hope this helps.

Was hoping to avoid editing the core library since this will affect sites across multiple servers, but I guess this is the best to be done at the moment. Later in the day I also discovered there's a similar problem with MySQL's `FIELD()` in an ORDER BY. e.g., `$status_ids = array(STATUS_PROCESSING, STATUS_AWAITING_SHIPPING, STATUS_SHIPPED); $statuses->order_by("FIELD(id, '" . implode("', '", $status_ids) . "')")` generates SQL ``ORDER BY FIELD(id, `'4'`, `'13'`, `'6')` `` For a spreading ORM, building non-typical queries is a bit of a rabble. — Derek MacDonald, Aug 07 '12 at 00:03
Datamapper ORM is incredibly useful for fairly simple querying. Perhaps you should fall back to CodeIgniter's native DB implementation for more complex queries. By the way, DM 2.0 is being developed, you can check it out on [github](https://github.com/WanWizard/Datamapper) and submit feature requests. — Jordan Arsenault, Aug 07 '12 at 00:17

score 0 · Answer 3 · answered Aug 17 '12 at 06:46

Since DataMapper doesn't support MYSQL's CASE keyword, the direct solution is to invoke SQL using the query() method:

$sql = "SELECT * FROM `countries` ORDER BY CASE `name` WHEN 'Australia' THEN 1 ELSE 2 END, `name`";
$countries = new Country();
$countries->query($sql);

Does CodeIgniter Datamapper ORM support a MySQL CASE statement in ORDER BY clauses?

3 Answers3

Linked