GROUP BY, HAVING, and ORDER BY to resultsetsUsing the schema given in the previous chapter:
auto all_accounts=example2::accounts::create(conn); all_accounts->join_ledger_entries(); 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”.