Write query with subquery in CodeIgniter Query Builder?

Question

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.

score 2 · Accepted Answer · answered Apr 11 '19 at 13:34

DO NOT USE get() followed by last_query() because get() will actually run the query on database.

Instead, use get_compiled_select() that will returns the query without running it.

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

By default get_compiled_select() will reset the query builder.

See the docs.

Naveen M · Answer 2 · 2019-04-12T04:55:56.687

1

Kindly check with this, hope will help you

$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');
// $this->db->get();
$lastquery = $this->db->last_query();

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

edited Apr 12 '19 at 04:55

answered Apr 11 '19 at 09:24

Naveen M

89
3

this is what i thought of. for now i'll use it. – romal tandel Apr 11 '19 at 10:00
1

@romaltandel This answer is wrong, `get()` method will actually run the query. See my answer. – lcssanches Apr 11 '19 at 13:36

score -1 · Answer 3 · answered Apr 11 '19 at 09:50

If your mysql query is ok, please use this method for run complicated query like this.

$sql = "Your Query";
$qr = $this->db->query($sql);
return $qr->row();

If still you are not getting the output please check your subquery first inside the from clause.

Write query with subquery in CodeIgniter Query Builder?

3 Answers3