Rails: ActiveRecord query based on association value

ruby-on-rails ruby activerecord associations

52,909

Solution 1

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.

Solution 2

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)

Solution 3

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)

52,909

Author by

user2158382

Updated on July 05, 2022

Comments

user2158382 almost 2 years
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
```
lobati over 9 years

You don't need .all on the end of this.
wiser over 8 years

Thanks. The same on Rails 4.2.5
user1801879 over 8 years

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.
Matthew Hinea about 7 years

I think the 'server' in the where string should be plural.
Uj Corb about 5 years

same on Rails 5.1.6 :)
Maxim Zubarev almost 5 years

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).
dcts over 4 years

same on rails 6.0.0
Omar Bahareth over 4 years

This sadly did not work for me on Rails 6. I get ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR: column servers.company_id does not exist)
Kyle Anderson almost 4 years

Should this be used with any sort of preload or include, or is it performant like this?
stevec over 3 years

Best answer since it also handles inequalities