require 'time'

class Api::V1::SchedulesController < ApplicationController
  def index
    
    # convert the dep and des station names to uppercase and replace underscores with spaces
    departure_station = params[:departure]&.upcase
    destination_station = params[:destination]&.upcase
    
    time = params[:time].present? ? Time.parse(params[:time]) : nil
    exclude = params[:restrict]&.downcase == 'true'

    # select all schedules that have the departure and destination stations
    # and also starting with station name
    # eg : if searching for pala it also return pala private stand , pala ksrtc stand, pala old stand 
    
    if departure_station && destination_station
      schedules = Schedule.joins(:stations)
                    .where("stations.name LIKE ? OR stations.name LIKE ? OR stations.name = ? OR stations.name = ?", "#{departure_station} %", "#{destination_station} %", "#{departure_station}", "#{destination_station}")
                    .select('schedules.*')
                    .group('schedules.id')
                    .having('COUNT(DISTINCT stations.name) = 2')
                    .distinct 
      trips = schedules.map do |schedule|
        stations = schedule.stations.order(:id)
        station_names = stations.map(&:name)

        # find the index of the departure and destination stations
        departure_index = station_names.index { |name| name =~ /#{departure_station}/i }
        destination_index = station_names.index { |name| name =~ /#{destination_station}/i }

        # this is used to exclude return trip , if the user is searching for a trip from pala to kottayam
        # it will exclude the trip from kottayam to pala (since database has both trips)

        if departure_index && destination_index && departure_index < destination_index
          departure_station_data = stations[departure_index]
          departure_time = Time.parse(departure_station_data.departure_time)

          next if time && departure_time < time

          trip_data = {
            vehicle_number: schedule.route.bus_schedule.vehicle_number,
            trip: schedule.trip,
            stations: stations.map do |station| 
              {
                station: station.name,
                arrivalTime: station.arrival_time,
                departureTime: station.departure_time
              }
            end
          }

          trip_data[:stations] = trip_data[:stations][departure_index..destination_index] if exclude
          trip_data
        end
      end.compact

      # sort the trips by departure time of the departure station
      sorted_trips = trips.sort_by do |trip|
        time_str = trip[:stations].find { |station| station[:station] =~ /#{departure_station}/i }[:departureTime]
        Time.parse(time_str)
      end

      render json: sorted_trips, status: :ok
    else
      render json: { error: 'Departure and destination stations are required. ' }, status: :unprocessable_entity
    end
  end
end



someone created an github issue as below, what are the issues of below method to implement in my code

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.
image

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.