I want to create a weighted tag cloud, but afaik the TagService provides me only a distinct list of tags. Is there an API method that exposes the count per tag or a non-distinct list of all tags?
Yeah, it does seem a big problem that you don't get a Node Count with the Tag Service. One of the main things people do with tags is create a Tag Cloud, which would be weighted by node count.
To get around this I wrote my own helper method:
First create a simple DTO class:
public class TagWithCount
{
public int Id { get; set; }
public string Text { get; set; }
public int NodeCount { get; set; }
public string Group { get; set; }
}
Then write an SQL query to get the result:
public static IEnumerable<TagWithCount> GetTagsInGroup(string group)
{
var db = ApplicationContext.Current.DatabaseContext.Database;
const string sql = @"SELECT T.id, T.[group], tag as Text, COUNT(N.id) as NodeCount
FROM dbo.cmsTagRelationship TR
INNER JOIN dbo.cmsTags T ON T.id = TR.tagId
INNER JOIN dbo.umbracoNode N ON TR.nodeId = N.id
WHERE [group] = @0
AND N.trashed = 0
GROUP BY T.id, T.[group], tag
ORDER BY NodeCount desc, tag asc";
return db.Fetch<TagWithCount>(sql, group);
}
It seems to work, but haven't tested it extensively.
Your code almost works... and I don't know why it's not working correctly *(unless the db.Fetch method is not mapping as it's supposed to)
I've tweaked your code a bit to include only the tags in a particular blog (i'm working on a site that has multiple blogs) and when I run it in SQL Management, it pulls up the data just fine
But when I run it in umbraco, all the NodeCount properties are being set to 1 (one). Does anyone know if there's a bug with Fetch?
Code behind
public IEnumerable<TagWithCount> GetTagsForBlog(DynamicNode node)
{
var db = ApplicationContext.Current.DatabaseContext.Database;
dynamic posts = node.Descendants("BlogPost");
string[] ids = new string[posts.Count()];
for(int i=0; i < ids.Length; i++)
ids[i] = posts[i].Id.ToString();
const string sql = @"SELECT T.id, T.[group], tag as Text, CAST(COUNT(N.id) AS INT) as NodeCount
FROM dbo.cmsTagRelationship TR
INNER JOIN dbo.cmsTags T ON T.id = TR.tagId
INNER JOIN dbo.umbracoNode N ON TR.nodeId = N.id
WHERE TR.nodeId IN (@0)
AND
N.trashed = 0
GROUP BY T.id, T.[group], tag
ORDER BY NodeCount desc, tag asc";
return db.Fetch<TagWithCount>(sql,ids);
}
*used the same TagWithCount class
public class TagWithCount
{
public int Id { get; set; }
public string Text { get; set; }
public int NodeCount { get; set; }
public string Group { get; set; }
}
public IEnumerable<TagWithCount> GetTagsForBlog(DynamicNode node)
{
var db = ApplicationContext.Current.DatabaseContext.Database;
int id = node.Id;
const string sql = @"SELECT T.[group], tag as Text, COUNT(N.id) as NodeCount
FROM dbo.cmsTagRelationship TR
INNER JOIN dbo.cmsTags T ON T.id = TR.tagId
INNER JOIN dbo.umbracoNode N ON TR.nodeId = N.id
WHERE N.parentId = @0
AND
N.trashed = 0
GROUP BY T.[group], tag
ORDER BY NodeCount desc, tag asc";
return db.Fetch<TagWithCount>(sql,id);
}
Ahh, it seems they have added the NodeCount to the TagService but not the TagQuery helper (that hangs of the UmbracoHelper object). So the following query (in 7.17) does return a NodeCount as it returns a collection of ITag objects, which have a NodeCount:
var tagsFromService = UmbracoContext.Application.Services.TagService.GetAllContentTags();
But the following query doesn't bring back a NodeCount :
var tags = Umbraco.TagQuery.GetAllTags();
This returns a collection of TagModel objects, that don't have the TagCount property.
Seems a bit strange! But the good news is that the queries return the same results a my home-rolled version. I suspect mine will be a tiny bit bit faster, too :)
Get tag count in v7
Hi,
I want to create a weighted tag cloud, but afaik the TagService provides me only a distinct list of tags. Is there an API method that exposes the count per tag or a non-distinct list of all tags?
TIA, André
Hello,
Has this been solved or implemented somehow?
Also looking to resolve this - any update?
Yeah, it does seem a big problem that you don't get a Node Count with the Tag Service. One of the main things people do with tags is create a Tag Cloud, which would be weighted by node count.
To get around this I wrote my own helper method:
First create a simple DTO class:
public class TagWithCount { public int Id { get; set; } public string Text { get; set; } public int NodeCount { get; set; } public string Group { get; set; } }Then write an SQL query to get the result:
public static IEnumerable<TagWithCount> GetTagsInGroup(string group) { var db = ApplicationContext.Current.DatabaseContext.Database; const string sql = @"SELECT T.id, T.[group], tag as Text, COUNT(N.id) as NodeCount FROM dbo.cmsTagRelationship TR INNER JOIN dbo.cmsTags T ON T.id = TR.tagId INNER JOIN dbo.umbracoNode N ON TR.nodeId = N.id WHERE [group] = @0 AND N.trashed = 0 GROUP BY T.id, T.[group], tag ORDER BY NodeCount desc, tag asc"; return db.Fetch<TagWithCount>(sql, group); }It seems to work, but haven't tested it extensively.
Your code almost works... and I don't know why it's not working correctly *(unless the db.Fetch method is not mapping as it's supposed to)
I've tweaked your code a bit to include only the tags in a particular blog (i'm working on a site that has multiple blogs) and when I run it in SQL Management, it pulls up the data just fine
But when I run it in umbraco, all the NodeCount properties are being set to 1 (one). Does anyone know if there's a bug with Fetch?
Code behind
*used the same TagWithCount class
I've changed the TSQL a bit, and now it works
The count property was added in version 7.1.5 I believe. Haven't had chance to use it yet to verify, but the code was added for that version.
http://issues.umbraco.org/issue/U4-5290
Andy
Ahh, it seems they have added the NodeCount to the TagService but not the TagQuery helper (that hangs of the UmbracoHelper object). So the following query (in 7.17) does return a NodeCount as it returns a collection of ITag objects, which have a NodeCount:
But the following query doesn't bring back a NodeCount :
This returns a collection of TagModel objects, that don't have the TagCount property.
Seems a bit strange! But the good news is that the queries return the same results a my home-rolled version. I suspect mine will be a tiny bit bit faster, too :)
Hopefully this will help some of you but I'm running 7.10.4 so I'm not sure how far back this will work but it uses the TagModel
I think it goes back to 7.2.0 - I reminded Shannon and it was added in 7.2.0 - https://issues.umbraco.org/issue/U4-5649
is working on a reply...