How to turn queries to perform efficiently in rails?

Question

Consider i have a 15 categories and 6 sub-categories and i have table items where i have set of records where i have to fetch in the following manner

category 1 ---> level 1 ---> 3 items with maximum price
category 1 ---> level 2 ---> 3 items with maximum price
  ...
  ...
  ...
category 15 ---> level 6 ---> 3 items with maximum price

and

@categories.each do |value|
   @sub-categories.each do |value1|
      array = Item.find(:all, :conditions => ["customer_id IN (?) AND category_id = ? AND sub-category_id = ?", @customer, value.id, value1.id], :order => 'price DESC', :limit => 3)
            array.each do |value2|
                   @max_price_item_of_each_customer << value2
            end
          end
        end

but this would take much time as this iterates. So how can i change this in such a way the time can be reduced? Any help is appreciated.

Answer 1

Try:

@max_price_item_of_each_customer = []
@categories.each do |value|   
      @max_price_item_of_each_customer +=  Item.find(:all, :conditions => ["customer_id IN (?) AND category_id = ? AND sub-category_id in (?)", @customer, value.id, @sub-categories.map(&:id)], :order => 'price DESC', :limit => 3)            
end

Answer 2

This all depends on the scale of records you're working with, but if you're working with a reasonable set, this should be faster and will reduce your queries to 1.

@customer_id = 1
@categories  = [1, 2, 3]
@subs        = [4, 5, 6]

@max_price_item_of_each_customer = []
items = Item.where(customer_id: @customer, category_id: @categories, subcategory_id: @subcategories)
items.group_by{|item| item.category_id}.each_pair do |category_id, category_items|
  category_items.group_by{|item| item.subcategory_id}.each_pair do |subcategory_id, subcategory_items|
    @max_price_item_of_each_customer += subcategory_items.sort{|x, y| y.price <=> x.price }.first(3)
  end
end

Answer 3

The solution below might work if you use Postgresql.

1. Select a group of 3 item ids from items table, sorted by price descending and grouped by category_id and subcategory_id. You can use Postgres array_agg to collect the item ids after grouping.
2. Select items row, where the item ids are in those grouped item ids. After that, order the result by category_id ascending, subcategory_id ascending, and price descending

The result is ActiveRecord::Relation, so you can iterate the items as usual. Since the result is flattened (but already ordered by categories, subcategories, and price), you need to separate the different categories and subcategories yourself.

grouped_item_ids = Item.where(customer_id: customer_id).
  select("items.category_id, items.subcategory_id, (array_agg(items.id order by items.price desc))[1:3] AS item_ids").
  group("items.category_id, items.subcategory_id").map {|item| item["item_ids"]}
@items = Item.where(id: grouped_item_ids.flatten).
  order("items.category_id ASC, items.subcategory_id ASC, items.price desc")

Answer 4

Following query works for me

   @max_price_item_of_each_customer =Item.find_by_sql(["SELECT i1.* FROM item i1
      LEFT OUTER JOIN item i2 ON (i1.category_id = i2.category_id AND i1.sub-category_id = i2.sub-category_id AND i1.id < i2.id)
      WHERE i1.customer_id IN (?) AND i1.category_id IN (?)
      GROUP BY i1.id HAVING COUNT(*) < 3
      ORDER BY price DESC", @customer, @categories.map(&:id)])