CODEWITHSUNDEEP
ruby-on-rails-3activerecord
Damon Aw
Damon Aw

Reputation: 4792

ActiveRecord - Retrieve one record for each association

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.

Upvotes: 1

Views: 587

Answers (2)

Damon Aw
Damon Aw

Reputation: 4792

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.

Upvotes: 1

Don Leatham
Don Leatham

Reputation: 2704

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.

Upvotes: 1

Related Questions