CODEWITHSUNDEEP
phpmysql
Roy Peleg
Roy Peleg

Reputation: 1030

What's the best way to display the number of items related to tags?

After following this recommendation regarding tags tables structure, I've implemented the following design:

Table: Item
Columns: ItemID, Title, Content

Table: Tag
Columns: TagID, Title

Table: ItemTag
Columns: ItemID, TagID

I'm facing another problem, I wanna display the number each tag appears in the system. What's the best way to do this in terms of search query / php code that will not cause a severe performance impact (tags count will appear for each item).

Thanks,

Roy

Upvotes: 0

Views: 206

Answers (3)

J__
J__

Reputation: 3837

SELECT Tag.Title, COUNT(Tag.TagID) AS TagCount
FROM ItemTag
LEFT JOIN Tag ON
(ItemTag.TagID = Tag.TagID)
GROUP BY Tag.Title
ORDER BY Tag.Title

Gives you a list of tags (ni alphabetical order) followed by the number of times they are used against an item. Unused tags do not appear.

Upvotes: 1

Instantsoup
Instantsoup

Reputation: 15265

Pretty sure the following (SQL) will get all the tag titles and associated counts of how often they appear on items.

select tag.title, count(itemtag.tagid) 
from tag, itemtag
where itemtag.tagid = tag.tagid
group by tag.title

Upvotes: 2

Tom Ritter
Tom Ritter

Reputation: 101380

select count(*)
from ItemTag
where TagId = X

is mySQL that will get it dynamically.

If you're worried about the performance hit, you should store a count in the tag table, and increment/decrement it when you tag or untag an item.

Upvotes: 2

Related Questions