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
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