4
\$\begingroup\$

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?

\$\endgroup\$
4
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ 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. \$\endgroup\$ – Marc Rohloff Jul 5 '17 at 14:58
0
\$\begingroup\$

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)
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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