CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Konstantin Komissarov
Konstantin Komissarov

Reputation: 365

How to make this query with SQLAlchemy

I have a base query and need to add a filter for locations.

Location.parents is an array of integers.

It works, but it uses 2 queries:


parents_ids = self._db_session.query(Location.parents)\
    .filter(Location.id == location_id)\
    .scalar()

query = query.filter(
    or_(
        BookingItem.location_id == location_id, 
        BookingItem.location_id.in_(parents_ids)
    )
)

How can I do this with one query? I tried to use a subquery, but it doesn't work.


parents_ids = self._db_session.query(Location.parents)\
    .filter(Location.id == location_id)\
    .subquery()

query = query.filter(
    or_(
        BookingItem.location_id == location_id, 
        BookingItem.location_id.in_(parents_ids.as_scalar())
    )
)

Upvotes: 0

Views: 49

Answers (1)

Ruben Helsloot
Ruben Helsloot

Reputation: 13129

If you want the SQL to look like this:

SELECT id
FROM booking_item
WHERE location_id = ?
   OR (SELECT location_id = ANY(parents) FROM location WHERE id = ?)

Then the related SQLAlchemy would be

parents_ids = self._db_session\
    .query(BookingItem.location_id == Location.parents.any())\
    .filter(Location.id == location_id)\
    .subquery()

query = query.filter(
    or_(
        BookingItem.location_id == location_id, 
        parents_ids.as_scalar()
    )
)

Another option is to use UNNEST to create a list of options:

SQL:

SELECT id
FROM booking_item
WHERE location_id = ?
   OR location_id IN (SELECT UNNEST(parents) FROM location WHERE id = ?)

Then the related SQLAlchemy would be

parents_ids = self._db_session\
    .query(func.unnest(Location.parents))\
    .filter(Location.id == location_id)\
    .subquery()

query = query.filter(
    or_(
        BookingItem.location_id == location_id, 
        BookingItem.location_id.in_(parents_ids.as_scalar())
    )
)

Upvotes: 1

Related Questions