Doing an EXCEPT SELECT in SQLAlchemy

Question

I want to run an SQLAlchemy query that pulls back a list of movie IDs but it should exclude some IDs if they are found in an alternate table.

I can do this in SQL (I'm using SQLlite 3.34.1) but simply using EXCEPT followed by a SELECT clause.

Here is what I'm trying to do. It's coming back with hundreds of results rather than 8 or so results it should find which I dont understand.

movies = db.session.query(Movies)

user_has_seen = db.session.query(User.movie_id)

final_results = db.session.query(movies).filter(~User.movie_id.in_(user_has_seen))

EDIT:

I have now created a slightly more complex query to the above one. On the first query I want to join the results to another table and on the second query I want to add a filter. I'm looking for the same outcome though; I want to select all the movies in the movies table and exclude the ones that the user has blacklisted.

Here is what I'm currently trying, but it is throwing up the following Cartesian warning;

SAWarning: SELECT statement has a cartesian product between FROM element(s) "movies" and FROM element "anon_1".  Apply join condition(s) between each element to resolve.

Here is the SQLAlchemy query:

full_movies = db.session.query(movies).join(Sometable, movies.id == sometable.id).subquery()
user_has_seen = db.session.query(User.movie_id).filter(user.seen == True).subquery() 
final_results = db.session.query(full_movies).filter(~Movies.movie_id.in_(user_has_seen))

When I remove the subqueries I get the following error;

sqlalchemy.exc.ArgumentError: Column expression or FROM clause expected, got  object resolved from  object. To create a FROM clause from a  object, use the .subquery() method. (Background on this error at: https://sqlalche.me/e/14/89ve)

I'm so lost

user_5320906 · Answer

You need to filter on the Movies model rather than the User model to avoid getting the cartesian product of the queries. This should work:

user_has_seen = db.session.query(User.movie_id)
final_results = db.session.query(Movies).filter(~Movies.movie_id.in_(user_has_seen))

The SQLAlchemy ORM does support EXCEPT, but the constraints on matching column types apply. This would get you only the movie ids:

user_has_seen = db.session.query(User.movie_id)
final_results = db.session..query(Movies.movie_id).except_(q1)

Note that the SQLAlchemy ORM performs EXCEPTs in nested SELECTs:

SELECT anon_1.movies_movie_id AS anon_1_movies_movie_id 
  FROM (
    SELECT movies.movie_id AS movies_movie_id 
      FROM movies 
    EXCEPT 
    SELECT users.movie_id AS users_movie_id
      FROM users
  ) AS anon_1

Doing an EXCEPT SELECT in SQLAlchemy

1 Answers