Search for items in MYSQL db that have ALL of the provided tags-CodePudding

Bottom line, I want to be able to search for items by tag and tag combinations (plus and minus). I played with subqueries and considered CTE functionality, but the first is labor intensive and the second doesn't work because the tag subquery changes by result. I think I can cheat by doing simple string search instead like so:

SELECT *, CONCAT(',',(SELECT GROUP_CONCAT(tag_id) FROM item_tags WHERE item_id = id),',') as tags FROM items
WHERE 
tags LIKE '%,97,%'
AND tags LIKE '%,30,%'
AND tags NOT LIKE '%,7,%'

The extra commas cover situations like how 7 would normally match 17, 107, etc (with the commas, I can search for just "7".

Anyway, it's saying "unknown column 'tags' in where clause" which I guess means you can't search a created value using LIKE? But I can't find a way to do a string search of created values either.

I could use some help finding either a way to do substring search of my "tags" value OR a more sophisticated way of solving my core issue. I could probably make it work by replacing each "LIKE" line with a repeated subquery, but that seems wildly inefficient.

CodePudding user response：

You can't reference a column alias in the WHERE clause. Folks often assume clauses of a query are evaluated top to bottom, but this is not the case. The WHERE clause performs row filtering, before expressions of the select-list are evaluated. There'd be no benefit to evaluating a costly expression for a million rows, if most of the rows are eliminated based on other conditions, right?

That's the reason for your "unknown column" error. The tags column is an alias for an expression in the select-list, which doesn't exist at the time the WHERE clause does its work.

Now for your task of filtering for items that match multiple tags. This is called relational-division. There are a few ways to do it, but unfortunately SQL doesn't have very convenient syntax for this operation, probably because it's less common than other operations like JOIN or UNION.

Here's one solution:

SELECT items.*
FROM items
JOIN item_tags ON items.id = item_tags.item_id
WHERE tag_id IN (7,30,97)
GROUP BY items.id
HAVING COUNT(DISTINCT tag_id) = 3;

You will find other solutions by reading answers to other questions with the relational-division tag.