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 ta开发者_高级运维gs (1, 5, 6, 7).

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

Simply change your SELECT to:

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

I think what you really want is a GROUP_CONCAT( tag_id ) (maybe in addition to your count). The group concat will concatinate your IDs such as you've shown... 1, 2, 3 and 1, 5, 6, 7. If you use THAT column as your order by, you should be good.

Now, that said, you might have to force some formatting of the concatination process to handle things like 1, 10, 100 coming before 1, 2, 3. As such, if formatting the concatinated ID strings to say... 2 or 3 positions, you would get

001, 002, 003 
vs
001, 005, 006, 007 
vs
001, 010, 100

HTH