Flask-SQLAlchemy Left Outer Join Filtered Query

Question

Initially I was attempting to do a 'Right Outer Join' but once I found out that wasn't supported I began working on coming from the left. However, I'm having trouble figuring out exactly how to write what I need. Essentially I have two tables, Table_1 and Table_2 and I need all rows from Table_1 where column_c is equal to 1. Additionally I need all rows from Table_2 where column_b is not already in Table 1. Visually it looks something like this:

**Table_1**
column_a ( a and b are the
column_b ( primary key.
column_c

**Table_2**
column_b

This is how I would write it in SQL:

SELECT *
FROM (SELECT * FROM Table_1 WHERE column_a = 123) t1
RIGHT OUTER JOIN Table_2 t2 ON t1.column_b = t2.column_b
WHERE t1.column_c = 1 or t1.column_c is NULL;

SELECT *
FROM Table_2 t2
LEFT OUTER JOIN (SELECT * FROM Table_1 WHERE column_a = 123) t1
ON Table_2 t2 ON t1.column_b = t2.column_b
WHERE t1.column_c = 1 or t1.column_c is NULL;

This is what I have in Flask-SQLAlchemy form, it's important to note this is a method in the db.Model Class of Table_2.

def all_exclude(self, column_a):
    return self.query.outerjoin(
        Table_1, 
        Table_1.column_b==Table_2.column_b).filter(or_(
            Table_1.column_c==None,
            and_(Table_1.column_c==1, 
                 Table_1.column_a==column_a))).all()

Unfortunately, I wasn't thinking about it when I wrote it and it wouldn't really work that way since I can't call the method from the class form. I'd have to do it after initializing a query which would only be from a single row, which is not what I need. I understand I could run it as a query like so:

Business.query.outerjoin(
    Table_1, 
    Table_1.column_b==Table_2.column_b).filter(or_(
        Table_1.column_c==None,
        and_(Table_1.column_c==1, 
             Table_1.column_a==column_a))).all()

But I'm trying to keep my classes separate for OOP purposes, but even then I don't think that would work since technically the filter isn't done before the join. Maybe the solution is easier than I think but I can't quite wrap my head around it. Thank you in advance!

Answer 1

Based on your comment this should answer your question:

SELECT Table_1.column_a, Table_1.column_b
  FROM Table_1 
 WHERE Table_1.column_a = 123
   AND Table_1.column_c = 1
 UNION
SELECT Table_2.column_a, Table_2.column_b /* I'm assuming these columns exist in Table_2. Make sure these columns are same as selected columns from Table_1 */
  FROM Table_2
 WHERE NOT EXISTS (SELECT 1 FROM Table_1 
                    WHERE Table_1.column_b = Table_2.column_b 
                      AND Table_1.column_a = 123);

This translates in Python SQLAlchemy to:

from sqlalchemy import exists

query_1 = (db.session.query(Table_1)
           .with_entities(Table_1.column_a, Table_1.column_b)
           .filter(Table_1.column_a == 123)
           .filter(Table_1.column_c == 1)

query_2 = (db.session.query(Table_2)
           .with_entities(Table_2.column_a, Table_2.column_b)
           .filter(
               ~exists().where(Table_1.column_b == Table_2.column_b)
              )
           )
query = query_1.union(query_2).all()