Search code examples
mysqlsqlkohanakohana-3kohana-orm

MySQL - How to query items by tags, order by matched tag count

My schema looks something like this:

items ( id, title, blah )
tags (id, name )
item_tags ( item_id, tag_id )

I want to list all items, where the item's tags are "in" an array of selected tags, and then order by the number of tags that match the selection (e.g. [1, 2, 3])

What I have so far is:

SELECT *, COUNT(item_tags.tag_id) AS tag_count
FROM items
JOIN item_tags
ON item_tags.item_id = items.id
WHERE item_tags.tag_id IN (1, 2, 3)
GROUP BY items.id
ORDER BY tag_count DESC

This works well, except the tag_count just gets the total number of tags for the item selected, i want it be the number of tags selected that are contained in (1, 2, 3).

An item with tags (1, 2, 3) should come before an item with tags (1, 5, 6, 7).

I am using Kohana 3's ORM if there is a solution that way.

Solution

  • Simply change your SELECT to:

    SELECT *, COUNT(*) AS tag_count
.....