What is the optimal way to count the number of results that would be returned by an ODB query?

Question

I have a C++ application that uses ODB with an SQLite database. I would like to count the number of results that would be returned by any query. Currently, I am doing something like the following:

using query = odb::query<person>;
auto filteredResults( db.query<person>(query::first == "John") );
const auto count = std::distance( filteredResults.begin(), filteredResults.end() );

Is there a more efficient way of counting the number of query results that would be returned?

I know that SQLite provides a COUNT function (https://www.sqlitetutorial.net/sqlite-count-function/) that appears to be more efficient, but is there a way to invoke this via ODB, or would I need to re-write the query in native SQLite (https://www.codesynthesis.com/products/odb/doc/manual.xhtml#3.12)?

Answer 1

For my case the answer was to use ODB views (https://www.codesynthesis.com/products/odb/doc/manual.xhtml#10).

This code sets up the view.

#pragma db view object(person)
struct PersonCount
{
    #pragma db column("COUNT(" + person::first + ")")
    std::size_t numPeople;
};

Then it can be used to count the number of people that match a given query.

transaction t( db.begin() );
PersonCount pc( db.query_value<PersonCount>( query::first == "John" ) );
t.commit();

cout << pc.numPeople << '\n';