Chapter 10. Adding GROUP BY, HAVING, and ORDER BY to resultsets

Using the schema given in the previous chapter:

auto all_accounts=example2::accounts::create(conn);

all_accounts->group_by(all_accounts->get_table_alias() + ".account_id");
all_accounts->having("COUNT(*)", ">", 2);
all_accounts->order_by(all_accounts->get_table_alias() + ".account_id DESC");

for (const auto &row: *all_accounts)

// ...

group_by() and order_by() take a variadic list of string parameters (alternatively, they can be called more than once, and the results are combined together). The list forms a GROUP BY or a ORDER BY clause, in the resulting SQL. The strings in each respective list get concatened, with commas separating them.

having() works list search() and takes the same parameters. The only difference between the two methods is each one's respective SQL forms either a WHERE or a HAVING clause.

The above example executes an SQL query that's equivalent to SELECT accounts.account_id, accounts.code FROM accounts AS accounts LEFT JOIN ledger_entries AS ledger_entries ON accounts.account_id=ledger_entries.account_id GROUP BY accounts.account_id HAVING COUNT(*) > 2 ORDER BY accounts.account_id DESC.