31

I have an Activities model, and they belong_to a Location

How do i select all the activities whose location.country = Australia? (for example)

Can I do this within a scope?

Will
  • 4,498
  • 2
  • 38
  • 65

3 Answers3

75

With the latest rails versions you can do:

Activity.joins(:location).where(locations: { country: "Australia" })

Beware:

  • it is location (singular) in joins(:location) because it references the belongs_to relationship name
  • it is locations (plural) in where(…) because it references the table name

The latter means that if you had the following:

belongs_to :location, class_name: "PublicLocation"

the query would be:

 Activity.joins(:location).where(public_locations: { country: "Australia" })
Arnaud
  • 17,268
  • 9
  • 65
  • 83
  • 6
    Clarification on the singular vs plural naming when it comes to belongs_to vs table name clears it up for me. Thanks! – boyan Oct 21 '16 at 12:51
22

The kind of query you're talking about is a join. You can try queries like this in the console like:

Activity.joins(:locations).where('locations.country = "Australia"')

This means that SQL is going to take all the activities and locations associated with then, find the locations where country=Australia, and then return you the activities that are associated with those locations.

To make this into a more reusable scope, define it on your model with a variable for country:

scope :in_country, lambda {|country| joins(:locations).where('locations.country = ?',country)}

You can learn more about this in the API docs.

Andrew
  • 42,517
  • 51
  • 181
  • 281
  • Ok, thanks for this. I hadn't realised I had to specify the join before i could do the query. risking lmgtfy, where are the docs for that? I had a look around & didn't find any good resources, must be missing something obvious. – Will Jan 02 '13 at 03:45
  • btw, i added an edit to match the variable being passed into the lambda to the country var used inside – Will Jan 02 '13 at 03:47
  • Great, thanks for the edit. Also, do note the other answer which suggests if you are going to be using the location object you may as well also include it. Lastly re: docs, this is SQL, not Rails. Therefore it's hard to find simple "how-to" docs for this kind of Rails-specific SQL usage, but any SQL tutorial/reference may be helpful. IMO learning to wield SQL is the hardest part of mastering Rails. Good luck! – Andrew Jan 02 '13 at 19:55
  • 1
    I'm surprised this works since `:location` is plural and that relationship doesn't seem like it'd exist – Jeremy Thomas Dec 08 '16 at 17:46
3

Yes, a scope can be used. Something like this ought to work on the Activities model:

scope :down_under, 
    joins(:locations).
    where("locations.country = 'Australia')
Marcin Kołodziej
  • 5,253
  • 1
  • 10
  • 17
Dave S.
  • 6,349
  • 31
  • 33
  • 2
    You don't need both joins and includes to run this query. If he's just loading activities there's no need to include all the locations along with the query. – Andrew Jan 02 '13 at 02:30
  • 2
    Good point - the includes is not strictly necessary. However if he does need the location object he can save himself a roundtrip to the DB. – Dave S. Jan 02 '13 at 03:05
  • Thanks for this. includes to avoid n+1 noted. – Will Jan 02 '13 at 22:08