On Sat, Aug 2, 2014 at 9:11 PM, Elliot <offonoffoffon...@gmail.com> wrote: > I am using sqlalchemy on a database of Google Transit Feed data. The models > are from pygtfs: > https://github.com/jarondl/pygtfs/blob/master/pygtfs/gtfs_entities.py > > Basically, StopTime is related both to Stop and Trip. I want all of the > stoptimes of all of the trips which have one StopTime that has a particular > stop. > > I believe this SQL does that: > > stoptimes1 = session.execute(''' > SELECT * FROM stop_times > JOIN trips ON stop_times.trip_id=trips.trip_id > JOIN stop_times AS st2 ON st2.trip_id=trips.trip_id > WHERE stop_times.stop_id=635 ORDER BY st2.stop_sequence > ''').fetchall() > > And I thought that this would be a translation of that into sqlalchemy: > > from sqlalchemy.orm import aliased > st2 = aliased(StopTime) > stoptimes2 = > session.query(StopTime).join(StopTime.trip).filter(StopTime.stop_id == > u'635').join(st2,Trip).order_by(st2.stop_sequence) > > But it is not: > > len(stoptimes1) > 2848 > > len(stoptimes2) > 109
print(stoptimes2) should reveal your issue. See also: https://stackoverflow.com/questions/4617291/how-do-i-get-a-raw-compiled-sql-query-from-a-sqlalchemy-expression > Where 109 is how many trips have a StopTime with stop_id == 635, and 2848 is > a much more reasonable number because each of those trips has many of stop > times. > > I would really appreciate getting some insight into how to do in sqlalchemy > what was done with the SQL. > > Thanks, > Elliot -Ryan -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
