it's not going to be simple as relationship() doesn't render HAVING, it would
have to be embedded in a subquery and then set into the relationship as a
"secondary" table or something similar - this is assuming the lat/long columns
in question are on "places_table". There are also special recipes that are
used to get parameters into a relationship() but overall this is not the easy
way to go and not for a specific query you only need in once place (or two).
Especially with the eager loading, easier to just specify what you're looking
for at query time.
If you just need to do this in isolation it would be better to render out the
query using session.query(Thing).join(Thing.places).having(distance < 25), and
then contains_eager(Thing.places).
The above is just an approximation. If you can work out the SQL we can show
you how to make query(Thing) do it exactly.
On Oct 16, 2012, at 1:30 PM, ThereMichael wrote:
> Thanks!
>
> Your example worked perfectly.
>
> But to simplify the question, I left out a wrinkle. My tables look roughly
> like this:
>
> mapper(Thing, things_table,properties={
> 'place_id' : things_table.c.place_id,
> ..
> 'place' : relation(Place,
> primaryjoin=(things_table.c.place_id==places_table.c.id)),
> })
>
> And then my query would be:
>
> things = session.query(Thing).options(eagerload(place)).order_by(...).all()
>
> Your solution was perfect, but it didn't end up applying as expected to the
> existing primaryjoin.
>
> Any ideas how to make it work in this fashion?
>
> Thanks again!
>
>
> On Tuesday, October 16, 2012 7:20:07 AM UTC-7, rpkelly wrote:
> On Tue, Oct 16, 2012 at 07:06:00AM -0700, Michael Wilson wrote:
> > Hi,
> >
> > I've like to include the distance of a particular row (object) from a given
> > point in a SQLAlchemy query using the haversine formula, and SORT on the
> > distance, similar to this example:
> >
> > http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula
> >
> >
> > But can't figure out how to do it. It seems like it's a combination of a
> > .label('distance') and .text(…) but I can't find a good example.
> >
> > My incoming rows have a longitude and latitude column, and I'd like to be
> > able to include the origin (37, -122 in the example) as parameters at
> > runtime.
>
> Completely untested, but hopefully sends you in the right direction:
>
> from sqlalchemy import func, literal, Column, Integer, Numeric, MetaData
>
> metadata = MetaData()
> markers = Table("markers", metadata,
> Column("id", Integer, primary_key=True),
> Column("lat", Numeric),
> Column("lng", Numeric),
> )
>
> origin_lat = 37
> origin_lng = -122
>
> distance = (
> 3959
> * func.acos(func.cos(func.radians(literal(origin_lat))))
> * func.cos(func.radians(markers.lat))
> * func.cos(func.radians(markers.lng) - func.radians(literal(origin_lng)))
> + func.sin(func.radians(literal(origin_lat)))
> * func.sin(func.radians(markers.lat))
> )
>
> query = (
> session.query(markers.lat, markers.lng, distance.label("distance"))
> .having(distance < literal(25))
> .order_by(distance)
> .limit(20)
> )
>
> -Ryan Kelly
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/XpMNDBBcmhMJ.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
