CODEWITHSUNDEEP
phpmysqlsql
Friendly Code
Friendly Code

Reputation: 1645

MySQL INNER JOIN ordering by multiple matches first

I have the following tables

gifts - a list of products
tags - a list of tags that can be applied to products
tags_gifts - a join for gifts and tags if they are applicable

Here's two tag examples (id, name):

508 - jewellery
7 - gold

I have the following SQL for results:

SELECT * FROM gifts 
LEFT JOIN tags_gifts ON tags_gifts.gift_id = gifts.gift_id 
INNER JOIN tags ON tags.id = tags_gifts.tag_id 
WHERE published = '1' AND ( (tags_gifts.tag_id = '508' OR tags_gifts.tag_id = '7') ) 
GROUP BY gifts.gift_id 
ORDER BY gift_popularity DESC LIMIT 0,20

This works fine and shows all results with matches of 'jewellery' or 'gold' and orders them by popularity, but I'd like to show multiple matches first eg. A product matches both tags, and then order the remaining products by popularity.

Can't figure out how to do this - thanks

Upvotes: 3

Views: 239

Answers (2)

Ajay Gupta
Ajay Gupta

Reputation: 514

This will work and will be fast, you can adjust "a LIMIT 20 OFFSET 0" as you wish.

SELECT a.* FROM (
    SELECT * FROM gifts 
    LEFT JOIN tags_gifts ON tags_gifts.gift_id = gifts.gift_id 
    INNER JOIN tags ON tags.id = tags_gifts.tag_id 
    WHERE published = '1' AND ( (tags_gifts.tag_id = '508' AND tags_gifts.tag_id = '7')  ) 
    GROUP BY gifts.gift_id 
    ORDER BY gift_popularity DESC  
)  UNION (
    SELECT * FROM gifts 
    LEFT JOIN tags_gifts ON tags_gifts.gift_id = gifts.gift_id 
    INNER JOIN tags ON tags.id = tags_gifts.tag_id 
    WHERE published = '1' AND ( (tags_gifts.tag_id = '508' OR tags_gifts.tag_id = '7') ) 
    GROUP BY gifts.gift_id 
    ORDER BY gift_popularity DESC
) a LIMIT 0,20

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You can use COUNT() in ORDER BY ,so if count returns 2 it means product has both tags 

SELECT * FROM gifts 
LEFT JOIN tags_gifts ON tags_gifts.gift_id = gifts.gift_id 
INNER JOIN tags ON tags.id = tags_gifts.tag_id 
WHERE published = '1' AND ( (tags_gifts.tag_id = '508' OR tags_gifts.tag_id = '7') ) 
GROUP BY gifts.gift_id 
ORDER BY COUNT(*) DESC , gift_popularity DESC
 LIMIT 0,20

Upvotes: 4

Related Questions