Arawak89 Arawak89 - 3 months ago 14
MySQL Question

Building a Tag Search Engine in Mysql - Trouble with sorting

I would like to do a simple search Engine about videos (like a very simple Shutterstock) based on PHP/MySQL.
So here's the deal. A video has multiple keywords (or tags). A user type in a search bar some keywords then he gets a list of the videos with these keywords in (ordered by relevance).

I've created 3 Mysql tables :


  • video : video_id, video_name, video_format, and so...

  • tag : tag_id, tag_name

  • video_tag : video_id, tag_id // A join table which link tag and video (as a video has 1 or multiple keywords and a keyword is used by 1 or multiple videos)



The relevance of the results is important, here is the logic:

If the user types in the search bar: water, wood and nature, the results would be like:

(1) the videos which associate water, wood and nature
(2) the videos which associate water and wood
(3) the videos which associate water and nature
(4) the videos which associate wood and nature
(5) the videos which associate water
(6) the videos which associate wood
(7) the videos which associate nature


So i've a made an algorithm in php which builds a multidimensional array of this kind :

[0] [water, wood, nature]
[1] [water, wood]
[2] [water, nature]
[3] [nature, water]
[4] [wood, nature]
[5] [water]
[6] [wood]
[7] [nature]


Next, I send this array in the MySQL request and here is where I'm stuck ... What I have for my request:

SELECT video.video_id,video.video_name, GROUP_CONCAT(tag.tag_name) AS tags

FROM
video_tag
INNER JOIN video
ON video_tag.video_id = video.video_id
INNER JOIN tag
ON tag.tag_id = video_tag.tag_id

GROUP BY video_id

HAVING tags LIKE '%water%' AND tags LIKE '%wood%' AND tags LIKE '%nature%.'
OR tags LIKE '%water%' AND tags LIKE '%wood%'
OR tags LIKE '%water%' AND tags LIKE '%nature%'
OR tags LIKE '%wood%' AND tags LIKE '%nature%'
OR tags LIKE '%water%'
OR tags LIKE '%wood%'
OR tags LIKE '%nature%'


But the order of the result is not right. Everything is sorted by video_id.

video_id video_name tags
1 video 01 wood, keyword4, keyword5, keyword 6, keyword 7
2 video 02 wood, nature, water, keyword 6
3 video 03 nature, water, keyword 7


The order I want is the following:

video_id video_name tags
2 video 02 wood, nature, water, keyword 6
3 video 03 nature, water, keyword 7
1 video 01 wood, keyword4, keyword5, keyword 6, keyword 7


Is there a way in Mysql to get the results ordered by the "natural" order of the request? Am I doing this the right way? Did someone already encountered this kind of trouble and can help me? :)

Answer

Thank you. With your proposition, I finally realised that I was overcomplicated myself. No need of a multidimensional array or combinatory logic ...

Here is the solution I found :

SELECT DISTINCT video.video_name, GROUP_CONCAT(tag.tag_name) AS tags

FROM
    video_tag
    INNER JOIN video
      ON video_tag.video_id = video.video_id
    INNER JOIN tag
      ON tag.tag_id = video_tag.tag_id

WHERE tag.tag_name= 'wood' OR tag.tag_name = 'nature' OR tag.tag_name = 'corporate'

GROUP BY video_id
ORDER BY COUNT(*) DESC;

Like that, I select all the videos linked with the needed tags. Then I just count how many tags a video have... And sort with that number... Finally, my trouble was very easy.

Sorry for the disturbance ;)

Comments