CODEWITHSUNDEEP
sql-serverdatabase-designtags
leora
leora

Reputation: 196639

Getting related tags from SQL Server when you have filtered down

I asked this question before and got a great working answer.

what is the query to get "related tags" like in stack overflow

but i realized that SOF actually takes it one step further as it supports multiple tag drilldown

what i mean is, if click on the tag C#, that will filter to 20,000 questions and it will show me all related tags on questions that also have C# as a tag.

but then, i can click on the tag "Collections" and now see total number of questions with the tag "C#" and "Collections". maybe that 500 questions

in addition, it will still show me related tags, which are tags that exist in questions which also have tag "C#" and "Collections"

and you can keep filtering down.

so the answer in the above link, only support 1 single tag.

in sql how would you get this to work to be able to completely drill down and show count of related tags and allow the user to keep choosing additional tags for drilldown.

Upvotes: 2

Views: 549

Answers (3)

Anwar Chandra
Anwar Chandra

Reputation: 8648

 SELECT t.tagName
 FROM tags t INNER JOIN tagsBridge tb on t.id=tb.tagID

 where tb.linkID in (

   select tb.linkID from tagsBridge tb
   where tb.tagID= @first_tag

   intersect

   select tb.linkID from tagsBridge tb
   where tb.tagID= @second_tag

   intersect

   select tb.linkID from tagsBridge tb
   where tb.tagID= @third_tag

   intersect

   select tb.linkID from tagsBridge tb
   where tb.tagID= @fourth_tag

   --- // you can continue
 )

)

Upvotes: 0

Lukáš Lalinský
Lukáš Lalinský

Reputation: 41306

I'd use something like this as the first version. @FirstTagID and @SecondTagID must be sorted by ID. You can query on more tags by adding more joins to the inner query.

SELECT tagName
FROM tags
WHERE id IN (
    SELECT tagID
    FROM tagsBridge
    WHERE linkID IN (
        SELECT t1.linkID
        FROM
            tagsBridge t1
            JOIN tagsBridge t2 ON t2.linkID=t1.linkID AND t2.tagID > t1.tagID
        WHERE t1.tagID=@FirstTagID AND t2.tagID=@SecondTagID
    )
    AND tagID!=@FirstTagID AND tagID!=@SecondTagID
)

Or if you want to include the number of links:

SELECT t.tagName, count(*)
FROM 
    tags t
    JOIN tagsBridge tb ON tb.tagID = t.id
WHERE
    tb.linkID IN (
        SELECT t1.linkID
        FROM
            tagsBridge t1
            JOIN tagsBridge t2 ON t2.linkID=t1.linkID AND t2.tagID > t1.tagID
        WHERE t1.tagID=@FirstTagID AND t2.tagID=@SecondTagID
    )
    AND t.tagID!=@FirstTagID AND t.tagID!=@SecondTagID
GROUP BY t.tagName

The same for three tags:

SELECT t.tagName, count(*)
FROM 
    tags t
    JOIN tagsBridge tb ON tb.tagID = t.id
WHERE
    tb.linkID IN (
        SELECT t1.linkID
        FROM
            tagsBridge t1
            JOIN tagsBridge t2 ON t2.linkID=t1.linkID AND t2.tagID > t1.tagID
            JOIN tagsBridge t3 ON t3.linkID=t2.linkID AND t3.tagID > t2.tagID
        WHERE t1.tagID=@FirstTagID AND t2.tagID=@SecondTagID AND t3.tagID=@ThirdTagID
    )
    AND t.tagID!=@FirstTagID AND t.tagID!=@SecondTagID AND t.tagID!=@ThirdTagID
GROUP BY t.tagName

Upvotes: 4

246tNt
246tNt

Reputation: 2170

Let's say you have a table called entity_tags linking entities with the tags like this :

entity_id : INTEGER
tag_id    : INTEGER

Let's say you user as selected N tags @1, @2, ... @N. To get the other tags associated with the results (and as a bonus, their frequency of occurence):

SELECT et.tag_id, COUNT(et.entity_id) as frequency FROM
 entity_tags AS et
 JOIN entity_tags AS et1 ON (et1.entity_id=et.entity_id AND et1.tag_id=@1)
 ...
 JOIN entity_tags AS etN ON (etN.entity_id=et.entity_id AND etN.tag_id=@N)
WHERE et.tag_id NOT IN (@1, @2, ... @N)
GROUP BY et.tag_id;

Upvotes: 1

Related Questions