QBGroupBy
Overview
The QBGroupBy
class is utilized to define SQL GROUP BY
conditions within a QBSelect
query. It allows adding columns, functions, or primary key columns to the GROUP BY
clause. This class also provides the capability to clear the existing GROUP BY
clause, ensuring flexibility in query construction.
Key Features
The parent
property links the QBGroupBy
instance to its parent table clause, which can be a query or a join clause. The root
property provides access to the parent query builder, enabling hierarchical query manipulation.
The add(column)
and add(function)
methods enable adding columns or functions to the GROUP BY
clause, supporting aggregation queries. The addPk()
method simplifies grouping by adding primary key columns in alphabetical order. Additionally, the clear()
method removes all conditions from the GROUP BY
clause, allowing for dynamic query adjustments.
For further details on query construction and execution, refer to the QBSelect section of the documentation.
Properties Summarized
Methods Summarized
Properties Detailed
parent
Get query builder parent table clause, this may be a query or a join clause.
Type QBSelect
Sample
var query = datasources.db.example_data.person.createSelect();
query.where.add(query.joins.person_to_parent.joins.person_to_parent.columns.name.eq('john'))
foundset.loadRecords(query)
root
Get query builder parent.
Type QBSelect
Sample
var subquery = datasources.db.example_data.order_details.createSelect();
var query = datasources.db.example_data.orders.createSelect();
query.where.add(query
.or
.add(query.columns.order_id.not.isin([1, 2, 3]))
.add(query.exists(
subquery.where.add(subquery.columns.orderid.eq(query.columns.order_id)).root
))
)
foundset.loadRecords(query)
Methods Detailed
add(column)
Add column name to group-by clause.
Same as query.groupBy().add(join.getColumn("value"))
Parameters
QBColumn column the column to add to the query condition
Returns: QBGroupBy the updated group-by clause with the specified column added.
Sample
var query = datasources.db.example_data.orders.createSelect();
query.groupBy.add(query.columns.orderid) // have to group by on pk when using having-conditions in (foundset) pk queries
.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.eq(0))
foundset.loadRecords(query)
add(function)
Add column name to group-by clause.
Same as query.groupBy().add(join.getColumn("value"))
Parameters
QBColumn function the function to add to the query
Returns: QBGroupBy the updated group-by clause with the specified function added.
Sample
var query = datasources.db.example_data.orders.createSelect();
query.groupBy.add(query.columns.orderid) // have to group by on pk when using having-conditions in (foundset) pk queries
.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.eq(0))
foundset.loadRecords(query)
addPk()
Add the tables' primary pk columns in alphabetical order to the group by clause.
Returns: QBGroupBy the updated group-by clause with the primary key(s) of the table added.
Sample
var query = datasources.db.example_data.orders.createSelect();
query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries
.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.eq(0))
foundset.loadRecords(query)
clear()
Clear the to group-by clause.
Returns: QBGroupBy the cleared group-by clause.
Sample
var q = foundset.getQuery()
q.where.add(q.columns.x.eq(100))
query.groupBy.clear.root.clearHaving()
foundset.loadRecords(q);
Last updated
Was this helpful?