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
Add a comment
|
4 Answers
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.
7 Comments
zb'
I asked for CI Query Builder not raw query, i can run raw query o'c,
Muhammad Raheel
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?
zb'
and why you placed the worse answer on this ? 2nd link looks good, may be it is best way to do that
Muhammad Raheel
if you can't understand the answer it does not mean it is worse it has been implementation of the 2nd link.
zb'
thanks for notes, but I'm on CI3, it have
$this->db->get_compiled_select() and $this->db->reset_query() |
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.
1 Comment
zb'
by CI Query Builder class
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
2 Comments
zb'
codeigniter still alive ? I asked question more than 10 years ago, right before it was discontinued...
mickmackusa
There are still projects running on it, but it has dwindling market share with the popularity of Laravel. I mostly necropost nowadays because there is plenty of outdated advice on Stack Overflow.
this library can help you to use sub queries with query builder have look on the doc of this library
