How to union joins using Idiorm

Question

I am using Idiorm for my SQL querying, and need to make a union of a right and left join.

Here is my actual SQL statement, which works:

SELECT p.*, a1.*, a2.szFirstName AS a2FirstName, a2.szLastName AS a2LastName, 

a2.szEmail AS a2Email,
a3.szFirstName AS a3FirstName, a3.szLastName AS a3LastName, a3.szEmail AS a3Email
FROM propertySummary p
LEFT OUTER JOIN agents a1 ON p.lAgentID = a1.lAgentID
LEFT OUTER JOIN agents a2 ON p.lAgentID2 = a2.lAgentID
LEFT OUTER JOIN agents a3 ON p.lAgentID3 = a3.lAgentID

UNION

SELECT p.*, a1.*, a2.szFirstName AS a2FirstName, a2.szLastName AS a2LastName, a2.szEmail AS a2Email,
a3.szFirstName AS a3FirstName, a3.szLastName AS a3LastName, a3.szEmail AS a3Email
FROM propertySummary p
RIGHT OUTER JOIN agents a1 ON p.lAgentID = a1.lAgentID
RIGHT OUTER JOIN agents a2 ON p.lAgentID2 = a2.lAgentID
RIGHT OUTER JOIN agents a3 ON p.lAgentID3 = a3.lAgentID

How can I rewrite this using an Idiorm query? I can't seem to find this in their documentation. Any suggestions would be greatly appreciated. Thanks in advance.

Link to Idiorm Documentation

From a github search, it seems no one has ever opened an issue about "union" and "union" is never mentioned in the code base. This seems like an oversight. https://github.com/j4mie/idiorm/search?utf8=%E2%9C%93&q=union I've opened an issue and I'll be interested to know what the developers say. — Martin Burch, Oct 21 '15 at 22:18
As far as I know it is feature close - there won't come any new features - only bugfixes thought. — Jurik, May 18 '16 at 13:31

score 0 · Answer 1 · answered May 18 '16 at 13:54

From the closed github issue:

Do it with raw_query. Example:

$sql = <<<SQL
    SELECT a.* FROM table_name_a a
    UNION ALL
    SELECT b.* FROM table_name_b b
SQL;

$recs = ORM::for_table('table_name_here_a')
    ->raw_query($sql)
    ->find_array();

How to union joins using Idiorm

1 Answers1