Rails: ActiveRecord query based on association value

Question

I have 2 models. Report and Server that have a belongs_to and has_many relationship. I created an accessor method using delegate that allows a Report to find its associated Server.company_id. Now, I want to run a query on Report that allows me to find all Report that are associated with a specific Server that has a specific company_id attribute of 5.

Here are my two models. And yes I know the current query wont work since Report does not have an attribute company_id.

And no, I dont want to store company_id inside of Report since that information doesn't belong in Report.

Report

class Report < ActiveRecord::Base

 belongs_to :server

 delegate :company_id, :to => :server

    class << self

        def method(url, base_url)
            #Report.where(company_id: 5)
        end
    end

end

Server

class Server < ActiveRecord::Base

 attr_accessible :company_id

 has_many :reports

end

score 108 · Accepted Answer · edited May 22 '18 at 10:32

108

You can perform a query like this:

Report.joins(:servers).where(:servers => {:company_id => 5})

To me, this is the cleaner solution to raw SQL.

edited May 22 '18 at 10:32

frogatto

28,539
11
83
129

answered Oct 21 '13 at 08:18

KappaNossi

2,656
1
15
17

8

You don't need `.all` on the end of this. – lobati Jan 30 '15 at 14:58
3

The second instance of "server", ".where(:server..." should be changed to ".where(:servers...". It requires a plural form as it is name of the table. – user1801879 Jan 04 '16 at 04:11
Sometimes it happens that not all your records have an association relation in the database, e.b. in this case, not every report might `belong_to` a server. Using `.joins(:servers)` will in that case only return the reports that do `belong_to` a server. If you want to return all the reports no matter of their actual database relations to the servers, use `.includes(:servers)` instead. Downside: It's slightly slower (obviously). – Maxim Zubarev Aug 04 '19 at 09:55
1

Should this be used with any sort of `preload` or `include`, or is it performant like this? – Kyle Anderson May 28 '20 at 12:05

score 49 · Answer 2 · answered Sep 08 '15 at 21:39

49

I'm using Rails 4.1.7 and the accepted answer did not work for me. What did work is

Report.joins(:server).where(:servers => {:company_id => 5})

Note that the difference is the key in the where clause is pluralized (:servers instead of :server)

answered Sep 08 '15 at 21:39

Joseph Gill

1,067
11
18

2

Thanks. The same on Rails 4.2.5 – wiser Dec 18 '15 at 00:38
4

same on Rails 5.1.6 :) – Uj Corb Mar 14 '19 at 10:20
1

same on rails 6.0.0 – dcts Oct 03 '19 at 12:19
This sadly did not work for me on Rails 6. I get `ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR: column servers.company_id does not exist)` – Omar Bahareth Oct 03 '19 at 14:09

Patrick Oscity · Answer 3 · 2017-03-27T12:11:04.507

27

This should do the trick

Report.joins(:server).where('servers.company_id = ?', 5)

you could also add a scope for this like so

scope :with_company_id, lambda {|id| joins(:server).where('servers.company_id = ?', id) }

and then write

Report.with_company_id(5)

edited Mar 27 '17 at 12:11

answered Oct 21 '13 at 08:04

Patrick Oscity

53,604
17
144
168

2

I think the 'server' in the `where` string should be plural. – Matthew Hinea Mar 27 '17 at 09:34
1

Best answer since it also handles inequalities – stevec Jan 02 '21 at 04:48

score 0 · Answer 4 · answered Sep 05 '22 at 08:30

This maybe an overkill but we have Arel:

# Get the SQL

# arelise will return the SQL (string)
def arelise(company_id)
    # Tables to query
    reports = Report.arel_table # Main table
    servers = Server.arel_table.alias('servers')

    reports
        .join(servers, Arel::Nodes::InnerJoin).on(reports[:server_id].eq(servers[:id]))
        .where(servers[:company_id].eq(company_id))
        # Select all (*)
        .project(reports[Arel.star])
        .to_sql
end

sql = arelise(1)
# puts sql

# To Execute:

reports = Report.find_by_sql(sql)

# puts reports

Rails: ActiveRecord query based on association value

4 Answers4

Linked