CODEWITHSUNDEEP
mysqlsqlmatchmatching
Igor
Igor

Reputation: 2969

MySQL: Matching objects by their common tags

I have the following tables structure:

pizza_tags
  id
  pizza_id
  tag_id

order_tags
  id 
  order_id
  tag_id

--

tags
  id
  tag (text)

Each pizza, as well as an order, can have multiple tags.

The question is how to find the best pizza for a given order (order_id). The more "matching" tags Pizza and Order have, the better pizza we can find for any given order.

I tried a handful of different SQL queries with intersections and joins, but didn't find a more or less quick query which will solve the described problem.

Please advise.

Upvotes: 1

Views: 49

Answers (2)

dub stylee
dub stylee

Reputation: 3342

If I am understanding the question correctly, you should be able to accomplish this using a subquery. Something like this:

SELECT o.id, o.order_id, o.tag_id,
(SELECT COUNT(*) FROM pizza_tags AS p WHERE p.tag_id = o.tag_id) AS p_tags
FROM order_tags AS o
GROUP BY o.order_id, o.tag_id
ORDER BY p_tags DESC;

Basically, you are getting any pizza_tags that match your order_tags.tag_id and sorting them with the most relevant first.

I have put together a minimal Fiddle here.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

The idea is a join between the two tag tables, on the tag_id. The rest is just aggregation, filtering, and counting.

select ot.order_id, count(*) as NumMatching
from order_tags ot join
     pizza_tags pt
     on ot.tag_id = pt.tag_id
where pt.pizza_id = @PIZZA_ID
group by ot.order_id
order by NumMatching desc;

Upvotes: 1

Related Questions