ActiveRecord - Retrieve one record for each association

Question

Merchant has_many Shops

Shop belongs_to Merchant

i.e. One merchant (Starbucks) can have many shops locations.

I'm using Gecoder to get the nearby shops, e.g. @shops = Shop.near("Times Square").

I would like to return only 1 record for each merchant only. I.e. @shops only contain 1 Starbucks, 1 Subway, but is a collection.

Sorry I've been Googling and searching on SO to no avail. Perhaps I'm not using the right word to describe what I want. Thanks in advance.

score 1 · Answer 1 · edited May 23 '17 at 12:03

1

To answer what you should be googling for, joined or combined queries within a scope will probably solve what you are looking to do. If you build a scope with :or logic combining queries, one each for each shop, limited to the first record, you should get what you are looking for.

I won't pretend that I understand Geocoder or advanced scopes enough to do this, but I found an example that shows this approach in another problem:

named_scope :or, lambda { |l, r| {
  :conditions => 
      "annotations.id IN (#{l.send(:construct_finder_sql,{:select => :id})}) or " + 
      "annotations.id IN (#{r.send(:construct_finder_sql,{:select => :id})})" 
}}

This comes from this SO question: Combine two named scopes with OR (instead of AND)

Hope this helps you find the solution.

edited May 23 '17 at 12:03

Community

1
1

answered Feb 24 '12 at 06:55

Don Leatham

2,694
4
29
41

Thanks! Wow 2 simple lines of code, but my pea brain's not really getting it. I'll give it a try. You can assume that I just want a Shop.all by where each unique `:merchant_id` is only represented once. – Damon Aw Feb 24 '12 at 06:58
To be clear, this is not code that fixes your exact problem. It is only an example of a concept that you might start googling for to learn how to solve your problem. – Don Leatham Feb 24 '12 at 07:29
Yup of course =). I was just hoping that Rails might have some magic bullets to solve the problem. I get it that it's a query with a "sub-query". – Damon Aw Feb 24 '12 at 08:17
Sorry if I confused you, the `group` statement is just what I needed. – Damon Aw Feb 25 '12 at 03:16

score 1 · Accepted Answer · answered Feb 25 '12 at 03:15

I googled a bit more and stumbled on group by for SQL.

If I have 4 shops belonging to 2 merchants near a location called "Raffles Place", within 1 kilometer.

Calling Shop.near("Raffles Place",1) returns 4 shops.

If I add a group to Shop.near("Raffles Place",1).group(:merchant_id), only 2 shops are returned.

This can be used with other conditions too, such as Shop.where(:featured=>true).group(:merchant_id) to only show 1 shop per featured merchant.

ActiveRecord - Retrieve one record for each association

2 Answers2