Sqlalchemy subquery

Question

I'm new in sqlalchemy, please help. I have this SQL query:

SELECT * FROM films
WHERE (
    SELECT count(film_to_genre.id) FROM film_to_genre
    WHERE films.id = film_to_genre.film_id AND film_to_genre.genre_id IN (2, 14)
) = 2

And I want to write it in SqlAlchemy. This is what I've tried:

db.query(models.Film)
  .filter(db.query(func.count(models.FilmToGenre.id))
                     .filter(and_(models.Film.id == models.FilmToGenre.film_id,
                                  models.FilmToGenre.genre_id.in_(genre_ids)))
                     .subquery().count == len(genre_ids)))

It converts to this SQL (problem in clause "WHERE false", it evaluates subquery immediately):

SELECT films.id AS films_id, films.kinopoisk_id AS films_kinopoisk_id, films.title AS films_title, films.year AS films_year, films.budget AS films_budget, films.run_time AS films_run_time, films.description AS films_description 
FROM films JOIN film_to_genre ON films.id = film_to_genre.film_id 
WHERE false
 LIMIT %(param_1)s OFFSET %(param_2)s

van · Accepted Answer · 2020-09-30 09:50:39Z

1

Add .c to access the column of the subquery:

Instead of subquery().count == len(genre_ids)) please use subquery().c.count == len(genre_ids))

I do, however, prefer as_scalar option:

genre_ids = [2, 14]
sq = (
    session.query(
        func.count(FilmToGenre.id)
    )
    .filter(
        and_(
            FilmToGenre.film_id == Film.id,
            FilmToGenre.genre_id.in_(genre_ids),
        )
    )
    .as_scalar()
)

q = (
    session.query(Film)
    .filter(sq == len(genre_ids))
)

edited Sep 30, 2020 at 9:50

answered Sep 30, 2020 at 9:38

van

77.5k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sqlalchemy subquery

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related