+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Development (https://forum.codeigniter.com/forumdisplay.php?fid=6)
+--- Forum: CodeIgniter 3.x (https://forum.codeigniter.com/forumdisplay.php?fid=17)
+--- Thread: Query Builder Nest Joins at the end of the Query (/showthread.php?tid=81628)
Query Builder Nest Joins at the end of the Query - ElTomTom - 03-30-2022
I'm creating a query with Query Builder for PostgreSQL, my query has tables and joins for each table.
It so happens that PostgreSQL requires that joins that use a table must be below it in the Query. Like(example 1).
I set up my query in CodeIgniter (example 2), however, it always nests the JOIN at the end, generating an SQL that has an error for PostgreSQL (example 3).
How can I fix this without having to create the SQL directly in CodeIgniter?
Example 1:
Code:
SELECT
...
FROM
table1 t
LEFT OUTER JOIN join00 p ON p.id = t.id
LEFT OUTER JOIN join01 ta ON ta.id = t.id,
tabl2 hs
LEFT OUTER JOIN join1 pstart ON pstart.id = hs.id
LEFT OUTER JOIN join2 pend ON pend.id = hs.idExample 2:
PHP Code:
$this->db->from('table1 t');
$this->db->join('join00 p', 'p.id = t.id', 'LEFT');
$this->db->join('join01 ta', 'ta.id = t.id', 'LEFT');
$this->db->from('tabl2 hs');
$this->db->join('join1 pstart', 'pstart.id = hs.id', 'LEFT');
$this->db->join('join2 pend', 'pend.id = hs.id', 'LEFT');
Example 3:
Code:
SELECT
*
FROM
"table1" "t",
"tabl2" "hs"
LEFT JOIN "join00" "p" ON "p"."p.id" = "t"."p.id"
LEFT JOIN "join01" "ta" ON "ta"."p.id" = "t"."p.id"
LEFT JOIN "join1" "pstart" ON "pstart"."p.id" = "hs"."p.id"
LEFT JOIN "join2" "pend" ON "pend"."p.id" = "hs"."p.id"RE: Query Builder Nest Joins at the end of the Query - iRedds - 03-30-2022
It's impossible.
In version 4.2, subqueries will become available in the FROM clause.
If your query can work through subqueries, then perhaps this will solve your problem.
Code:
SELECT * FROM (SELECT * FROM t1 JOIN t2) as t3, (SELECT * FROM t4 JOIN t5) as t6