Eager load Rails' has_many with two primary_keys and foreign

I have two models

class TimeEntry < ApplicationRecord
  belongs_to :contract
end

class Timesheet < ApplicationRecord
  belongs_to :contract
  has_many :time_entries, primary_key: :contract_id, foreign_key: :contract_id
end

Additionally, both models have a date column.

The problem: A Timesheet is only for a fixed date and by scoping only to contract_id I always get all time_entries of a contract for each Timesheet.

I tried to scope it like this:

has_many :time_entries, ->(sheet) { where(date: sheet.date) }, primary_key: :contract_id, foreign_key: :contract_id

This works, but unforunately it is not eager loadable:

irb(main):019:0> Timesheet.where(id: [1,2,3]).includes(:time_entries).to_a
  Timesheet Load (117.9ms)  SELECT "timesheets".* FROM "timesheets" WHERE "timesheets"."id" IN ($1, $2, $3)  [["id", 1], ["id", 2], ["id", 3]]
  TimeEntry Load (0.3ms)  SELECT "time_entries".* FROM "time_entries" WHERE "time_entries"."date" = $1 AND "time_entries"."contract_id" = $2  [["date", "2014-11-21"], ["contract_id", 1]]
  TimeEntry Load (0.3ms)  SELECT "time_entries".* FROM "time_entries" WHERE "time_entries"."date" = $1 AND "time_entries"."contract_id" = $2  [["date", "2014-11-22"], ["contract_id", 1]]
  TimeEntry Load (0.3ms)  SELECT "time_entries".* FROM "time_entries" WHERE "time_entries"."date" = $1 AND "time_entries"."contract_id" = $2  [["date", "2014-11-23"], ["contract_id", 1]]

Is it possible, to provide Rails with two primary_keys AND foreign_keys? Or how could I make the example above eager loadable to avoid n 1 queries?

CodePudding user response：

You can use a custom SQL query for the association to retrieve the TimeEntry records for a given Timesheet in this way:

class Timesheet < ApplicationRecord
  belongs_to :contract
  has_many :time_entries, lambda {
    select('*')
      .from('time_entries')
      .where('time_entries.date = timesheets.date')
      .where('time_entries.contract_id = timesheets.contract_id')
  }, primary_key: :contract_id, foreign_key: :contract_id
end

Then, can use

timesheets = Timesheet.where(id: [1,2,3]).eager_load(:time_entries)
time_entries = timesheets.first.time_entries

Note:- this will only work with while eager loading, not preloading. That's why explicitly using the keyword instead of includes.

CodePudding user response：

To avoid n 1 queries, you could eager load the time entries for the timesheets with the following syntax:

Timesheet.where(id: [1,2,3]).includes(:time_entries).eager_load(:time_entries).to_a

Alternatively, you could use the "preload" method instead of "includes" and "eager_load":

Timesheet.where(id: [1,2,3]).preload(:time_entries).to_a

For using two primary_keys AND foreign_keys:

Yes, it is possible to provide Rails with two primary keys and foreign keys. This can be done by defining the primary key and foreign key columns in the model's definition using the primary_key and foreign_key methods, respectively. For example:

class User < ApplicationRecord
  primary_key :id, :email
  foreign_key :user_id, :email
end

In the above code, the id and email columns are defined as the primary keys, and the user_id and email columns are defined as the foreign keys.

Note that using multiple primary keys is generally not recommended, as it can lead to complexity and performance issues. It is generally better to use a single, unique primary key for each model.