c++sqliteodb

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


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


Solution

  • 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';