Service Framework

Appearance

Query Builder ​

The query builder provides a fluent interface for building and executing SQL queries against MySQL. Each method returns a reference to the query object, so you can chain calls.

Creating a Query ​

cpp
using namespace framework;
using namespace framework::database;

auto q = query<User>("users");

The template parameter User should be a model class. The string "users" is the table name.

WHERE Clauses ​

where(col, val) — Basic equality ​

Adds WHERE col = val. The simplest and most common WHERE clause.

cpp
q->where("name", "Alice");
// WHERE name = 'Alice'

Parameters:

  • col — Column name as a string.
  • val — Value to match. Accepts any Boost.MySQL field type: strings, integers, booleans, etc.

where(col, op, val) — Custom operator ​

Adds WHERE col op val with a custom comparison operator.

cpp
q->where("age", ">", 18);
// WHERE age > 18
q->where("name", "LIKE", "%Alice%");
// WHERE name LIKE '%Alice%'

Parameters:

  • col — Column name.
  • op — Comparison operator ("=", ">", "<", ">=", "<=", "<>", "LIKE", etc.).
  • val — Value to compare against.

or_where(col, val) — OR equality ​

Adds OR col = val. Use when you need an alternative condition.

cpp
q->where("role", "admin");
q->or_where("role", "moderator");
// WHERE role = 'admin' OR role = 'moderator'

Both or_where(col, val) and or_where(col, op, val) are supported.

where_in(col, vals) — IN list ​

Adds WHERE col IN (val1, val2, ...). Use when a column can match any value in a set.

cpp
q->where_in("role", {"admin", "moderator"});
// WHERE role IN ('admin', 'moderator')

Parameters:

  • col — Column name.
  • vals — A std::vector<boost::mysql::field> of allowed values.

where_not_in(col, vals) — NOT IN ​

Adds WHERE col NOT IN (...). The inverse of where_in.

cpp
q->where_not_in("status", {"banned", "deleted"});
// WHERE status NOT IN ('banned', 'deleted')

where_between(col, a, b) — Range ​

Adds WHERE col BETWEEN a AND b. Use for numeric or date ranges.

cpp
q->where_between("age", 18, 65);
// WHERE age BETWEEN 18 AND 65

where_not_between(col, a, b) — Outside range ​

Adds WHERE col NOT BETWEEN a AND b. The inverse of where_between.

cpp
q->where_not_between("age", 18, 65);
// WHERE age NOT BETWEEN 18 AND 65

where_null(col) — IS NULL ​

Adds WHERE col IS NULL. Use to find records with missing values.

cpp
q->where_null("deleted_at");
// WHERE deleted_at IS NULL

where_not_null(col) — IS NOT NULL ​

Adds WHERE col IS NOT NULL. The inverse of where_null.

cpp
q->where_not_null("email");
// WHERE email IS NOT NULL

where_column(col1, op, col2) — Column comparison ​

Compares one column against another column. Useful for joins or self-referential queries.

cpp
q->where_column("created_at", "<", "updated_at");
// WHERE created_at < updated_at

where_raw(sql, bindings) — Raw SQL ​

Injects a raw SQL fragment with parameter binding. Use for database-specific features like JSON functions or complex expressions.

cpp
q->where_raw("JSON_EXTRACT(data, '$.key') = ?", {"value"});
// WHERE JSON_EXTRACT(data, '$.key') = 'value'

Parameters:

  • sql — Raw SQL string. Use ? as placeholders.
  • bindings — A std::vector<boost::mysql::field> of values for the placeholders.

Ordering ​

order_by(col, dir) ​

Adds ORDER BY col dir. Controls the sort order of results.

cpp
q->order_by("created_at", "DESC");
// ORDER BY created_at DESC
q->order_by("name", "ASC");
// ORDER BY name ASC

Parameters:

  • col — Column name.
  • dir — Direction: "ASC" (default) or "DESC".

order_by_raw(expr) ​

Adds a raw SQL fragment to the ORDER BY clause. Use for expressions like RAND() or FIELD().

cpp
q->order_by_raw("RAND()");
// ORDER BY RAND()

Limit & Offset ​

limit(n) ​

Limits the number of rows returned.

cpp
q->limit(10);
// LIMIT 10

offset(n) ​

Skips the first N rows. Typically used with limit() for pagination.

cpp
q->offset(20);
// OFFSET 20

SELECT ​

select(cols) ​

Specifies which columns to return. If not called, all columns (*) are selected.

cpp
q->select({"id", "name", "email"});
// SELECT id, name, email

select_raw(expr) ​

Adds a raw SQL expression to the SELECT clause. Use for computed columns, subqueries, or MySQL functions.

cpp
q->select_raw("COUNT(*) as total");

add_select(col) ​

Appends an additional column to an existing SELECT list.

cpp
q->select({"id", "name"});
q->add_select("email");
// SELECT id, name, email

Joins ​

join(table, one, op, two) ​

Adds an INNER JOIN clause.

cpp
q->join("posts", "users.id", "=", "posts.user_id");
// INNER JOIN posts ON users.id = posts.user_id

left_join(table, one, op, two) ​

Adds a LEFT JOIN. All rows from the left table are kept, even when there is no match on the right.

cpp
q->left_join("profiles", "users.id", "=", "profiles.user_id");
// LEFT JOIN profiles ON users.id = profiles.user_id

right_join(table, one, op, two) ​

Adds a RIGHT JOIN. All rows from the right table are kept.

cpp
q->right_join("logs", "users.id", "=", "logs.user_id");

Subquery WHERE ​

where_in(col, subquery) ​

Pass a subquery object for advanced filtering.

cpp
template<typename U>
q->where_in("user_id", sub_query);
// WHERE user_id IN (SELECT ...)

The subquery must be a std::shared_ptr<query<U>>.

Aggregate Methods ​

These execute the query and return a single scalar value via callback.

count(pool, cb) ​

Returns the number of matching rows.

cpp
q->count(pool, [](auto ec, int64_t total) {
    fmt::println("Total users: {}", total);
});

sum(pool, col, cb) ​

Returns the sum of a numeric column.

cpp
q->sum(pool, "price", [](auto ec, int64_t sum) { });

avg(pool, col, cb) ​

Returns the average of a numeric column. The callback receives a double.

cpp
q->avg(pool, "rating", [](auto ec, double avg) { });

min(pool, col, cb) — Minimum value ​

Returns the minimum value of a column.

cpp
q->min(pool, "price", [](auto ec, int64_t min) { });

max(pool, col, cb) — Maximum value ​

Returns the maximum value of a column.

cpp
q->max(pool, "price", [](auto ec, int64_t max) { });

exists(pool, cb) ​

Returns true if at least one matching row exists.

cpp
q->exists(pool, [](auto ec, bool exists) {
    if (exists) fmt::println("Found!");
});

Executing Queries ​

These methods execute the query and return model instances.

get(pool, cb) ​

Returns all matching rows as a vector of model instances.

cpp
q->get(pool, [](auto ec, auto results) {
    for (auto& user : results) {
        fmt::println("User: {}", user->name);
    }
});

first(pool, cb) ​

Returns the first matching row, or nullptr if none.

cpp
q->first(pool, [](auto ec, auto row) {
    if (row) fmt::println("Found: {}", row->name);
});

first_or_fail(pool, cb) ​

Returns the first matching row, or calls the callback with an error if none exists.

cpp
q->first_or_fail(pool, [](auto ec, auto row) {
    if (ec) { /* handle not found */ }
});

sole(pool, cb) ​

Returns exactly one row. Fails with an error if zero or more than one row match.

cpp
q->sole(pool, [](auto ec, auto row) {
    // Exactly one result expected
});

pluck(pool, col, cb) — Single column values ​

Returns a vector of values from a single column.

cpp
q->pluck(pool, "email", [](auto ec, auto values) {
    for (auto& val : values) {
        fmt::println("{}", val.as_string());
    }
});

value(pool, col, cb) — Single scalar value ​

Returns the first value of a single column.

cpp
q->value(pool, "name", [](auto ec, auto val) {
    fmt::println("First name: {}", val.as_string());
});

Find-or-Create ​

first_or_create(pool, cb, factory) ​

Returns the first matching row, or creates one using the factory function.

cpp
q->first_or_create(pool, [](auto ec, auto user, bool created) {
    if (created) fmt::println("Created new user");
}, []() { return std::make_shared<User>(); });

update_or_create(pool, cb, factory) ​

Returns the first matching row, or creates one. If the row exists, it is updated with the query's WHERE values.

cpp
q->update_or_create(pool, [](auto ec, auto user, bool created) {
    // user exists or was created
}, []() { return std::make_shared<User>(); });

first_or(pool, cb, factory) ​

Returns the first matching row, or calls the factory to provide a default.

cpp
q->first_or(pool, [](auto ec, auto user) {
    if (user) process(user);
}, []() { return std::make_shared<User>(); });

Direct Mutation ​

update(pool, cols, vals, cb) ​

Updates matching rows directly without loading models.

cpp
q->update(pool, {"name", "status"}, {"Inactive", "archived"},
    [](auto ec) { });

upsert(pool, insert_cols, insert_vals, update_cols, cb) ​

Inserts a row, or updates it if a duplicate key conflict occurs.

cpp
q->upsert(pool, {"id", "name"}, {42, "Alice"}, {"name"},
    [](auto ec, auto affected) { });

destroy(pool, cb) ​

Deletes all matching rows.

cpp
q->destroy(pool, [](auto ec) { });

Utility Methods ​

to_sql() — Debug SQL output ​

Returns the generated SQL string without executing it. Useful for debugging query building.

cpp
auto sql = q->to_sql();
fmt::println("Generated SQL: {}", sql);

Soft Deletes ​

with_trashed() — Include soft-deleted records ​

By default, soft-deleted records are excluded from queries. Call with_trashed() to include them:

cpp
q->with_trashed();
// Includes records where deleted_at IS NOT NULL

only_trashed() — Only soft-deleted records ​

Returns only records that have been soft-deleted:

cpp
q->only_trashed();
// WHERE deleted_at IS NOT NULL

Pagination ​

paginate(pool, cb, per_page, page_num) ​

Executes the query with pagination. Automatically runs a count query to determine the total and last page.

cpp
q->paginate(pool, [](auto ec, page<User> p) {
    fmt::println("Page {} of {} ({} items)", p.current_page, p.last_page, p.total);
    for (auto& user : p.items) {
        fmt::println("User: {}", user->name);
    }
}, 15, 1);

Parameters:

  • pool — Database connection pool.
  • cb — Callback receiving the page<T> struct.
  • per_page — Items per page (default: 15).
  • page_num — Page number (default: 1, 1-indexed).

The page<T> struct provides:

  • items — vector<shared_ptr<T>> for the current page.
  • total — Total number of matching rows.
  • last_page — Total number of pages.
  • has_prev() / has_next() — Navigation helpers.

Chunking ​

chunk(pool, size, cb) ​

Processes results in batches to avoid loading the entire result set into memory at once. Important: You must call next() to advance to the next chunk. If you forget, iteration stops silently without error.

cpp
q->chunk(pool, 100, [](auto ec, auto results, auto next) {
    for (auto& row : results) {
        process(row);
    }
    next();  // load the next chunk
});

Parameters:

  • pool — Database pool.
  • size — Number of rows per chunk.
  • cb — Callback with (ec, results, next). Call next() to fetch the next chunk.

Cursor-Based Iteration ​

each(pool, cb, size) — Row by row ​

Processes each row individually without loading the entire result set. The callback receives one row at a time and calls a next function to advance.

cpp
q->each(pool, [](auto ec, auto row, auto next) {
    if (!ec && row) {
        fmt::println("User: {}", row->name);
        next();  // fetch next row
    }
}, 100);  // fetch 100 rows at a time internally

Parameters:

  • pool — Database pool.
  • cb — Callback with (ec, row, next). Call next() to advance to the next row.
  • size — Internal batch size (default: 100). Controls how many rows are fetched per query.

cursor(pool, cb, size) — Forward-only cursor ​

Identical to each() — iterates over results one at a time with internal batching.

cpp
q->cursor(pool, [](auto ec, auto row, auto next) {
    process(row);
    next();
}, 100);

Use each() or cursor() when processing large datasets where loading all rows into memory at once is not feasible.

Eager Loading ​

Eager loading fetches related records in separate queries and attaches them to the results. This avoids the N+1 query problem.

cpp
// Load posts for each user
q->with_has_many<Post>("user_id", [](const Post& p) { return p.user_id; }, "posts");

// Load a count of related records
q->with_count<Post>("user_id", "posts_count");

The fk_extractor callback tells the query builder how to match parent records to related records. It receives each related model instance and returns the foreign key value that links it to the parent. The query builder uses this to group related records by parent ID.

cpp
// Example: fk_extractor returns the user_id from each Post
// The query builder groups posts by user_id, attaching them to the matching User
q->with_has_many<Post>("user_id", [](const Post& p) -> int64_t {
    return p.user_id;
}, "posts");

Available methods:

  • with_has_many<Related>(fk, fk_extractor, relation_name, related_table)
  • with_has_one<Related>(fk, fk_extractor, relation_name, related_table)
  • with_belongs_to<Parent>(fk, fk_extractor, relation_name, parent_key, parent_table)
  • with_belongs_to_many<Related>(pivot, local_fk, related_fk, relation_name, related_table)
  • with_morph_many<Related>(morph_name, morph_type, relation_name, type_column, id_column, related_table)
  • with_count<Related>(fk, count_as, local_key, related_table)

Relationship Existence ​

Filters results to only include records that have related records.

cpp
template<typename Related>
q->where_has<Post>("user_id", "id");
// WHERE EXISTS (SELECT 1 FROM posts WHERE posts.user_id = users.id)

Filters results to only include records that do NOT have related records. The inverse of where_has.

cpp
template<typename Related>
q->where_doesnt_have<Post>("user_id", "id");
// WHERE NOT EXISTS (SELECT 1 FROM posts WHERE posts.user_id = users.id)

Group By & Having ​

Group results for aggregation.

cpp
q->group_by("role");
// GROUP BY role

q->having("count", ">", 5);
// HAVING count > 5

Conditional Clauses ​

when(condition, callback) ​

Conditionally applies query clauses. If condition is true, the callback is invoked with the query builder.

cpp
q->when(shouldFilter, [](auto& query) {
    query->where("active", true);
});

Shortcuts ​

Convenience methods for common patterns.

cpp
q->latest();      // order_by("created_at", "DESC")
q->oldest();      // order_by("created_at", "ASC")
q->where_key(1);  // where("id", 1)

Increment / Decrement ​

Atomically update a numeric column.

cpp
q->increment(pool, "views", [](auto ec) { }, 1);
q->decrement(pool, "stock", [](auto ec) { }, 1);

Global Scopes ​

Global scopes are predefined constraints applied automatically to every query for a model. Use them for global filters like soft deletes or multi-tenant isolation.

Defining a Global Scope ​

On your model, add a static method or lambda that modifies queries:

cpp
struct User : model<User> {
    static inline auto global_scope = [](framework::database::query<User>& q) {
        q.where("deleted_at", nullptr);
    };
};

// In your model registration, add it:
User::add_global_scope(User::global_scope);

Alternatively, use add_global_scope() directly:

cpp
User::add_global_scope([](auto& q) {
    q.where("tenant_id", current_tenant);
});

Bypassing Global Scopes ​

cpp
q->without_global_scopes();  // Skip global scopes for this query
q->apply_global_scopes();    // Apply them explicitly (default)

To clear all global scopes for a model:

cpp
User::clear_global_scopes();

Copyright 2026 Ian Torres