translate from SQL to SQLAlchemy

Question

i have the following sql script.

SELECT * FROM movies LEFT JOIN possession ON movies.id = possession.movie_id AND possession.master_id = ? WHERE possession.id is NULL ORDER BY movies.id DESC

and need help to illustrate in sqlalchemy. Please can someone help me?

The following doesn't work:

movies = movie.Movie.query.join(possession.Possession, movie.Movie.id == possession.Possession.movie_id)\ .filter(possession.Possession.master_id == session.get('userId'))\ .filter(not_(possession.Possession.id)).order_by(movie.Movie.id.desc()).all()

Thank you very much!

Cheers

You have master_id in your SQL query, but user_id in your SQLAlchemy query. Is that intentional? — Blender
– Blender, Commented Apr 21, 2013 at 6:47

van · Accepted Answer · 2013-04-21 09:30:53Z

The most direct way to translate your SQL query is the ORM query below:

qry = (session.query(Movie) .outerjoin(Possession, and_(Movie.id == Possession.movie_id, Possession.master_id == master_id)) .filter(Possession.id == None) .order_by(desc(Movie.id)) ) movies = qry.all()

produces:

SELECT movies.id AS movies_id, movies.name AS movies_name FROM movies LEFT OUTER JOIN possessions ON movies.id = possessions.movie_id AND possessions.master_id = :master_id_1 WHERE possessions.id IS NULL ORDER BY movies.id DESC

I also think this would be the faster implementation compared to an alternative of using any (see below), which is more concise, but the SQL it produces is not. This also assumes there is a relationship Movie.possessions = relationship(Possession):

qry = (session.query(Movie) .filter(~Movie.possessions.any(Possession.master_id == master_id)) .order_by(desc(Movie.id)) ) movies = qry.all()

produces:

SELECT movies.id AS movies_id, movies.name AS movies_name FROM movies WHERE NOT (EXISTS (SELECT 1 FROM possessions WHERE movies.id = possessions.movie_id AND possessions.master_id = :master_id_1)) ORDER BY movies.id DESC

Collectives™ on Stack Overflow

translate from SQL to SQLAlchemy

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related