Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Restaurant has_many Dish

Dish
has_many Photo

Photo
belongs_to Dish

Restaurant 1
  Dish 1
    Photo 1   May 9, 1:00 PM
  Dish 2
    Photo 2   May 9, 2:00 PM
  Dish 3
    Photo 3   May 9, 3:00 PM

Restaurant 2
  Dish 4
    Photo 4   May 9, 1:00 PM
  Dish 5
    Photo 5   May 9, 2:00 PM
  Dish 6
    Photo 6   May 9, 3:00 PM

I'm trying to retrieve the latest 50 photos with a limit of 2 dish photos per restaurant. Given the data above I'd be able to retrieve photos with ids 2, 3, 5, and 6

My current implementation is ugly to say the least.

hash = {}
bucket = []
Photo.includes(:dish => [:restaurant]).order("created_at desc").each do |p|
  restaurant_id = p.dish.restaurant.id
  restaurant_count = hash[restaurant_id].present? ? hash[restaurant_id] : 0
  if restaurant_count < 2
    bucket << p
    hash[restaurant_id] = restaurant_count + 1
  end
  # if you've got 50 items short circuit.
end

I can't help but feel that there's a much more efficient solution. Any ideas would be appreciated :-).

share|improve this question
1  
This isn't trivial logic to implement even in pure SQL. You might be better off maintaining a specialized queue structure of some sort that always maintains a list of these 50 photos. You can then do the 2-per-restaurant limit when you enequeue a new photo. Obviously this would be optimized for few-writes-many-reads. – mikeryz 17 hours ago
Yes, that was my next step, since I'm really quite clueless when it comes to subqueries. – lemon 16 hours ago
If you're down for some Googling to set up the SQL, something like this: books.google.com/… would probably be the best way to do it... – mikeryz 16 hours ago

1 Answer

up vote 1 down vote

There should be a way of 'grouping' your query, but at least the following is a bit simpler:

def get_photo_bucket
  photo_bucket = restaurant_control = []
  Photos.includes(:dish => [:restaurant]).order("created_at desc").each do |photo|
    if photo_bucket.count < 50 && restaurant_control.count(photo.dish.restaurant.id) < 2
      photo_bucket << photo
      restaurant_control << photo.dish.restaurant.id
    end
  end
  photo_bucket
end
share|improve this answer
Unfortunately this will simply return the latest two dishes from each restaurant, not the 50 most recent with a limit of two per. – mikeryz 17 hours ago
Sorry @mikeryz, I misunderstood your constraints. I have edited the answer. – Galen 17 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.