CODEWITHSUNDEEP
mysqlsql
jeff
jeff

Reputation: 13653

How to get related questions using tag_ids in MySQL?

I have a MySQL database structure which has questions and tags, something like stackoverflow.

So my three tables are like this :

questions:
-----------------------
id | content | asked_on

tags:
--------
id | tag

question tags:
---------------
id | qid | tid

and I'm looking for a "pure-SQL" way to get related questions based on a given row from questions table.

By related questions, I mean I want to find the number of mutual tags.

And of course I can construct the SQL query in PHP, so I can do something like : 

COUNT xyz ..... WHERE tags.id = 17 OR tags.id = 42 OR etc..

but in the end, I should have something like :

qid  number_of_mutual_tags
---  --------------------
42     4
12     3
25     3
...

Sample Case:

suppose the reference question has tags php,sql,mysql,javascript,html

the table above is constructed with these questions :

id   tags  // of course this is just a representation of the three MySQL tables
---  ----
42   php,sql,mysql,javascript,xyz
12   php,sql,mysql , abc
25   sql,mysql,javascript, ijk

of course it should have a LIMIT clause.

What Have I Tried?

Frankly, I cannot do anything since I'm not so good with SQL. I guess I have to do something with JOIN and COUNT commands but how ?

Thanks for any help !

Upvotes: 1

Views: 62

Answers (2)

Iłya Bursov
Iłya Bursov

Reputation: 24146

So, in your code you have some kind of array with tags of currently displayed question, let it be $array

so, you query to get related questions, ordered by count of mutual tags will be:

$query = 'select qid, count(*)
from questiontags
where tid in (' . implode(',', $array) . ')
group by qid
order by count(*) desc';

if you don't have this array, you can change query to

$query = 'select qid, count(*)
from questiontags
where tid in (select tid from questiontags where qid=' . $CURRENTQUESTIONID . ')
group by qid
order by count(*) desc';

and good point from @Gordon Linoff - add condition qid <> $CURRENTQUESTIONID

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Use a join to find the tags in common. Then aggregate to count them:

select qt.qid, count(qtx.tid) as number_of_mutual_tags
from questiontags qt left outer join
     questiontags qtx
     on qt.tid = qtx.tid and
        qtx.qid = XX 
group by qt.qid
order by number_of_mutual_tags desc;

If you want to exclude the original question, you can add the condition: qt.qid <> qtx.qid.

Upvotes: 4

Related Questions