Dynamic SQL
-
Yes, still from our beloved forum software:
public static string BuildGalleryThreadQuery(GalleryThreadQuery query, string databaseOwner)
{StringBuilder sb = new StringBuilder();
// Start with the basic tables
sb.AppendFormat("select T.ThreadID from {0}.cs_Threads T ", databaseOwner);
sb.AppendFormat("right join {0}.cs_Posts P on (P.ThreadID = T.ThreadID and P.PostLevel = 1) ", databaseOwner);
sb.AppendFormat("right join {0}.cs_Sections F on (F.SectionID = T.SectionID and F.ApplicationType = {1}) ", databaseOwner, (int)ApplicationType.Gallery);
sb.AppendFormat("left join {0}.cs_PostMetadata M on (M.PostID = P.PostID and M.MetaKey = '{1}') ", databaseOwner, ExifProperty.DateTimeOriginal.ToString());// Are we filtering by CategoryID (this has nothing to do with returning categories)
if(query.HasCategory || query.HasNoCategories)
sb.AppendFormat("left join {0}.cs_Posts_InCategories PC on (PC.PostID = P.PostID)",databaseOwner);sb.Append("where ");
if(query.HasFolder)
sb.AppendFormat("T.SectionID = {0} and ", query.SectionID);if(query.IsGalleryEnabled)
sb.Append("F.IsActive = 1 and ");if(query.OnlyApproved)
sb.AppendFormat("T.IsApproved = 1 and ");if(query.HasGroup)
sb.AppendFormat("P.SectionID = F.SectionID and F.GroupID = {0} and ", query.GroupID);if(query.FilterKey.Length > 0)
sb.AppendFormat("F.SectionID in ({0}) and ", query.FilterKey);// Add standard filters
sb.AppendFormat("T.SettingsID = {0} and P.SettingsID = {0} ", ProviderHelper.Instance().GetSettingsID());// Add category filter if necessary
if(!query.HasNoCategories && query.HasCategory)
sb.AppendFormat(" and PC.CategoryID = {0}", query.CategoryID);// Ordering
string order = "desc";
if(query.SortOrder == SortOrder.Ascending)
order = "asc";// Sort by
string groupBy = string.Empty;
string orderBy = string.Empty;
switch(query.SortBy)
{
case GalleryThreadSortBy.Author:
orderBy = " order by P.PostAuthor";
groupBy = "P.PostAuthor";
break;
case GalleryThreadSortBy.Comments:
orderBy = " order by T.TotalReplies";
groupBy = "T.TotalReplies";
break;
case GalleryThreadSortBy.Rating:
orderBy = " order by case when T.TotalRatings > 0 then (convert(decimal,T.RatingSum) / convert(decimal,T.TotalRatings)) else 0 end";
groupBy = "T.TotalRatings, T.RatingSum";
break;
case GalleryThreadSortBy.Subject:
orderBy = " order by P.Subject";
groupBy = "P.Subject";
break;
case GalleryThreadSortBy.Views:
orderBy = " order by T.TotalViews";
groupBy = "T.TotalViews";
break;
default:
case GalleryThreadSortBy.ThreadDate:
orderBy = " order by P.PostDate";
groupBy = "P.PostDate";
break;
case GalleryThreadSortBy.PictureDate:
orderBy = " order by M.MetaValue " + order + ", P.PostDate";
groupBy = "M.MetaValue, P.PostDate";
break;
}// Add group by and having if necessary, then order by
if(query.HasNoCategories)
{
sb.Append(" group by T.ThreadID, " + groupBy);
sb.Append(" having count(PC.CategoryID) = 0");
}
sb.Append(orderBy + " " + order);// Send back the query
return sb.ToString();
}
-
I love these bits of code:
if(query.HasCategory || query.HasNoCategories)
If it has a category, or it doesn't have a category. or this 'if' statement is extremely redundant, then...
Later on:
if(!query.HasNoCategories && query.HasCategory)
If it doesn't (not have categories), or it (has a category), or (x), or not (not x), then..
I also like the mixed pluralization (plural "HasNoCategories" yet singular "HasCategory").