Rails 5 how to form association between tables on multiple shared attributes

Question

In Rails 5, given a relationship between two tables that involves joining them on multiple shared attributes, how can I form an association between the models corresponding to these tables?

SQL:

SELECT *
FROM trips
JOIN stop_times ON trips.guid = stop_times.trip_guid AND trips.schedule_id = stop_times.schedule_id

I tried the following configuration, which works in general...

class Trip < ApplicationRecord
  has_many :stop_times, ->(trip){ where("stop_times.schedule_id = ?", trip.schedule_id) }, :inverse_of => :trip, :primary_key => :guid, :foreign_key => :trip_guid, :dependent => :destroy
end

class StopTime < ApplicationRecord
  belongs_to :trip, :inverse_of => :stop_times, :primary_key => :guid, :foreign_key => :trip_guid
end

Trip.first.stop_times.first #> StopTime object, as expected
Trip.first.stop_times.first.trip #> Trip object, as expected

... but when I try to use it in more advanced queries, it triggers ArgumentError: The association scope 'stop_times' is instance dependent (the scope block takes an argument). Preloading instance dependent scopes is not supported....

Trip.joins(:stop_times).first #=> the unexpected ArgumentError
StopTime.joins(:trip).first #> StopTime object, as expected

I understand what the error is referencing, but I'm unsure of how to fix it.

EDIT:

I was hoping a single association would be sufficient, but it has been noted two different associations can do the job:

class Trip < ApplicationRecord
  has_many :stop_times, 
              ->(trip){ where("stop_times.schedule_id = ?", trip.schedule_id) }, 
              :primary_key => :guid, 
              :foreign_key => :trip_guid # use trip.stop_times instead of trip.joined_stop_times to avoid error about missing attribute due to missing join clause

  has_many :joined_stop_times, 
            ->{ where("stop_times.schedule_id = trips.schedule_id") },
            :class_name => "StopTime",
            :primary_key => :guid,
            :foreign_key => :trip_guid # use joins(:joined_stop_times) instead of joins(:stop_times) to avoid error about instance-specific association
end

Trip.first.stop_times
Trip.eager_load(:joined_stop_times).to_a.first.joined_stop_times # executes a single query

If anyone reading this knows how to use a single association, please at-mention me.

Answer 1

I don't think it is the right solution, but it can help. You can add another similar instance independent association that will be used for preloading only. It will work with :joins and :eager_load but not with :includes.

class Trip < ApplicationRecord
  has_many :preloaded_stop_times, 
           -> { where("stop_times.schedule_id = trips.schedule_id") },               
           class_name: "StopTime", 
           primary_key: :guid, 
           foreign_key: :trip_guid
end

# Usage
trips = Trip.joins(:preloaded_stop_times).where(...)
# ...

# with :eager_load
trips = Trip.eager_load(:preloaded_stop_times)

trips.each do |trip|
  stop_times = trip.preloaded_stop_times
  # ...
end