CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Arun Gupta
Arun Gupta

Reputation: 11

Reduce loop of activerecord queries into one query

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.

Upvotes: 0

Views: 331

Answers (3)

Arun Gupta
Arun Gupta

Reputation: 11

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)

Upvotes: 1

Tumas
Tumas

Reputation: 1727

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')))"

Upvotes: 0

sites
sites

Reputation: 21785

You could pass an array to where:

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

Upvotes: 1

Related Questions