代码点火器中的条件时的情况

3gtaxfhh 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(327)

如何在代码点火器中使用条件？我必须将此查询转换为codeigniter格式请帮助。

$search = "%$search%";
$sql = "SELECT tickerr_tickets.*, tickerr_users2.name as agent_name, tickerr_ticket_departments.name as department_name, CASE WHEN guest_name != '' THEN guest_name ELSE tickerr_users1.name END AS client_final_name FROM tickerr_tickets INNER JOIN tickerr_ticket_departments ON tickerr_tickets.department=tickerr_ticket_departments.id  LEFT JOIN tickerr_users tickerr_users1 ON tickerr_tickets.userid = tickerr_user1.id LEFT JOIN tickerr_users tickerr_users2 ON tickerr_tickets.agentid = tickerr_users2.id WHERE `status`=1 AND `agentid`!=0 (`tickerr_tickets`.`id` LIKE ? OR `tickerr_tickets`.`date` LIKE ? OR `last_update` LIKE ? OR `subject` LIKE ? OR `content` LIKE ? OR `tickerr_ticket_departments`.`name` LIKE ? OR (CASE WHEN guest_name != '' THEN guest_name ELSE tickerr_users.name END) LIKE ? OR `tickerr_users`.`username` LIKE ?) ORDER BY `$order_by` $order LIMIT $starting,$rows";

$query = $this->db->query($sql, array($search, $search, $search, $search, $search, $search, $search, $search));

mysql codeigniter

来源：https://stackoverflow.com/questions/50128209/case-when-condition-in-code-igniter

1条答案

按热度按时间

vc6uscn91#

除此之外，您的本机查询是错误的，因为没有 tickerr_users table -你的别名叫做 tickerr_users1 以及 tickerr_users2 ，一个合适的解决方案是使用内置的查询生成器
像下面这样的方法应该能奏效

$query = $this->db
    ->select('tickerr_tickets.*, tickerr_users2.name as agent_name, tickerr_ticket_departments.name as department_name, CASE WHEN guest_name != \'\' THEN guest_name ELSE tickerr_users1.name END AS client_final_name', false)
    ->from('tickerr_tickets')
    ->join('tickerr_ticket_departments', 'tickerr_tickets.department=tickerr_ticket_departments.id','inner')
    ->join('tickerr_users as tickerr_users1', 'tickerr_tickets.userid = tickerr_user1.id', 'left')
    ->join('tickerr_users as tickerr_users2', 'tickerr_tickets.agentid = tickerr_users2.id', 'left')
    ->where('status', 1)
    ->where('agentid !=', 0)
    ->group_start()
        ->like('tickerr_tickets.id', $search)
        ->or_like('tickerr_tickets.date', $search)
        ->or_like('tickerr_tickets.date', $search)
        ->or_like('tickerr_tickets.last_update', $search)
        ->or_like('tickerr_tickets.subject', $search)
        ->or_like('tickerr_tickets.content', $search)
        ->or_like('tickerr_ticket_departments.name', $search)
        ->or_like('tickerr_users1.username', $search)
        ->or_where('(CASE WHEN guest_name != \'\' THEN guest_name ELSE tickerr_users.name END) LIKE '.$this->db->escape('%'.$search.'%').'', NULL, false)
    ->group_end()
    ->order_by($order_by)
    ->limit($rows, $starting)
    ->get();

赞(0）回复(0）举报 2021-06-21

我来回答

代码点火器中的条件时的情况

1条答案

相关问题

热门标签

最新问答