# User and picture models

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?

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.

• 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. Jul 5, 2017 at 14:58

There are two options:

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)