How to implement tag counting

Question

I have my tags like these in my database:

Table: Item Columns: ItemID, Title, Content Table: Tag Columns: TagID, Title Table: ItemTag Columns: ItemID, TagID //example -- this is the right sidebar of stackoverflow c# × 59279 sql × 14885 asp.net-mvc × 9123 linq × 4337 tags × 339 

if I wanted to know the amount of each tag, for example, how stackoverflow counts their tags, how can I do this? What request I would fulfill. I am open to both regular sql and linq

Answer 1

Add another column to the column that acts as a counter. When you add or remove a tag from an item that you are updating the counter (in other words, when adding a line to the Itemtag, increase the counter in the Tag table when you delete the counter decrement)

add tag to element:

 INSERT INTO Itemtag (itemid,tagid) VALUES ('$itemid','$tagid'); UPDATE Tag SET counter=counter+1 WHERE tagid='$tagid'; 

remove tag from element

 DELETE FROM Itemtag WHERE itemid='$itemid' AND tagid='$tagid'; UPDATE Tag SET counter=counter-1 WHERE tagid='$tagid'; 

get element tags using counter

 SELECT t.title, t.counter FROM Itemtag AS it JOIN Tag AS t ON t.idtag=it.tagid WHERE it.itemid='$itemid' 

Answer 2

 select t.Title, count(it.TagID) as TagCount from Tag t inner join ItemTag it on t.TagID = it.TagID inner join Item i on it.ItemID = i.ItemID where i.ItemID = @currentItemID -- optional, if you only want current page group by t.Title 

Answer 3

You can use another column in Item to store the tag counter and synchronize it when adding or removing tags.

Answer 4

 SELECT title, count(*) FROM tag JOIN itemtag ON itemtag.tagid = tag.tagid GROUP BY title