Reduce loop of activerecord queries into one query

Question

I have a Listing model which has a category column and size column. For each category I have an array of sizes. I want to return only the Listings in each category that correspond to the size arrays. (I also have an array of designers as a condition with params[:designer].)

Params hash:

params[:category] => ['tops', 'bottoms', 'outerwear', 'footwear']
params['tops'] => ['M', 'L']
params['bottoms'] => []
params['outerwear'] => ['XL']
params['footwear'] => ['11', '12']

I've created a loop to do this:

@listings = []
params[:category].each do |category|
  @listings += Listing.where(category: category, size: params[category], designer: params[:designer], sold: nil).includes(:photos).page(params[:category_page]).per(@perpage)
end

But I need it to be all in one query since I'm using the kaminari gem (the .page call) to paginate it.

sites · Accepted Answer · 2013-10-24 21:35:57Z

1

You could pass an array to where:

@listings = Listing.where(category: params[:category], s...

answered Oct 24, 2013 at 21:35

sites

21.9k18 gold badges91 silver badges147 bronze badges

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

1 Comment

Arun Gupta Over a year ago

I may be missing something but I don't think this addresses finding different sizes in different categories.

Arun Gupta · Accepted Answer · 2013-10-27 07:08:16Z

I ended up using Arel which is pretty good. Arel lets you build up whatever query you want and then call it on Model.where(). It's kind of complicated but was the only solution I found that worked.

t = Listing.arel_table
query = t[:category].eq('rooney')
params[:category].each do |category|
  if params[category]
    params[category].each do |size|
      query = query.or(t[:category].eq(category).and(t[:size].eq(size)))
    end
  end
end
dquery = t[:designer].eq('rooney')
params[:designer].each do |designer|
  dquery = dquery.or(t[:designer].eq(designer))
end
query = query.and(dquery)
@listings = Listing.where(query).includes(:photos).page(params[:category_page]).per(@perpage)

EDIT:

The designer query can be simplified using .eq_any().

t = Listing.arel_table
query = t[:category].eq('rooney')
params[:category].each do |category|
  if params[category]
    params[category].each do |size|
      query = query.or(t[:category].eq(category).and(t[:size].eq(size)))
    end
  end
end
dquery = t[:designer].eq_any(params[:designer])
query = query.and(dquery)
@listings = Listing.where(query).includes(:photos).page(params[:category_page]).per(@perpage)

Tumas · Accepted Answer · 2013-10-25 22:57:26Z

0

Transform it to a IN query:

Listing.where(designer: params[:designer]).where("category IN (?)", params[:category])

Update:

seems above code is not what op wanted, so I changed it a bit. If I understand correctly you need to generate one big query with lots of ORs in it. You can do that without going to arel:

params[:category] = ['tops', 'bottoms', 'outerwear', 'footwear']
params['tops'] = ['M', 'L']
params['bottoms'] = []
params['outerwear'] = ['XL']
params['footwear'] = ['11', '12']

query = params[:category].map do |category|
  str = "(category = ?"
  str += " AND size IN (?)" if params[category].any?
  str += ")"
  str
end

scope = Listing.where(query.join(" OR "), *params[:category].map { |cat| [cat, params[cat].any? ? params[cat] : nil] }.flatten(1).compact)
scope = scope.where(designer: params[:designer], sold: nil)
scope = scope.page(params[:per_page]).per(@page)

It generates this query (before pagination):

2.0.0-p247 :095 > scope.to_sql
 => "SELECT \"listings\".* FROM \"listings\"  WHERE \"listings\".\"designer\" IS NULL  AND \"listings\".\"sold\" IS NULL AND ((category = 'tops' AND size IN ('M','L')) OR (category = 'bottoms') OR (category = 'outerwear' AND size IN ('XL')) OR (category = 'footwear' AND size IN ('11','12')))"

edited Oct 25, 2013 at 22:57

answered Oct 24, 2013 at 21:35

Tumas

1,72712 silver badges10 bronze badges

1 Comment

Arun Gupta Over a year ago

I may be missing something but I don't think this addresses finding different sizes in different categories.

Collectives™ on Stack Overflow

Reduce loop of activerecord queries into one query

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related