7

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?

potashin
  • 44,205
  • 11
  • 83
  • 107
Sebastialonso
  • 1,437
  • 18
  • 34
  • 2
    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: https://stackoverflow.com/questions/24266069/join-multiple-tables-with-active-records/24266583#24266583) – MrYoshiji Aug 29 '17 at 20:14

4 Answers4

10

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))
Jan Klimo
  • 4,643
  • 2
  • 36
  • 42
Danil Speransky
  • 29,891
  • 5
  • 68
  • 79
  • you are correct. Let me try your method, hopefully it won't perform more than one query, and I'll get back to you. – Sebastialonso Aug 29 '17 at 21:27
  • 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. – ecbrodie Dec 12 '18 at 22:10
5

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 )
Greg
  • 5,862
  • 1
  • 25
  • 52
1

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 })
potashin
  • 44,205
  • 11
  • 83
  • 107
  • 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. – Sebastialonso Aug 29 '17 at 20:08
  • 1
    @Sebastialonso: according to the activerecord docs I am wrong and you can use `joins`, but the table name caveat is crucial. – potashin Aug 29 '17 at 20:11
0

In somecase you need to add .references(associations)

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