How can I execute this complex SQL query using Django ORM? (subquery with join)

Question

I'm used to writing my own SQL queries, and I'm trying to get used to the whole ORM object that seems so popular these days.

Here's the request:

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 am using ORM Django by default. What is the most pythonic way to do this?

Some background information: The DB I am using is the GTFS specification (Google Transit feed specification). It is assumed that this request will receive a list of all routethat go through a specific one stop, however, the information associated with them is in the table trips.

This request is great for me, so the only reason I ask is to find out.

Thank!

Answer 1

, , , Models.

- , , :

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

... -

Route.objects.filter(trip__stop__id=my_stop_id)

Route, Stop id, my_stop_id, , , .

, , " ", . , ( ) related_name -.

Answer 2

, , , Django ORM .

, , - . Postgres, : http://code.djangoproject.com/ticket/6422

:

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