Does CodeIgniter Datamapper ORM support a MySQL CA

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?

Answer 1

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.

Answer 2

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

Answer 3

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