Rails - Using join with custom-named associations

Question

I have the following model

class Measurement < ApplicationRecord
  belongs_to :examination, class_name: "TestStructure", foreign_key: "examination_id"

end

The association is actually made to the TestStructure model, but the association name is examination. There is no examination table.

The problem arises when I'm querying using join. The following query

Measurement.joins(:examination).where(examination: { year: 2016, month: 5 })

fails, with this error

ActiveRecord::StatementInvalid:
   PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "examination"
   LINE 1: ...d" = "measurements"."examination_id" WHERE "examinati...
# --- Caused by: ---
 # PG::UndefinedTable:
 #   ERROR:  missing FROM-clause entry for table "examination"
 #   LINE 1: ...d" = "measurements"."examination_id" WHERE "examinati...

So clearly, the examinations table doesn't exists, but I can't find a way to tell ActiveRecord I'm using a named association instead of the default one.

Any insights?

Percept

With includes, joins and references you need to use the relation name as defined in your model. With where you need to use the exact table name. So if your model TestStructure store data in the table custom_named_table, you need to do Measurement.joins(:examination).where(custom_named_table: { year: 2016, month: 5 }) (you can find the table name using TestStructure.table_name) (see my previous answers on that subject: #24266569) – Jd

Answer 1

where expects the actual table name, it just inserts it in SQL:

Article.where(whatever: {you: 'want'}).to_sql
=> "SELECT `articles`.* FROM `articles` WHERE `whatever`.`you` = 'want'"

So you may use:

Measurement.joins(:examination).where(test_structures: { year: 2016, month: 5 })

But it's not good

Then you depend on table name while Model should abstract such things. You could use merge:

Measurement.joins(:examination).merge(TestStructure.where(year: 2016, month: 5))

Underact

you are correct. Let me try your method, hopefully it won't perform more than one query, and I'll get back to you. – Percept

I will call out that using .merge() along with .joins() has been a huge lifesaver for me on the Rails project I work on for a living. We have Rails Engines, so each of the DB table names for our ActiveRecord models have the engine name as a prefix. This causes complexity with using an association's name in a where() call, I would have to use the DB table's name as a Hash key instead. The combination of .joins() and merge() has made ActiveRecord querying a much more pleasurable experience for this project. – Heredia

Answer 2

For joins you use the association name, but for where you need to use the table name

Measurement.joins(:examination).where(test_structures: { year: 2016, month: 5 })

or

Measurement.joins(:examination).where('test_structures.year': 2016, 'test_structures.month': 5 )

Felicific

Answer 3

In this example table name examinations should be provided instead of an association name examination in the where method.

Measurement.jons(:examination).where(examinations: { year: 2016, month: 5 })

Bernadinebernadotte

Please correct me if I'm wrong, does eager_load actually retrieves records into memory? I'm aiming to not do that, thus the join. – Percept

@Sebastialonso: according to the activerecord docs I am wrong and you can use joins, but the table name caveat is crucial. – Bernadinebernadotte

Answer 4

In somecase you need to add .references(associations)

Measurement.joins(:examination).merge(TestStructure.where(year: 2016, month: 5)).references(:examination)

Gam