ActiveRecord inner join using name from has_many association
I have two main ActiveRecord models (Rails 4.2):
class ImportJob < ActiveRecord::Base
has_many :logs, class_name: 'ImportLog', foreign_key: 'job_id', dependent: :destroy
end
class ImportLog < ActiveRecord::Base
belongs_to :job, class_name: 'ImportJob', foreign_key: :job_id
end
I am trying to trigger a request for ImportJob
using INNER JOIN
on ImportLog
using the name I gave it in the declaration has_many
:
ImportJob.joins(:logs).where(logs: { stage: "load", status: "succeeded" })
However, when it is created, INNER JOIN
ActiveRecord does not automatically use the name logs
. This is the error with the generated SQL:
PG::UndefinedTable: ERROR: missing FROM-clause entry for table "logs"
LINE 1: ..."import_logs"."job_id" = "import_jobs"."id" WHERE "logs"."st...
^
: SELECT "import_jobs".* FROM "import_jobs" INNER JOIN "import_logs" ON "import_logs"."job_id" = "import_jobs"."id" WHERE "logs"."stage" = $1 AND "logs"."status" = $2
These two queries work, but they seem either unintuitive or inelegant:
-
Refer to the "logs" using the table name "import_logs" (not intuitive).
ImportJob.joins(:logs).where(import_logs: { stage: "load", status: "succeeded" })
-
Write yourself
INNER JOIN
and enable "AS logs" (inelegant).ImportJob.joins("INNER JOIN import_logs AS logs ON logs.job_id = import_jobs.id") .where(logs: { stage: "load", status: "succeeded" })
Is there a better solution than these?
source to share
The more arguments you have to provide to ads belongs_to
and other relationships, the more pain you set up in the future. As clumsy as the import_job.import_logs
sounds, your AR models should be as simple as possible even at the cost of a certain degree of elegance.
Instead, create your beautiful, elegant API as a layer between your AR models and your controllers. Limit it to only the operations you need and not the unlimited operations allowed ActiveRecord::Base
. Take this opportunity to define a clean, ample-enough interface for your import system and AR models as an implementation detail. He will pay dividends in the future.
source to share