Group By queries

The system supports group queries. Group queries are particularly useful in combination with aggregate functions:

var q = WAFContext.Session.CreateQuery();
var parent = new AqlAliasArticleBase();
var child = new AqlAliasArticleBase();
var join = new AqlAliasRelHierarchical(parent, child);
q.From(join);
q.GroupBy(parent.NodeId);
q.Where(Aql.DateTimePart(child.ReleaseDate, DateTimePart.DayOfWeek) == 0);
q.Having(Aql.Count(child.NodeId) > 1);
var rParent = q.Select(parent.Name);
var rChild = q.Select(Aql.Count(child.NodeId));
var rs = q.Execute(); 

StringBuilder html = new StringBuilder();
html.Append("Total count:" + rs.RowCount + "<br />");

while (rs.Read()) 
{
   html.Append(rParent.Value);
   html.Append(" - ");
   html.Append(rChild.Value);
   html.Append("<br />");
}

Response.Write(html.ToString());

This example will return a list of articles with more than one child and a released on a Monday.

The list will contain the name of the parent and the number matching child articles.

Notice the use of the “.Having” statement. The differences on the “Having” statement and the “Where” statement is like SQL. “Having” operate on the values belonging to the group. Where operate on the values before the groups are aggregated.