Query with JOIN on a subquery using CodeIgniter query builder methods

Question

It is possible to generate the following query using CI's query builder methods?

SELECT name 
FROM table1 t1 
JOIN 
     (SELECT ID FROM table2 ORDER BY id LIMIT 5) t2 
     ON t2.id=t1.t2_id 
WHERE t1.id>5

Priestley

Answer 1

Well there are a couple of ways of doing it. One way is here which is a hack.

How can I rewrite this SQL into CodeIgniter's Active Records?

This other way is very simple.

$this->db
        ->select('ID')
        ->from('table2')
        ->order_by('id')
        ->limit('5');   

$subquery = $this->db->_compile_select();

$this->db->_reset_select(); 

$query  =       $this->db
                    ->select('t1.name')
                    ->from('table1 t1 ')
                    ->join("($subquery)  t2","t2.id = t1.t2_id")
                    ->get('table1 t1');

Some point about it.
You are bound to use from clause in subqueries because get runs the query.
In codeigniter 2 _compile_select and _reset_select can not be accessed because they are protected methods.
You may have to remove the keyword before both methods in system/database/DB_active_rec.php

This article is useful too.

Season

I asked for CI Query Builder not raw query, i can run raw query o'c, – Sherellsherer

Well i have added the links of two articles which you haven't even seen they tell exactly how to do it. One is my own answer. Care to have a look? – Season

and why you placed the worse answer on this ? 2nd link looks good, may be it is best way to do that – Sherellsherer

if you can't understand the answer it does not mean it is worse it has been implementation of the 2nd link. – Season

implementation of 2nd link here your sample is just simple query run... – Sherellsherer

yes, just after you fix the answer, i can't accept the answer without it – Sherellsherer

thanks for notes, but I'm on CI3, it have $this->db->get_compiled_select() and $this->db->reset_query() – Sherellsherer

Answer 2

in CI3, simply use the fourth parameters for escape

$this->db->from('table')
->join('SELECT id from table2 where something=%s) as T2'),'table.id=T2.id', 'LEFT',NULL)
->get()->row();

Don't forget to escape parameters in your subquery to avoid SQL Injection.

Aladdin

by CI Query Builder class – Sherellsherer

Answer 3

this library can help you to use sub queries with query builder have look on the doc of this library

sub query with query builder

Presidio

would be usefull if it was implemented as group_start() – Sherellsherer

well you can add this feature to this library and share with developer on github will be useful for other developers too – Presidio

will do when I got a time for that and know CI more than now – Sherellsherer

Answer 4

The subquery can be safely built as a compiled select string which is fed to the parent query build.

get_where() consolidates what ordinarily would require a from() call, a where() call, and a get() call.

$sub = $this->db
    ->select('id')
    ->order_by('id', 'ASC')
    ->limit(5)
    ->get_compiled_select('table2');

return $this->db
    ->select('t1.name')
    ->join("($sub) t2", 't2.id = t1.t2_id')
    ->get_where('table1 t1', ['t1.id >' => 5])
    ->result();

Depending on database dialect/driver, the rendered SQL should resemble:

SELECT `t1`.`name`
FROM `table1` `t1`
JOIN (
    SELECT `id`
    FROM `table2`
    ORDER BY `id` ASC
    LIMIT 5
) `t2` ON `t2`.`id` = `t1`.`t2_id`
WHERE `t1`.`id` > 5

Cannon