Question

The syntax checker of the CSelectQuery API is making things a little difficult for me today.

I am trying to run an aggregate query to get a count of bugs for each category listed in descending order using the following code

CSelectQuery qryCasesByCategory = api.Database.NewSelectQuery("Bug");  
qryCasesByCategory.AddInnerJoin("Category", "Category.ixCategory=Bug.ixCategory");  
qryCasesByCategory.AddSelect("Category.sCategory as Category");  
qryCasesByCategory.AddSelect("COUNT(*) as Total");  
qryCasesByCategory.AddGroupBy("Category.sCategory");  
qryCasesByCategory.AddOrderBy("Total");

The API throws an exception on the last line complaining that I haven't specified a table name.

System.ArgumentException: Invalid syntax: column names must be fully qualified with the table name (found "Total" expected "[TABLE].Total")

Server stack trace: at FogCreek.FogBugz.Database.CSqlParser.ParseColumn(CSqlTokenList tokens, Nullable1 fTableNameRequired) at FogCreek.FogBugz.Database.CSqlParser.ParseTerm(CSqlTokenList tokens, Nullable1 fInsideSelect, Nullable1 fInsideInsert, Nullable1 fInsideOrderBy) at FogCreek.FogBugz.Database.CSqlParser.ParseExpression(CSqlTokenList tokens, Nullable1 fInsideSelect, Nullable1 fInsideInsert, Nullable1 fInsideOrderBy) at FogCreek.FogBugz.Database.CSqlParser.ParseOrderByTerm(CSqlTokenList tokens, Nullable1 fTableNameRequired) at FogCreek.FogBugz.Database.CSqlParser.ParseOrderByList(CSqlTokenList tokens) at FogCreek.FogBugz.Database.CSqlValidator.AssertValid(String s, String sType) at FogCreek.FogBugz.Database.CSqlValidator.AssertValidOrderByList(String s) at FogCreek.FogBugz.Database.CSelectQuery.AddOrderBy(String sSqlOrderBy) ...

Since there really is no table name for an aggregate, how can I tell the CSelectQuery to sort on an aggregate value?

Answer 1

How about using a nested query? that may do the trick, because the "total" value is then accessible as "normal" column.

Example using this approach to address the specific query in the question above:

 CSelectQuery qryBugCountsByCategoryID = api.Database.NewSelectQuery("Bug");           
 qryBugCountsByCategoryID.AddSelect("Bug.ixCategory");
 qryBugCountsByCategoryID.AddSelect("COUNT(*) as TotalCases");
 qryBugCountsByCategoryID.AddGroupBy("Bug.ixCategory");

 CSelectQuery qryCategories = api.Database.NewSelectQuery("Category");
 qryCategories.AddSelect("Category.sCategory as Category");
 qryCategories.AddSelect("BugCounts.TotalCases");
 qryCategories.AddInnerJoinSelect(qryBugCountsByCategoryID, "BugCounts", "Category.ixCategory=BugCounts.ixCategory");
 qryCategories.AddOrderBy("BugCounts.TotalCases Desc");