Use self join to retrieve schedules

[sheminanto]

We are currently looping over the schedules to fetch the required data.

Kerala-Private-Bus-Timing-API/app/controllers/api/v1/schedules_controller.rb

Line 24 in ee8deb6

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.