Complex Rails/Postgres SQL optimization

Question

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 :-).

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
– mikeryz, Commented May 9, 2013 at 14:45
Yes, that was my next step, since I'm really quite clueless when it comes to subqueries. — lemon
– lemon, Commented May 9, 2013 at 15:04
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
– mikeryz, Commented May 9, 2013 at 15:22

Galen · Accepted Answer · 2013-05-09 14:53:23Z

1

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

edited May 9, 2013 at 14:53

answered May 9, 2013 at 13:35

Galen

9475 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mikeryz Over a year ago

Unfortunately this will simply return the latest two dishes from each restaurant, not the 50 most recent with a limit of two per.

Galen Over a year ago

Sorry @mikeryz, I misunderstood your constraints. I have edited the answer.

Collectives™ on Stack Overflow

Complex Rails/Postgres SQL optimization

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related