Geek Answers Handbook

Find occurrence of marked items in mysql table

Let's say I have this table:

item_id tag_id ------- ------ 1 1 1 2 2 2 2 3

As you can imagine, this is a table in which I have links to some elements and tags that belong to them. An item can have more than one tag, and one tag can be selected for more than one item.

Let's say I also have a special tag team (f.ex. tag_id = 50, 73, and 119) and a “items” table with an identifier (specified by item_id ).

Is there an efficient query that gives me:

  • number of items with these tags
  • the elements themselves?

What i tried

 SELECT COUNT(*) FROM ( SELECT COUNT(*) AS c FROM items_tags it JOIN items i ON i.id = it.item_id WHERE (tag_id=7 OR tag_id=95 OR tag_id=150) AND `status`='active' GROUP BY item_id ) t1 WHERE c=3 <-- c= number of tags

I can have both results, but with a very (apparently) inefficient query. After the exam with EXPLAIN, I would like to get rid of the “range” given by the ORs.

Clarification of my problem: The problem is that I was given a very poorly written PHP framework that is repeated more than 900 times using various tag identifiers. Let's say you have one or more fixed identifiers (selected tags) and iterates over all 900+ tags to find the number of occurrences of elements that have common PLUS tags, iteration (this is a function to refine the search, showing only those elements which have all the tags plus one).

This code works as follows: I select one or more tags and their identifier is included in the query string. Say I selected tags 54 and 77. The code should find each element ID for elements that have BOTH tags 54 and 77, and list them one by one: we get a list of "elements with selected tags."

Then it offers a choice to refine the search, and here the odd part goes: the PHP code loop processes ALL 900+ tags, and for each iteration it takes a tag and counts how many elements ALL have 54, 77 tags and one for iteration. If count> 0, it displays the tag name with the account number, filtering out each tag whose elements do not have a link to the selected tags.

It would be nice to achieve the same result in a less intense way.

+4
source share
1 answer

To get a list of product IDs that match all tags, you can use this query:

 SELECT items.id FROM items JOIN items_tags ON items.id = items_tags.item_id WHERE (items_tags.tag_id IN (7,95,150)) AND (items.status = 'active') GROUP BY items.id HAVING COUNT(DISTINCT items_tags.tag_id) = 3

Please note: if you are sure that you will never have duplicate tags for the same item, you can replace COUNT(DISTINCT items_tags.tag_id) with COUNT(*) for efficiency.

To get the number of these elements, wrap this in a COUNT request:

 SELECT COUNT(*) FROM ( SELECT items.id ... ) t

To get a list of items, wrap it in this SELECT query:

 SELECT * FROM items WHERE id IN ( SELECT items.id ... )

UPDATE

To get the number of elements for each of the remaining tags in combination with the original list, you can do this:

 SELECT tag_id, COUNT(DISTINCT item_id) FROM items_tags WHERE item_id IN ( SELECT items.id ... ) AND tag_id NOT IN (7,95,150) GROUP BY tag_id
+3
source

More articles:


All Articles