SQLAlchemy where exists example

sqlalchemy_where_exists.md

This python:

lg = LocationGroup
lgl1 = LocationGroupLocation
lgl2 = aliased(LocationGroupLocation)

sub_q1 = (
    self.db_session.query(lg).
    filter(lg.location_group_type_id == 2,
           lg.location_group_id == lgl1.location_group_id)
)

q1 = (
    self.db_session.query(
        lgl1.location_id,
        lgl2.location_id.label('sister_station')).
    join(lgl2, lgl1.location_group_id == lgl2.location_group_id).
    filter(
        lgl1.location_id != lgl2.location_id,
        sub_q1.exists())
)

Is equivalent to this SQL:

select lgl1.location_id, lgl2.location_id as sister_station
from location_group_location lgl1, location_group_location lgl2
where lgl1.location_group_id = lgl2.location_group_id
    and lgl1.location_id != lgl2.location_id
    and exists (
        select null
        from location_group lg
        where lg.location_group_id = lgl1.location_group_id
            and lg.location_group_type_id = 2)
order by lgl1.location_id;

Thanks worked like a charm.

Thanks, it helped a lot

3Q