internal static IEnumerable <PostsByTagModel> GetContentByTags(this UmbracoHelper helper, IMasterModel masterModel, string tagGroup, string baseUrlName)
{
var tags = helper.TagQuery.GetAllContentTags(tagGroup).ToArray();
if (!tags.Any())
{
return(Enumerable.Empty <PostsByTagModel>());
}
//TODO: We want to use the core for this but it's not available, this needs to be implemented: http://issues.umbraco.org/issue/U4-9290
var appContext = helper.UmbracoContext.Application;
var sqlSyntax = appContext.DatabaseContext.SqlSyntax;
IEnumerable <PostsByTagModel> GetResult()
{
var taggedContent = new List <TagDto>();
//process in groups to not exceed the max SQL params
foreach (var tagBatch in tags.InGroupsOf(2000))
{
var sql = GetTagQuery("cmsTagRelationship.nodeId, cmsTagRelationship.tagId, cmsTags.tag", masterModel, sqlSyntax)
.Where("tagId IN (@tagIds) AND cmsTags." + sqlSyntax.GetQuotedColumnName("group") + " = @tagGroup", new
{
tagIds = tagBatch.Select(x => x.Id).ToArray(),
tagGroup = tagGroup
});
taggedContent.AddRange(appContext.DatabaseContext.Database.Fetch <TagDto>(sql));
}
var result = new List <PostsByTagModel>();
foreach (var groupedTags in taggedContent.GroupBy(x => x.TagId))
{
//will be the same tag name for all of these tag Ids
var tagName = groupedTags.First().Tag;
var publishedContent = helper.TypedContent(groupedTags.Select(t => t.NodeId).Distinct()).WhereNotNull();
var model = new PostsByTagModel(
publishedContent.Select(c => new PostModel(c)).OrderByDescending(c => c.PublishedDate),
tagName,
masterModel.RootBlogNode.Url.EnsureEndsWith('/') + baseUrlName + "/" + tagName.ToLowerInvariant());
result.Add(model);
}
return(result.OrderBy(x => x.TagName).ToArray());
}
#if DEBUG
return(GetResult());
#else
//cache this result for a short amount of time
return((IEnumerable <PostsByTagModel>)appContext.ApplicationCache.RuntimeCache.GetCacheItem(
string.Concat(typeof(UmbracoHelperExtensions).Name, "GetContentByTags", masterModel.RootBlogNode.Id, tagGroup),
getResult, TimeSpan.FromSeconds(30)));
#endif
}
internal static PostsByTagModel GetContentByTag(this UmbracoHelper helper, IMasterModel masterModel, string tag, string tagGroup, string baseUrlName)
{
//TODO: We want to use the core for this but it's not available, this needs to be implemented: http://issues.umbraco.org/issue/U4-9290
var appContext = helper.UmbracoContext.Application;
var sqlSyntax = appContext.DatabaseContext.SqlSyntax;
Func <PostsByTagModel> getResult = () =>
{
var sql = GetTagQuery("cmsTagRelationship.nodeId, cmsTagRelationship.tagId, cmsTags.tag", masterModel, sqlSyntax)
.Where("cmsTags.tag = @tagName AND cmsTags." + sqlSyntax.GetQuotedColumnName("group") + " = @tagGroup", new
{
tagName = tag,
tagGroup = tagGroup
});
var taggedContent = appContext.DatabaseContext.Database.Fetch <TagDto>(sql);
var result = new List <PostsByTagModel>();
foreach (var groupedTags in taggedContent.GroupBy(x => x.TagId))
{
//will be the same tag name for all of these tag Ids
var tagName = groupedTags.First().Tag;
var publishedContent = helper.TypedContent(groupedTags.Select(t => t.NodeId).Distinct()).WhereNotNull();
var model = new PostsByTagModel(
publishedContent.Select(c => new PostModel(c)).OrderByDescending(c => c.PublishedDate),
tagName,
masterModel.RootBlogNode.Url.EnsureEndsWith('/') + baseUrlName + "/" + tagName.ToLowerInvariant());
result.Add(model);
}
return(result.FirstOrDefault());
};
#if DEBUG
return(getResult());
#else
//cache this result for a short amount of time
return(appContext.ApplicationCache.RuntimeCache.GetCacheItem <PostsByTagModel>(
string.Concat(typeof(UmbracoHelperExtensions).Name, "GetContentByTag", masterModel.RootBlogNode.Id, tagGroup),
getResult, TimeSpan.FromSeconds(30)));
#endif
}
/// <summary>
/// Returns an RSS feed URL specific to this tag
/// </summary>
/// <param name="url"></param>
/// <param name="model"></param>
/// <returns></returns>
public static string ArticulateTagRssUrl(this UrlHelper url, PostsByTagModel model)
{
return(model.TagUrl.EnsureEndsWith('/') + "rss");
}
internal static PostsByTagModel GetContentByTag(this UmbracoHelper helper, IMasterModel masterModel, string tag, string tagGroup, string baseUrlName, long page, long pageSize)
{
//TODO: We want to use the core for this but it's not available, this needs to be implemented: http://issues.umbraco.org/issue/U4-9290
var sqlSyntax = Current.SqlContext.SqlSyntax;
PostsByTagModel GetResult()
{
var sqlTags = GetTagQuery($"{Constants.DatabaseSchema.Tables.Node}.id", masterModel, sqlSyntax);
//For whatever reason, SQLCE and even SQL SERVER are not willing to lookup
//tags with hyphens in them, it's super strange, so we force the tag column to be - what it already is!! what tha.
sqlTags.Where($"CAST({Constants.DatabaseSchema.Tables.Tag}.tag AS NVARCHAR(200)) = @tagName AND {Constants.DatabaseSchema.Tables.Tag}." + sqlSyntax.GetQuotedColumnName("group") + " = @tagGroup", new
{
tagName = tag,
tagGroup = tagGroup
});
//get the publishedDate property type id on the ArticulatePost content type
using (var scope = Current.ScopeProvider.CreateScope())
{
var publishedDatePropertyTypeId = scope.Database.ExecuteScalar <int>($@"SELECT {Constants.DatabaseSchema.Tables.PropertyType}.id FROM {Constants.DatabaseSchema.Tables.ContentType}
INNER JOIN {Constants.DatabaseSchema.Tables.PropertyType} ON {Constants.DatabaseSchema.Tables.PropertyType}.contentTypeId = {Constants.DatabaseSchema.Tables.ContentType}.nodeId
WHERE {Constants.DatabaseSchema.Tables.ContentType}.alias = @contentTypeAlias AND {Constants.DatabaseSchema.Tables.PropertyType}.alias = @propertyTypeAlias", new { contentTypeAlias = "ArticulatePost", propertyTypeAlias = "publishedDate" });
var sqlContent = GetContentByTagQueryForPaging($"{Constants.DatabaseSchema.Tables.Node}.id, {Constants.DatabaseSchema.Tables.PropertyData}.dateValue", masterModel, sqlSyntax, publishedDatePropertyTypeId);
sqlContent.Append($"WHERE ({Constants.DatabaseSchema.Tables.Node}.id IN (").Append(sqlTags).Append("))");
//order by the dateValue field which will be the publishedDate
sqlContent.OrderBy($"({Constants.DatabaseSchema.Tables.PropertyData}.dateValue) DESC");
//Put on a single line! NPoco paging does weird stuff on multiline
sqlContent = Current.SqlContext.Sql(sqlContent.SQL.ToSingleLine(), sqlContent.Arguments);
//TODO: ARGH This still returns multiple non distinct Ids :(
var taggedContent = scope.Database.Page <int>(page, pageSize, sqlContent);
var result = new List <PostsByTagModel>();
var publishedContent = helper.Content(taggedContent.Items).WhereNotNull();
var model = new PostsByTagModel(
publishedContent.Select(c => new PostModel(c)),
tag,
masterModel.RootBlogNode.Url.EnsureEndsWith('/') + baseUrlName + "/" + tag.ToLowerInvariant(),
Convert.ToInt32(taggedContent.TotalItems));
result.Add(model);
scope.Complete();
return(result.FirstOrDefault());
}
}
#if DEBUG
return(GetResult());
#else
//cache this result for a short amount of time
return((PostsByTagModel)Current.AppCaches.RuntimeCache.Get(
string.Concat(typeof(UmbracoHelperExtensions).Name, "GetContentByTag", masterModel.RootBlogNode.Id, tagGroup, tag, page, pageSize),
GetResult, TimeSpan.FromSeconds(30)));
#endif
}
public static PostsByTagModel GetContentByTag(this UmbracoHelper helper, IMasterModel masterModel, string tag, long page, long pageSize)
{
var appContext = helper.UmbracoContext.Application;
var sqlSyntax = appContext.DatabaseContext.SqlSyntax;
PostsByTagModel GetResult()
{
var sqlTags = GetTagQuery("umbracoNode.id", masterModel, sqlSyntax);
if (sqlSyntax is MySqlSyntaxProvider)
{
sqlTags.Where("cmsTags.tag = @tagName", new
{
tagName = tag
});
}
else
{
//For whatever reason, SQLCE and even SQL SERVER are not willing to lookup
//tags with hyphens in them, it's super strange, so we force the tag column to be - what it already is!! what tha.
sqlTags.Where("CAST(cmsTags.tag AS NVARCHAR(200)) = @tagName", new
{
tagName = tag
});
}
//get the publishedDate property type id on the ArticulatePost content type
var publishedDatePropertyTypeId = appContext.DatabaseContext.Database.ExecuteScalar <int>(@"SELECT cmsPropertyType.id FROM cmsContentType
INNER JOIN cmsPropertyType ON cmsPropertyType.contentTypeId = cmsContentType.nodeId
WHERE cmsContentType.alias = @contentTypeAlias AND cmsPropertyType.alias = @propertyTypeAlias", new { contentTypeAlias = "BlogPost", propertyTypeAlias = "publishedDate" });
var sqlContent = GetContentByTagQueryForPaging("umbracoNode.id", masterModel, sqlSyntax, publishedDatePropertyTypeId);
sqlContent.Append("WHERE umbracoNode.id IN (").Append(sqlTags).Append(")");
//order by the dataDate field which will be the publishedDate
sqlContent.OrderBy("cmsPropertyData.dataDate DESC");
//TODO: ARGH This still returns multiple non distinct Ids :(
var taggedContent = appContext.DatabaseContext.Database.Page <int>(page, pageSize, sqlContent);
var result = new List <PostsByTagModel>();
var publishedContent = helper.TypedContent(taggedContent.Items).WhereNotNull();
var model = new PostsByTagModel(
publishedContent.Select(c => new PostListItemModel(c)),
tag,
"/Category/" + tag.ToLowerInvariant(),
Convert.ToInt32(taggedContent.TotalItems));
result.Add(model);
return(result.FirstOrDefault());
}
#if DEBUG
return(GetResult());
#else
//cache this result for a short amount of time
return((PostsByTagModel)appContext.ApplicationCache.RuntimeCache.GetCacheItem(
string.Concat(typeof(UmbracoHelperExtensions).Name, "GetContentByTag", masterModel.RootBlogNode.Id, tagGroup, tag, page, pageSize),
GetResult, TimeSpan.FromSeconds(30)));
#endif
}
