Codeigniter JOIN (SELECT query

Question

Can I create the following query using the 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 

Answer 1

Well, there are several ways to do this. One way is hacking.

How can I rewrite this SQL code in Active Records CodeIgniter?

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'); 

A few words about this.
You must use the sentence in the subqueries because get starts the request.
In codeigniter 2, _compile_select and _reset_select cannot be accessed because they are protected by methods.
You may need to remove the keyword before both methods in the system / database / DB _active_rec.php

This article is also helpful.

Answer 2

in CI3, just use the fourth parameters to escape

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

Remember to avoid the parameters in your subquery to avoid SQL injection.

Answer 3

this library can help you use subqueries with the query builder, look at the document of this library

helper query with query builder