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.

Curr

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 :preload.

Note that :includes might internally use either :eager_load or :preload. So, :includes will not always work with that association. You should explicitly use :eager_load instead.

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

Bacterin

yes i think this works. i'm testing something right now and will mark as answer if all goes well. – Curr

this gets past the original error, but causes a different error... Trip.joins(:stop_times).first.stop_times #> Unknown column trips.schedule_id in where clause: SELECT stop_times.* FROM stop_times WHERE stop_times.trip_guid = '1600' AND (stop_times.schedule_id = trips.schedule_id) because the query is missing a join clause – Curr

joins does not work like this in general. If you want to preload stop_times, use :includes instead. In yor example there are two queries performed. The first one is with JOIN to get the first trip, the second one is to get stop_times of the trip. Join gives nothing here for the second query. And the second query is done without preloading, but as I said, the scope can be used just in queries with preloading. Try Trip.includes(:preloaded_stop_times).first.preloaded_stop_times – Bacterin

using includes in the manner you suggest triggers the same "unknown column" error. i did however have luck with: Trip.joins(:stop_times).includes(:stop_times).first.stop_times – Curr

actually this approach still causes the "unknown column" error when trying to invoke like so: Trip.first.stop_times. – Curr

As I have already told you twice, you can use the second association for queries with preloading only. :stop_times is for common queries like Trip.first.stop_times, while :preloaded_stop_times is for queries with preloading like Trip.includes(:preloaded_stop_times).first.preloaded_stop_times. See the usage examples. You were right, includes did not work. I edited the answer to show how it can work. You can use :eager_load instead of :includes. – Bacterin

Thanks. I'm hoping to only define and use one association. – Curr