11

It is possible to generate the following query by CI Query Builder class ?

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
zb'
  • 8,071
  • 4
  • 41
  • 68

3 Answers3

22

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.

Community
  • 1
  • 1
Muhammad Raheel
  • 19,823
  • 7
  • 67
  • 103
  • I asked for **CI Query Builder** not raw query, i can run raw query o'c, – zb' Jan 10 '13 at 06:32
  • 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? – Muhammad Raheel Jan 10 '13 at 06:33
  • and why you placed the worse answer on this ? 2nd link looks good, may be it is best way to do that – zb' Jan 10 '13 at 06:41
  • if you can't understand the answer it does not mean it is worse it has been implementation of the 2nd link. – Muhammad Raheel Jan 10 '13 at 06:44
  • implementation of 2nd link [here](http://codepad.org/KvCIQoEt) your sample is just simple query run... – zb' Jan 10 '13 at 06:57
  • yes, just after you fix the answer, i can't accept the answer without it – zb' Jan 10 '13 at 07:02
  • 1
    thanks for notes, but I'm on CI3, it have `$this->db->get_compiled_select()` and `$this->db->reset_query()` – zb' Jan 10 '13 at 07:17
5

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.

Zoé R.
  • 93
  • 1
  • 9
0

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

umefarooq
  • 4,540
  • 1
  • 29
  • 38