CODEWITHSUNDEEP
mysqlsqldatabase
roy naufal
roy naufal

Reputation: 389

mysql select ... in , count several match relevance

I am trying to do a 'similar posts' mysql string, all my posts have tags, so I am trying with this query:

SELECT articles.id,title,cover_image 
FROM articles 
INNER JOIN article_tags ON (article_tags.article_id = articles.id) 
WHERE  article_tags.tag_id 
IN (312,13016,109,120,1493,103,1667,13015,331,5,564,13014,1670,113,13045) 
AND articles.id <> 88230

it does the job somehow, but the problem is the posts are not really relevant to the original post, for example if one tag is 'cool', then i would get any post with tag cool

ideally, what i want is to sort these results by how many occurrences of tags there are, for example:

article 1 has tags: tag1, tag2, tag3, tag4, tag5
article 2 has tags: tag2, tag3, tag4,
article 3 has tags: tag1, tag4
article 4 has tags: tag1, tag2, tag4, tag5

so I'd like sql to return this set of results:

tag1,tag4, tag2, tag3

Upvotes: 0

Views: 123

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You are on the right track. Just use group by to find the number of matches and order by that:

SELECT articles.id, title, cover_image 
FROM articles INNER JOIN
     article_tags
     ON article_tags.article_id = articles.id
WHERE article_tags.tag_id IN (312,13016,109,120,1493,103,1667,13015,331,5,564,13014,1670,113,13045) AND
      articles.id <> 88230 
GROUP BY articles.id,title, cover_image
ORDER BY count(*) DESC;

You can include count(*) as NumMatches in the select as well.

Upvotes: 2

Related Questions