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