[SOLVED] return list of similar tagged urls mysql

Issue

urls table

url date
http://url1 a
http://url2 b
http://url3 c
http://url4 d

tags table

url tags
http://url1 x
http://url1 y
http://url2 x
http://url2 y
http://url3 x
http://url3 z
http://url4 z

the url table has url data and tags table has url tags data.

I want urls having similar tags ordered by the count of similar tags

eg1:
if i want URLs which has similar tags as URL ‘http://url1’
the query should return

url similar tags
http://url2 x,y
http://url3 x

‘http://url1’ has x,y

‘http://url2’ has x,y in common with ‘http://url1’

‘http://url3’ has x in common with ‘http://url1’

eg2:
for ‘http://url4’

url similar tags
http://url3 z

‘http://url3’ has z in common with ‘http://url4’

so urls should be returned order by similar tags count (desc)

note: The query should only return similar urls for specified url like in eg1 ‘http://url1’

i am not good at larger sql queries, so i need to ask this.

Thank you in advance

Solution

SELECT
    urls.url,
    GROUP_CONCAT(t1.tags) as "similar tags"
FROM urls
INNER JOIN tags t1 ON t1.url=urls.url
INNER JOIN tags t2 ON t2.url='http://url1' and t2.tags = t1.tags
GROUP BY urls.url

Optionally you can add a WHERE urls.url <> 'http://url1' (Before the GROUP BY, and after the last INNER JOIN)

see: DBFIDDLE

EDIT: Starting from the table tags, like @Andrea_86 suggest, also works:

SELECT 
   t1.url, 
   GROUP_CONCAT(t1.tags) as "similar tags" 
FROM tags t1 
INNER JOIN tags t2 ON t2.url='http://url1' and t2.tags = t1.tags 
GROUP BY t1.url

Answered By – Luuk

Answer Checked By – Cary Denson (BugsFixing Admin)

Leave a Reply

Your email address will not be published.