MYSQL PeeWee Full Join without RawQuery

Question

I am using PeeWee with MySQL. I have two tables that need a full join to keep records from both left and right sides. MySQL doesn't support this directly, but I have used "Method 2" in this helpful artice - http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/ to create a Full Join SQL statement that seems to work for my data. It requires a "UNION ALL" of "LEFT OUTER JOIN" and "RIGHT OUTER JOIN", using an excusion of duplicate data in the 2nd result set.

I'm matching up backup-tape barcodes in the two tables. SQL

SELECT * FROM mediarecall AS mr
LEFT OUTER JOIN media AS m ON mr.alternateCode = m.tapeLabel
UNION ALL
SELECT * FROM mediarecall AS mr
RIGHT OUTER JOIN media AS m ON mr.alternateCode = m.tapeLabel
WHERE mr.alternateCode IS NULL

However, when I come to bring this into my python script using PeeWee, I discovered that there doesn't seem to be a JOIN.RIGHT_OUTER to allow me to re-create this SQL. I have used plently of JOIN.LEFT_OUTER in the past, but this is the first time I have needed a Full Join.

I can make PeeWee work with a RawQuery(), of course, but I'd love to keep my code looking more elegant if I can.

Has anyone managed to re-create a Full Join with MySQL and PeeWee without resorting to RawQuery?

I had envisaged something like the following (which I know is invalid):-

left_media = (MediaRecall
             .select()
             .join(Media,JOIN.LEFT_OUTER, 
                   on=(MediaRecall.alternateCode == Media.tapeLabel)
                   )
             )

right_media = (MediaRecall
             .select()
             .join(Media,JOIN.RIGHT_OUTER, 
                   on=(MediaRecall.alternateCode == Media.tapeLabel)
                   )
             )
             .where(MediaRecall.alternateCode >> None) # Exclude duplicates

all_media = ( left_media | right_media) # UNION of the 2 results, which I
                                        # can then use .where(), etc on

Answer 1

You can add support for right outer:

from peewee import JOIN
JOIN['RIGHT_OUTER'] = 'RIGHT OUTER'

Then you can use JOIN.RIGHT_OUTER.