We are currently looping over the schedules to fetch the required data.
|
trips = schedules.map do |schedule| |
Fetching data in a loop results in n+1 queries. It will slow down the application gradually as the database size increases.
Ref: https://guides.rubyonrails.org/active_record_querying.html#n-1-queries-problem,
https://www.bigbinary.com/blog/preload-vs-eager-load-vs-joins-vs-includes
The image below shows the number of database queries generated.
The n+1 query issue can be fixed by using a self-join query to retrieve all the possible bus schedules.
arrival_time = "06:35 am"
stations = Station.select("s1.name departure_station, s2.name destination_station, s1.schedule_id schedule_id, s1.departure_time departure_time, s2.arrival_time arrival_time")
.joins("s1, stations s2")
.preload(schedule: { route: :bus_schedule })
.where("s1.schedule_id=s2.schedule_id and s1.name ILIKE ? and s2.name ILIKE ? and s1.arrival_time > ?",
"%#{params[:departure]}%", "%#{params[:destination]}%", arrival_time)
.order("s1.arrival_time ASC")
The above code will fire only 4 database queries independent of the database size.