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 }