GTFS MySQL Query Column Not Found

Im working with GTFS dataset that I have imported into MySql, Im accessing it with PHP. Im trying to find the trips for a specific route for a given time and date. My Sql query looks like this

'SELECT  t.route_id AS route_id, t.service_id AS service_id, t.trip_headsign AS trip_headsign,
                t.trip_id AS trip_id,
              ( SELECT   departure_time
                FROM     stop_times
                WHERE    trip_id = t.trip_id
                ORDER BY departure_time ASC limit 1) AS initial_departure_time,
              ( SELECT   arrival_time
                FROM     stop_times
                WHERE    trip_id = t.trip_id
                ORDER BY arrival_time DESC limit 1) AS final_arrival_time
        FROM trips AS t INNER JOIN calendar_dates AS c
        WHERE t.service_id = c.service_id
          AND c.date ='.$calendar_date.' 
          AND initial_departure_time <='.$time.'
          AND'.$time. '<=final_arrival_time
          AND t.route_id ='.$route.'
        ORDER BY trip_id ASC';

$route, $calendar_date and $time are all passed in.

The query is returning Column not found:

1054 Unknown column 'initial_departure_time' in 'where clause'. Im thinking that initial_departure_time cant be evaluated.

Im completely lost as too how to resolve this. Any clues, thanks in advance

Solution

You can't use aliases in your WHERE clause.

Either incorporate the sub-queries into the WHERE clause:

'SELECT t.route_id AS route_id, 
        t.service_id AS service_id, 
        t.trip_headsign AS trip_headsign,
        t.trip_id AS trip_id,
        (SELECT departure_time
         FROM stop_times
         WHERE trip_id = t.trip_id
         ORDER BY departure_time ASC limit 1) AS initial_departure_time,
        (SELECT arrival_time
         FROM stop_times
         WHERE trip_id = t.trip_id
         ORDER BY arrival_time DESC limit 1) AS final_arrival_time
FROM trips AS t INNER JOIN calendar_dates AS c
WHERE t.service_id = c.service_id
AND c.date ='.$calendar_date.' 
AND (SELECT departure_time
     FROM stop_times
     WHERE trip_id = t.trip_id
     ORDER BY departure_time ASC limit 1) <='.$time.'
AND'.$time. '<= (SELECT arrival_time
                 FROM stop_times
                 WHERE trip_id = t.trip_id
                 ORDER BY arrival_time DESC limit 1)
AND t.route_id ='.$route.'
ORDER BY trip_id ASC';

Or wrap the query and then use the aliases in that:

'SELECT * FROM (
        SELECT t.route_id AS route_id, 
        t.service_id AS service_id, 
        t.trip_headsign AS trip_headsign,
        t.trip_id AS trip_id,
        (SELECT departure_time
         FROM stop_times
         WHERE trip_id = t.trip_id
         ORDER BY departure_time ASC limit 1) AS initial_departure_time,
        (SELECT arrival_time
         FROM stop_times
         WHERE trip_id = t.trip_id
         ORDER BY arrival_time DESC limit 1) AS final_arrival_time
         FROM trips AS t INNER JOIN calendar_dates AS c
         WHERE t.service_id = c.service_id
         AND c.date ='.$calendar_date.' 
         AND t.route_id ='.$route.'
         ORDER BY trip_id ASC) a 
WHERE a.initial_departure_time <='.$time.'
AND'.$time. '<= a.final_arrival_time';

This might speed it up (you may need to swap the MIN and MAX around):

'SELECT t.route_id AS route_id, 
        t.service_id AS service_id, 
        t.trip_headsign AS trip_headsign,
        t.trip_id AS trip_id,
        MIN(s.departure_time) AS initial_departure_time,
        MAX(s.arrival_time) AS final_arrival_time
FROM trips AS t 
INNER JOIN calendar_dates AS c ON t.service_id = c.service_id
LEFT JOIN stop_times s ON s.trip_id = t.trip_id
WHERE c.date ='.$calendar_date.' 
AND t.route_id ='.$route.'
GROUP BY t.route_id, t.service_id, t.trip_headsign, t.trip_id,
HAVING '.$time.' BETWEEN MAX(s.arrival_time) AND MIN(s.departure_time)
ORDER BY trip_id ASC';