User and picture models

Question

I have the following models:

1. User
2. Picture
3. Variant

User has_many pictures, picture has_many variants.

Variant has value price and I am trying to find the price of all pictures of selected (e.g. for user id 15) current user.

First I tried like this:

def value(user)
 total = 0
 user.pictures.each do |picture|
 picture.variants.each do |variant|
 total += variant.price
 end
 end
 total.to_f
end

How could I improve this code so that I can avoid the N+1 problem?

Answer 1

You could do this with just one query. There are variations how to construct the query but I would probably do something like this:

picture_ids = user.pictures.select(:id) # Will be used as a subquery if you use select
Variant.where(picture_id: picture_ids).sum(:price)

This will only generate one query, and sum all the prices using sql, so you don't lose performance.

EDIT: As pointed out in the comment below, if you are using MySQL (or have a SQL server which does not handle subqueries that good) you can use pluck instead of select. That will make as separate query to fetch all the picture_ids and everything else will still work the same way.

• Marc Rohloff: With MySQL you need to be careful about using sub-selects as their performance is not very good (see dba.stackexchange.com/questions/14565/…) iirc, Oracle and PostgreSQL optimize the query better.

Answer 2

There are two options:

1) Add a has_many though clause to your user object:

class User
 has_many :pictures
 has_many :variant, through: :pictures
 ...

You could also explicitly join tables into a single query:

Variants.joins(:pictures).where(pictures: {user: user} )

I would also use the sum method to do the query on the database server:

user.variant.sum(:price)