lostdorje
lostdorje

Reputation: 6460

How to build a sqlalchemy query that has a filter with the @> operator?

I have this flask/sqlalchemy query that works below:

paged_data = House.query\
    .with_entities(
        House.name,
        db.func.earth_distance(
            db.func.ll_to_earth(current_latitude, current_longitude),
            House.earth_location
        ).label('house_distance_from_current_location')
    )\
    .filter(db.text('earth_box(ll_to_earth(:latitude, :longitude), :radius)'
                    ' @> ll_to_earth(houses.latitude, houses.longitude)'
                   )
    )\
    .params(latitude=latitude, longitude=longitude, radius=radius)\
    .all()

but I prefer to clean it up to look more like the code below...obviously it won't work quite like that...but I can't figure out how write it...in particular, the use of the @> operator which is not valid Python the way a > operator is.

paged_data = House.query\
    .with_entities(
        House.name,
        db.func.earth_distance(
            db.func.ll_to_earth(current_latitude, current_longitude),
            House.earth_location
        ).label('house_distance_from_current_location')
    )\
    .filter(
        db.func.earth_box(db.func.ll_to_earth(current_latitude, current_longitude), radius) @> House.earth_location
    )\
    .params(latitude=latitude, longitude=longitude, radius=radius)\
    .all()

Is there a way to do this, or do I just need to settle with using text()?

For reference here is the House model (it uses a create_materialized_view() function which works fine):

class House(db.Model):
    __table__ = create_materialized_view(
        'houses',
        db.select([
            House.id.label('id'),
            House.name.label('name'),
            House.latitude.label('latitude'),
            House.longitude.label('longitude'),
            db.func.ll_to_earth(House.latitude, House.longitude).label('earth_location'),
        ]).where(
            db.and_(
                House.reviewed == True,
                House.deleted == False
            )
        )
    )

Upvotes: 2

Views: 761

Answers (1)

r-m-n
r-m-n

Reputation: 15120

You need op method

.filter(
    db.func.earth_box(db.func.ll_to_earth(current_latitude, current_longitude), radius)
    .op('@>')(House.earth_location)
)

Upvotes: 3

Related Questions