How To Use Mathematic Equations As Filters In Sqlalchemy

I'm using the SQLAlchemy ORM to construct the MySQL queries in my application, and am perfectly able to add basic filters to the query, like so: query = meta.Session.query(User).fi

Solution 1:

You can use literal SQL in your filter, see here: http://www.sqlalchemy.org/docs/05/ormtutorial.html?highlight=text#using-literal-sql

For example:

clause = "SQRT(POW(69.1 * (latitude - :lat),2) + POW(53.0 * (longitude - :long),2)) < 5"query = meta.Session.query(User).filter(clause).params(lat=my_latitude, long=my_longitude)

Solution 2:

I'd use the query builder interface and the func SQL function constructor to abstract the calculation as a function. This way you can use it freely with aliases or joins.

User.coords = classmethod(lambda s: (s.latitude, s.longitude))

defcalc_distance(latlong1, latlong2):
    return func.sqrt(func.pow(69.1 * (latlong1[0] - latlong2[0]),2)
                   + func.pow(53.0 * (latlong1[1] - latlong2[1]),2))

meta.Session.query(User).filter(calc_distance(User.coords(), (my_lat, my_long)) < 5)