SQL / PHP：按不同表格的最高投票排序

Question

I needed to recommend to the visitor similar items to the one they where looking at. So I asked in another question for a SQL query to get items with the same categories (from a 'categories' table). The next is the correct code I'm actually using:

SELECT c2.itemID
FROM categories c1
JOIN categories c2 ON c1.catID = c2.catID
WHERE c1.itemID = :id
AND c2.itemID <> :id
GROUP BY c2.itemID
ORDER BY count(c2.itemID) DESC;

It returns items ordered by the number of categories matches (from a table containing 'itemIDs' and 'catIDs'). So for example: item 1 have categories 2,3,4,5,6 and item 2 have categories 2,3,4,5,6 and item 3 have categories 3,5,6 then if I compare item 1 (the one the visitor is looking at) to item 2 and 3 I need to get item 2 first and then item 3 because item 2 have more categories matches than item 3.

NOW: I need also to to order the results by highest vote. The items votes are in a different table. The table 'votes' contain two columns: 'itemID' and 'total_value', 'total_value' being the final rate.

So, How to order the results also by the number of categories matches and also by highest vote from a different table???

Update: The number if items in the table is 8000+. I think the better thing to do to recommend the most similar items is to order it first by items with the exact set of categories and then by votes. Any ideas? Thanks!

Answer 1

SELECT c2.itemID
FROM categories c1
JOIN categories c2 ON c1.catID = c2.catID
JOIN votes v ON v.itemID=c1.itemId
WHERE c1.itemID = :id
AND c2.itemID <> :id
GROUP BY c2.itemID
ORDER BY count(c2.itemID), v.total_value DESC;