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!

Over-complicating is the word in need here! First of all, why are you writing your `RIGHT OUTER JOIN` the way you are doing it? Do you also need to select only those rows from `Table_1` whose `column_a = 123`? And why are you using `or t1.column_c IS NULL`, Don't you want only rows from `Table_1` which have `column_c = 1`? — Sameer Mirji, Jan 31 '16 at 08:36
@SameerMirji Well I need all rows from `Table_1` where `column_a = 123` and `column_c = 1`. Additionally, I need all rows from `Table_2` that do not have a `column_b` that is part of the primary key in `Table_1` to 123. It's a many-to-many relationship where `Table_1` is the association table for `Table_2` and another table. The primary key is a combination of `Table_2` (`column_b`) and the other table (`column_a`). Essentially I'm trying to avoid presenting a row that could cause a duplicate entry if it was selected. — ThatTechGuy, Feb 01 '16 at 23:09

score 6 · Accepted Answer · answered Feb 02 '16 at 05:11

6

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()

answered Feb 02 '16 at 05:11

Sameer Mirji

2,135
16
28

1

`column_a` is not included in `Table_2` only `column_b` is. However, I think it should still mostly work. I'll play with it a bit and give it a shot. However, I'd prefer if we could do this with a join like shown in my standard SQL. – ThatTechGuy Feb 02 '16 at 18:19
What is the reason you need the query with a join? Since you need exclusive rows from each of the tables (w.r.t. column_b), its not a good way achieve that with a join. The above union query has minimum CPU Cost and number of queries executed for this is only 1. – Sameer Mirji Feb 03 '16 at 03:37
Really? Oh well then that's actually better. For some reason I thought the join with the inline view would have better performance. But I'll take your word on the union and give it a shot. Thanks! – ThatTechGuy Feb 03 '16 at 13:12
@ThatTechGuy: You can accept the answer if you see that your problem is solved. ;) – Sameer Mirji Feb 03 '16 at 13:49
I certainly will, I just have to plug it in and see if it returns the correct records :) – ThatTechGuy Feb 03 '16 at 13:57

Flask-SQLAlchemy Left Outer Join Filtered Query

1 Answers1