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.id
Example 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"