store with categories of goods

it is necessary for each category (sorted by popularity) to select one product (sorted by popularity), but since products can be repeated from category to category, it is necessary to make a selection of products unique.

There are three tables:

categories:

cats id,name,popularity

products:

 items id,name,popularity

relationship table:

 cats2items cat_id, item_id

at the output you need to get like:

 cat_id item_id 1 5 3 1 2 56 10 177

Zillion options tried, here's the last one:

 SELECT cats2items.cat_id, i.item_id FROM cats2items inner join ( select items.id, items.popularity FROM items ORDER by items.popularity DESC ) i WHERE cats2items.item_id = i.id AND cats2items.cat_id IN (SELECT cats.id FROM cats ORDER BY cats.popularity DESC) GROUP BY cats2items.cat_id

but the products are not unique and they are not sorted by popularity:

 cat_id item_id 1 86 2 3 3 1 4 49 5 3

tell me where to dig pliz

  • And which of the groups should include the product, if it is the most popular in several groups? - Akina
  • to the first one (they are also sorted by popularity) - Mikhail
  • I would solve within the stored procedure - with a cursor and a temporary table inside. Yes, by the way ... and what to do if ALL products of the next category are already involved for output in more popular categories? - Akina
  • Can you help with a link to the topic disclosure or sample code? I'm still a beginner) - Mikhail
  • >>> what to do if ALL products of a regular category are already involved for output in more popular categories? - this is not possible, there are many goods, but the question is good for the future - Mikhail

1 answer 1

I'm afraid without magic with variables can not do here. We need a sanchala to get a full multiplied sample of all products in all groups, sort it by popularity. Further, in the course of the lines, mark the first product in the group, in case it has not been met before and memorize in the text variable that such product has already been used:

 select cat_id, cat_name, item_id, item_name from ( select y.*, @cgrp, @first:=if(@cgrp!=cat_id and find_in_set(item_id,@used)=0,1,0) first, @used:=concat(@used,if(@first,concat(',',item_id),'')), @cgrp:=if(@first,cat_id,@cgrp) from ( select c.id cat_id,c.name cat_name, c.popularity cat_p, i.id item_id, i.name item_name, i.popularity item_p from cats c, items i, cats2items ci, (select @cgrp:=0,@used:='',@first:=0) x where ci.cat_id=c.id and ci.item_id=i.id order by c.popularity desc, c.id, i.popularity desc ) y ) z where first=1 order by cat_p desc, item_p desc

Sample on sqlfiddle.com . Please note that in category 4, the query did not display a single line, since both products that are in this category were previously displayed in other categories. And this is not treated in the given formulation of the problem, categories in which few goods can suffer from it.

  • Thank you so much!! everything works great! And for 180 categories and 53k products, a run time of 8-11 seconds is a lot or normal? - Mikhail
  • one
    @Mikhail Generally a lot. On the other hand, and work decently. If we say each product in 10 categories, we get after multiplying the tables of 530k records, and we still need to sort them. And maybe it's something else. just try to make select count(1) from (самый внутренный подзапрос) x i. see how much time will be performed exactly the interior. And how many entries it will show interestingly. And if just as slowly, then look at the query execution plan, maybe there are not enough useful indexes ... - Mike