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").


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.