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.

lcssanches · Accepted Answer · 2019-04-11 13:34:51Z

2

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.

answered Apr 11, 2019 at 13:34

lcssanches

1,03414 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mickmackusa Over a year ago

Relevant reading: Implement SELECT SUM() query using Codeigniter select_sum() active record method and return the value

Naveen M · Accepted Answer · 2019-04-12 04:55:56Z

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, 2019 at 4:55

answered Apr 11, 2019 at 9:24

Naveen M

893 bronze badges

2 Comments

romal tandel Over a year ago

this is what i thought of. for now i'll use it.

lcssanches Over a year ago

@romaltandel This answer is wrong, get() method will actually run the query. See my answer.

Shahriar63 · Accepted Answer · 2019-04-11 09:50:29Z

-1

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.

answered Apr 11, 2019 at 9:50

Shahriar63

581 silver badge8 bronze badges

Collectives™ on Stack Overflow

Write query with subquery in CodeIgniter Query Builder?

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related