activerecord where clause on relation belongs_to

Question

I'm not strong in sql and relatively new to rails. The

Case
  attr_accessible client_id
  belongs_to Client

Client
  attr_accessibe name
  has_many Cases

I can query directly by client_id and get a record back as expected

Case.where(client_id: 1)

But I would like to query by client.name

Case.where(client.name => "Foo")

This give me an error that tells me that client is not a method of case.

Undefined method or local variable

Ultimately, what I'm trying to do is very simple: get the first Case that belongs to client "Foo". The query I would expect to use is this.

Case.where(client.name => "Foo").first

What should it be?

`Case.joins(:client).where(clients: { name: 'foo' })` is what you are looking for -- pay attention to the plural/singular: in the joins/includes, use the same name as the relations was declared in the model ; in the where clause, always use the pluralized version of the relation (don't ask me why) — MrYoshiji, Sep 19 '13 at 17:11

score 25 · Accepted Answer · edited May 23 '17 at 11:33

25

Case.joins(:client).where(clients: { name: 'foo' })

This query will joins the clients on the case table (eliminates the Cases without any client associated) and add the where clause "where clients.name = 'foo'"

In human language, this query does:

Get the Cases having at least one Client having the name strictly equal (case-sensitive) to 'foo'

Pay attention to the plural/singular:

in the joins/includes, use the same name as the relation is declared in the model
in the where clause, always use the pluralized version of the relation (actually the table's name)

Additionnal informations:

You can use an array of values in the where clause:

Case.joins(:client).where(clients: { id: [1,2,5] })

Difference between .joins and .includes: Rails :include vs. :joins

edited May 23 '17 at 11:33

Community

1
1

answered Sep 19 '13 at 17:18

MrYoshiji

54,334
13
124
117

Ok well this answered my immediate question but created another. thanks. will link to that one from here. – rakitin Sep 19 '13 at 17:28
2

The plural/singular issue is actually about two completely independent things. `joins`/`includes` refer to _how your association is called in Rails_ (here it's `:client`), whereas within `where` you're referencing _db tables and db columns_ (here the table is called `clients`). It is just convention/convenience to name them the same. – codener Feb 10 '16 at 16:18

score 1 · Answer 2 · answered Sep 19 '13 at 17:18

1

Depending on what you're doing it may be easier to go about it this way:

Client.where(:name => "foo").first.cases

This will return all the cases for the client.

answered Sep 19 '13 at 17:18

Helios de Guerra

3,445
18
23

activerecord where clause on relation belongs_to

2 Answers2