phpcodeigniterjoinsubqueryquery-builder

Query with JOIN on a subquery using CodeIgniter query builder methods


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

Solution

  • 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