sql - Reducing Queries while looking for associated records -
i have code, performing awful lot of sql queries, last method ('most related'). following setup this:
class user < activerecord::base authenticates_with_sorcery! has_and_belongs_to_many :items def purchased_categories ids = [] self.items.each |item| ids << item.categories.pluck(:id) end ids.flatten.uniq end def recommended_items item.includes(:categories).where("categories.id in (?)", self.purchased_categories).references(:categories).uniq - self.items end def most_related cs = self.purchased_categories self.recommended_items.sort { |a, b| (a.categories.pluck(:id) & cs).length <=> (b.categories.pluck(:id) & cs).length } end end my item model looks following:
class item < activerecord::base has_and_belongs_to_many :categories has_and_belongs_to_many :users end i have ton of queries in most_related method, , wondering, if can reduce somehow?
edit:
the main issue i'm seeing in most_related - performing ton of queries, see below:
item load (4.1ms) select "items".* "items" inner join "items_users" on "items"."id" = "items_users"."item_id" "items_users"."user_id" = $1 [["user_id", 815249]] (0.9ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1253]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1253]] sql (2.9ms) select distinct "items"."id" t0_r0, "items"."name" t0_r1, "items"."created_at" t0_r2, "items"."updated_at" t0_r3, "categories"."id" t1_r0, "categories"."name" t1_r1, "categories"."created_at" t1_r2, "categories"."updated_at" t1_r3 "items" left outer join "categories_items" on "categories_items"."item_id" = "items"."id" left outer join "categories" on "categories"."id" = "categories_items"."category_id" (categories.id in (134,152)) (0.8ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1684]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1596]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1596]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1684]] (0.4ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1622]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1685]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.8ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1683]] (0.7ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.7ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1378]] (0.7ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1594]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.4ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1678]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1428]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1427]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1623]] (0.4ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1676]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1456]] (0.7ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1532]] (1.1ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1546]] (0.4ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1641]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1681]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.7ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1677]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.8ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1288]] (0.7ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1533]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1686]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1643]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1679]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1682]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.8ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1687]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1675]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1376]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1549]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1680]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1750]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1643]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1623]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1623]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1596]] (0.7ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1533]] (0.7ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1623]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1532]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1623]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1378]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1623]] (0.5ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1622]] (0.6ms) select "categories"."id" "categories" inner join "categories_items" on "categories"."id" = "categories_items"."category_id" "categories_items"."item_id" = $1 [["item_id", 1623]]
try divide sentence
item.includes(:categories).where("categories.id in (?)", self.purchased_categories).references(:categories).uniq into two, 1 ids of items, , other categories through table between item , category, like
1. item.includes(:categories).where("categories.id in (?)", self.purchased_categories) 2. category.includes(:items).where("items.id in (?)", <the ids of items>)
Comments
Post a Comment