How can I do this complex SQL query using Django ORM? (sub-query with a join)

Question

I'm used to writing my own SQL queries and I'm trying to get used to the whole ORM thing that seems to be so popular nowadays.

Here's the query:

SELECT * FROM routes WHERE route_id IN (
    SELECT DISTINCT t.route_id FROM stop_times AS st 
    LEFT JOIN trips AS t ON st.trip_id=t.trip_id
    WHERE stop_id = %s
)

where %s is an integer.

I'm using Django's default ORM. What's the most pythonic way to do this?

Some background info: The DB I'm using is from a GTFS (Google Transit feed specification). This query is supposed to get a list of every route that goes through a particular stop, however the info linking these is in the trips table.

This query works just fine for me, so the only reason I'm asking is to learn.

Thanks!

Answer 1

Correct me if I'm wrong, but I don't think you can do that with Django ORM in a normal way.

There is no subquery support and with a normal join it would depend on your database if a distinct could help you. If you are using Postgres than you could do it with this patch: http://code.djangoproject.com/ticket/6422

The query would be something like this:

Route.objects.filter(stop_time__trips__stop_id=...).distinct('stop_time__route_id')

Answer 2

It'd probably be a bit easier to figure out the appropriate way to do this if you had what you were using for the relevant Models.

I'm assuming something like the following, based on the specification you mentioned working from:

class Route(models.Model):
    #bunch of stuff
    pass
class Stop(models.Model):
    #bunch of stuff
    stop_times = models.ManyToManyField(through=StopTime)
class StopTime(models.Model):
    trip = models.ForeignKey(Trip)
    stop = models.ForeignKey(Stop)
    # bunch of additional meta about this M2M table
    pass
class Trip(models.Model):
    route = models.ForeignKey(Route)
    # bunch of stuff

If that's the case... you should be able to do something like

Route.objects.filter(trip__stop__id=my_stop_id)

to get all Route objects that go through a given Stop with a primary key id equal to my_stop_id, which I'm assuming is an integer as per your post.

I apologize if the syntax is a bit off, as I haven't needed to do many-to-many relationships using an explicit extra table. Some adjustment may also be needed if you have to (or choose to) use the related_name parameter for any the foreign keys or the many-to-many-field.