Description
The arrivalsAndDeparturesForStopHandler in internal/restapi/arrivals_and_departure_for_stop.go currently suffers from a significant N+1 query problem.
Inside the main loop iterating over stopTimes, the code executes individual database queries for GetRoute and GetTrip for every single arrival.
Impact
For a busy stop with 50 upcoming arrivals, this results in 100+ separate database queries per single HTTP request. This causes high latency and unnecessary load on the database.
Goal
Refactor the handler to use Batch Fetching:
- Collect all unique Route IDs and Trip IDs before the loop.
- Fetch all required data in just two queries (
GetRoutesByIDs and GetTripsByIDs).
- Use in-memory maps for O(1) lookups inside the loop.