Codeigniter : Error in ORDER BY CASE query

Question

Here is my query in Codeigniter

$this->db->select('p.*,u.firstname, u.lastname,s.title AS industry, pt.type_name , al.length_value',FALSE);
$this->db->from($this->_tbl_projects . ' as p');
$this->db->join($this->_tbl_client_details . ' as c', 'c.id = p.client_id', 'left');
$this->db->join($this->_tbl_users . ' as u', 'u.id = c.user_id', 'left');
$this->db->join($this->_tbl_project_types . ' as pt', 'pt.project_type_id = p.project_type_id', 'left');
$this->db->join($this->_tbl_specializations . ' as s', 's.specialization_id = p.specialization_id', 'left');
$this->db->join($this->_tbl_article_length . ' as al', 'al.article_length_id = p.article_length_id', 'left');
$this->db->order_by("CASE p.submit_to
                                    WHEN '' THEN 0
                                    WHEN 'writer' THEN 1
                                    ELSE 2
                                END, p.request_end_date asc",FALSE);

It Print out

SELECT p.*, u.firstname, u.lastname, s.title AS industry, pt.type_name, al.length_value 
FROM (`projects` as p) 
LEFT JOIN `client_details` as c ON `c`.`id` = `p`.`client_id` 
LEFT JOIN `users` as u ON `u`.`id` = `c`.`user_id` 
LEFT JOIN `project_types` as pt ON `pt`.`project_type_id` = `p`.`project_type_id` 
LEFT JOIN `specializations` as s ON `s`.`specialization_id` = `p`.`specialization_id` 
LEFT JOIN `article_length` as al ON `al`.`article_length_id` = `p`.`article_length_id` WHERE `p`.`client_id` = '26' AND `p`.`status` IN (2, 3) 
ORDER BY 
        `CASE` p.submit_to 
            WHEN '' THEN 0 
            WHEN 'writer' THEN 1 
            ELSE 2 
         END, `p`.`request_end_date` asc

Here in printed query, CASE is printed by `CASE`, so sql throws error.

How can I solve it?

Structure of submit_to field is

  submit_to enum('','writer','students') NOT NULL

Answer 1

The CodeIgniter documentation indicates case statements within the order by clause isn't supported within the Active Record class. I'd recommend refactoring the SQL call so that the case statement is part of the select clause. Something like the following should do the trick.

$this->db->select("p.*,u.firstname, u.lastname,s.title AS industry, pt.type_name, al.length_value, CASE p.submit_to WHEN 'writer' THEN 2 WHEN 'students' THEN 1 ELSE 0 END AS ordered_submit_to",FALSE);
$this->db->from($this->_tbl_projects . ' as p');
$this->db->join($this->_tbl_client_details . ' as c', 'c.id = p.client_id', 'left');
$this->db->join($this->_tbl_users . ' as u', 'u.id = c.user_id', 'left');
$this->db->join($this->_tbl_project_types . ' as pt', 'pt.project_type_id = p.project_type_id', 'left');
$this->db->join($this->_tbl_specializations . ' as s', 's.specialization_id = p.specialization_id', 'left');
$this->db->join($this->_tbl_article_length . ' as al', 'al.article_length_id = p.article_length_id', 'left');
$this->db->order_by('ordered_submit_to', 'ASC');
$this->db->order_by('p.request_end_date', 'ASC');

Answer 2

I found a good solution in another answer on SO so in case you've landed on this page and haven't found that one, I'll re-post it here.

---

You are able to use a case statement if you wrap the case statement in parentheses.

$this->db->order_by("
    (CASE p.submit_to
        WHEN '' THEN 0
        WHEN 'writer' THEN 1
        ELSE 2
    END), 
    p.request_end_date asc"
);