Welcome Guest, Not a member yet? Register   Sign In
Query Builder Nest Joins at the end of the Query
#1
Bug 

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"
Reply
#2

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
Reply




Theme © iAndrew 2016 - Forum software by © MyBB