require 'time' class Api::V1::SchedulesController < ApplicationController def index…
- User
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 endend end
edit above code to use below logic 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 LIKE ? and s2.name LIKE ?", "%#{params[:departure]}%", "%#{params[:destination]}%")
- Assistant
You can refactor the original controller action to utilize the improved query logic you've suggested. Below is the updated
indexaction that incorporates the new query to retrieve the stations, as well as preloading the associatedschedulewith itsrouteandbus_schedule:require 'time' class Api::V1::SchedulesController < ApplicationController def index # Convert provided departure and destination station names to uppercase with spaces departure_station = params[:departure]&.upcase&.tr('_', ' ') destination_station = params[:destination]&.upcase&.tr('_', ' ') # Parse time if provided time = params[:time].present? ? Time.parse(params[:time]) : nil # Exclude return trips exclude_return = params[:restrict]&.downcase == 'true' # Check for the presence of required parameters if departure_station && destination_station 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("JOIN stations s2 ON s1.schedule_id = s2.schedule_id") .preload(schedule: { route: :bus_schedule }) .where("s1.name LIKE ? AND s2.name LIKE ?", "#{departure_station}%", "#{destination_station}%") trips = stations.map do |station| # Ensure stations are in correct order for a valid trip departure_time = Time.parse(station.departure_time) next if time && departure_time < time next if exclude_return && departure_time > Time.parse(station.arrival_time) { schedule_id: station.schedule_id, departure_station: station.departure_station, destination_station: station.destination_station, departure_time: station.departure_time, arrival_time: station.arrival_time, vehicle_number: station.schedule.route.bus_schedule.vehicle_number } end.compact # Sort trips by departure time sorted_trips = trips.sort_by { |trip| Time.parse(trip[:departure_time]) } render json: sorted_trips, status: :ok else render json: { error: 'Departure and destination stations are required.' }, status: :unprocessable_entity end end endA few things to note about the changes:
-
joins("JOIN stations s2 ON s1.schedule_id = s2.schedule_id"): I've changed this to an explicit JOIN clause that connects two instances of the stations table onschedule_id. -
I've removed the redundant call to
distinctandhavingas the SQL select with join does the comparison by itself. -
preload(schedule: { route: :bus_schedule }): We're preloading associatedschedule,route, andbus_schedulemodels to avoid N+1 queries when we access these associations for each station. -
We're using
compactto removenilelements from the array, which are results ofnext ifconditions. -
We're sorting trips by the parsed
departure_timefrom the stations records. -
I've removed underscores and replaced them with spaces directly where the parameters are assigned. If underscores are valid and should not be replaced, you may need to adjust the code accordingly.
Make sure to review and adjust the new logic according to the rest of the existing codebase and the intended functionality of your application. The updated logic assumes certain behaviors and relationships, and thorough testing is recommended to ensure proper integration.
-