时间戳

首页 > 解决方案 > Write query with subquery in CodeIgniter Query Builder?

php - Write query with subquery in CodeIgniter Query Builder?

问题描述

I have the following query which has an issue with Query Builder.

SELECT Sum(TEMP.total) AS total_amount
FROM   (SELECT Ifnull(t3.amount, t1.amount) AS total
        FROM   table1 AS t1
               LEFT JOIN table2 AS t2
                      ON t2.class = t1.class
               LEFT JOIN table3 AS t3
                      ON t3.student = t2.student
                         AND t3.type = t1.type) AS TEMP

Is there any way to do it with Query Builder? I'm currently using this method.

标签： phpcodeigniterquery-builder

解决方案

DO NOT USEget()后跟，last_query()因为get()实际上会在数据库上运行查询。

相反，使用get_compiled_select()它将返回查询而不运行它。

$this->db->select('IFNULL(t3.amount, t1.amount) as total');
$this->db->from('table1 as t1');
$this->db->join('table2 as t2', 't2.class = t1.class', 'LEFT');
$this->db->join('table3 as t3', 't3.student = t2.student AND t3.type = t1.type', 'LEFT');
$subquery = $this->db->get_compiled_select();


$this->db->select('SUM(TEMP.total) as total_amount');
$this->db->from('('.$subquery.') as TEMP');
$result = $this->db->get()->result_array();

默认情况下get_compiled_select()将重置查询生成器。

请参阅文档。

推荐阅读