Reputation: 6460
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