QCubed Query Clauses

All QCubed Query method calls take in an optional set of Clauses. Clauses let you alter the result set by performing the equivalents of most of your major SQL clauses, including JOIN, ORDER BY, GROUP BY and DISTINCT.

The following is the list of Clause classes and what parameters they take:

• QQ::orderBy(array/list of Nodes or Conditions)
• QQ::groupBy(array/list of Nodes)
• QQ::having(QQSubSqlNode)
• QQ::count(NodeBase, string)
• QQ::minimum(NodeBase, string)
• QQ::maximum(NodeBase, string)
• QQ::average(NodeBase, string)
• QQ::sum(NodeBase, string)
• QQ::expand(NodeBase)
• QQ::expandAsArray(NodeBase for an Association Table)
• QQ::limitInfo(integer[, integer = 0])
• QQ::distinct()

orderBy() and groupBy() follow the conventions of SQL ORDER BY and GROUP BY. It takes in a list of one or more Column Nodes. This list could be a parameterized list and/or an array.

Specifically for orderBy(), to specify a Node that you wish to order by in descending order, add a "false" after the Node. So for example, QQ::orderBy(QQN::person()->LastName, false, QQN::person()->FirstName) will do the SQL equivalent of "ORDER BY last_name DESC, first_name ASC".

Count, Minimum, Maximum , Average and Sum are aggregation-related clauses, and only work when groupBy() is specified. These methods take in an attribute name, which can then be retrieved using getVirtualAttribute() on the object.

having() adds a SQL Having clause, which allows you to filter the results of your query based on the results of the aggregation-related functions. having() requires a Subquery, which is a SQL code snippet you create to specify the criteria to filter on. (See the Subquery section later in this tutorial for more information on those).

expand() and exapandAsArray() deal with Object Expansion / Early Binding. More on this can be seen in the Early Binding of Related Objects example.

limitInfo() will limit the result set. The first integer is the maximum number of rows you wish to limit the query to. The optional second integer is the offset (if any).

Distinct will cause the query to be called with SELECT DISTINCT.

Select all People, Ordered by Last Name then First Name

Note now QQ::OrderBy gets two parameters here

• Linda Brady
• Brett Carlisle
• John Doe
• Mike Ho
• Samantha Jones
• Jacob Pratt
• Kendall Public
• Ben Robinson
• Alex Smith
• Jennifer Smith
• Wendy Smith
• Karen Wolfe

Select all People, Ordered by Last Name then First Name, Limited to the first 4 results

Combining QQ::orderBy and QQ::limitInfo

• Linda Brady
• Brett Carlisle
• John Doe
• Mike Ho

Select all People, those with last name Smith first, then ordered by First Name

Using a QQ::condition as an ORDER BY clause

• Alex Smith
• Jennifer Smith
• Wendy Smith
• Ben Robinson
• Brett Carlisle
• Jacob Pratt
• John Doe
• Karen Wolfe
• Kendall Public
• Linda Brady
• Mike Ho
• Samantha Jones

Select all Projects and the Count of Team Members (if applicable)

GROUP BY in action

• ACME Website Redesign (5 team members)
• State College HR System (6 team members)
• Blueman Industrial Site Architecture (5 team members)
• ACME Payment System (7 team members)

Select all Projects with more than 5 team members.

Using a Having clause to further limit group functions

State College HR System (6 team members)
ACME Payment System (7 team members)