0

I'm new to Rails (and SQL) and am trying to write an AREL query for a transit app.

The Background:

I have a few different models (Stop, Route, Trip, and StopTime). A Route has many trips, and a Trip has many stop times. This represents one route (eg: Green Line) having many trips throughout the day (the 6AM train, the 7AM train, etc.), that stop at a number of stops (MLK blvd, Washington blvd, etc.) according to a table of stop times.

The Objective:

Once a user has selected their Route and direction (eg: Green line towards UCLA), and we have determined their current stop via GPS, I want to show all the subsequent stops so the user can input their final destination.

The Problem:

In order to do this I'd like to write an AREL query in my controller that does the following:

For a given route (given_route.trips) and direction (trip.direction_id (which is a boolean)) I'd like to select the ONE trip that has the most stops (ie: select the trip where count of stops_times.sequence is the highest).

In other words:

  • For a given route, which has (lets say) 100 trips each day, we want to:
  • Select only the trips that match the direction_id (trip.direction_id)
  • For these trips, we want to pick the 1 trip where the count of Trip.stop_times.sequence is the highest

Thanks in advance -- any help would be much appreciated!

Kyle Chadha
  • 3,741
  • 2
  • 33
  • 42
  • What type of database are you using? To do this in one query might require some db-specific functions. – steakchaser Mar 03 '14 at 00:46
  • My advice: start by writing out the algorithm in pseudo code (ie what you'd do if it's *not* written in an AREL query). Then you can refactor towards a single line of AREL – Taryn East Mar 03 '14 at 00:47
  • PostgreSQL. After a bit more tinkering, I'm at: 'route.trips.where(direction_id: 0)' & 'route.trips[0].stop_times.count' ... just need to figure out how to connect those two pieces. – Kyle Chadha Mar 03 '14 at 00:48
  • @TarynEast That makes sense. I suppose I could do it by iterating through route.trips.each, saving the trip & count of stop_times to a hash, and then selecting the max value. Will do that in the mean time and would love to hear if you have any thoughts on the AREL as well – Kyle Chadha Mar 03 '14 at 00:58

0 Answers0