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

Type	Name	Summary
QBSelect	parent	Get query builder parent table clause, this may be a query or a join clause.
QBSelect	root	Get query builder parent.

Methods Summarized

Type	Name	Summary
QBGroupBy	add(column)	Add column name to group-by clause.
QBGroupBy	addPk()	Add the tables' primary pk columns in alphabetical order to the group by clause.
QBGroupBy	clear()	Clear the to group-by clause.

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)

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);

---