Codeigniter：ORDER BY CASE查询中出错 [英] Codeigniter : Error in ORDER BY CASE query

问题描述

这是我在 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);

打印出来

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

在打印查询中， CASE <

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

如何解决这个问题？

submit_to字段的结构是

Structure of submit_to field is

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

推荐答案

CodeIgniter文档指示在Active Record类中不支持by子句。我建议重构SQL调用，以便case语句是select子句的一部分。像下面这样应该做的。

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

这篇关于Codeigniter：ORDER BY CASE查询中出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！