/ 
Database-Abstraction-0.51
⭐ Starred 0 / Database::Abstraction::Query

NAME

Database::Abstraction::Query - Fluent, chainable query builder for Database::Abstraction

VERSION

Version 0.51

SYNOPSIS

my $db = Database::Foo->new(directory => '/path/to/data');

# --- Basic usage -----------------------------------------------

# All rows
my $all = $db->query->all();

# Filter, sort, page
my $rows = $db->query
    ->where(status => 'active')
    ->where(score  => { '>=' => 80 })
    ->order_by('score DESC')
    ->limit(10)
    ->offset(20)
    ->all();

# Single row
my $row = $db->query->where(name => 'Alice')->first();

# Count
my $n = $db->query->where(status => 'active')->count();

# --- Specific columns ------------------------------------------

my $names = $db->query->select('name, score')->where(status => 'active')->all();

# --- Joins -----------------------------------------------------

my $joined = $db->query
    ->join({ table => 'dept', on => 'e.dept_id = dept.id', type => 'LEFT' })
    ->where(dept_name => 'Engineering')
    ->all();

# --- OR criteria -----------------------------------------------

my $either = $db->query
    ->where(-or => [
        { status => 'active'           },
        { score  => { '>=' => 95 }     },
    ])
    ->all();

DESCRIPTION

Database::Abstraction::Query is a fluent query builder returned by $db->query(). You assemble a query by chaining builder methods, then execute it with a terminal method.

  • Builder methods (select, where, join, order_by, limit, offset, group_by, measure, having) all return $self, so calls can be chained in any order.

  • Terminal methods (all, first, count) assemble the SQL, execute it, and return the result. Each terminal method can be called on the same builder object independently - calling first() does not modify the stored state (it temporarily sets LIMIT 1 internally).

  • where() calls are merged with AND semantics: each call adds more required conditions. To express OR conditions pass -or => [...] as a key inside a single where() call.

  • The join parameter accepts the same spec as the join parameter in "QUERY CRITERIA" in Database::Abstraction.

  • BerkeleyDB backends support all(), first(), and count() with where() criteria and order_by()/limit()/offset() applied in Perl. The join() builder method and the select() column projection are not supported on BerkeleyDB and will raise an error.

METHODS

new

my $q = Database::Abstraction::Query->new(_db => $db_object);

Construct a new, empty query builder bound to $db_object. In practice you almost always call this via $db->query() instead.

select

$q->select('name, score');
$q->select('COUNT(*) AS n, status');

Set the column expression for the SELECT clause. Default is * (all columns). Returns $self.

where

$q->where(status => 'active');
$q->where(score  => { '>'  => 8     });
$q->where(name   => { -in  => [...] });
$q->where(-or    => [ {...}, {...}     ]);

Add one or more criteria to the query. Multiple calls are merged with AND semantics. Accepts a flat list of key/value pairs or a single hashref.

Supports the full criteria syntax of "QUERY CRITERIA" in Database::Abstraction: plain scalars, wildcard strings, undef (IS NULL), comparison operator hashrefs ({ '>' => n }), -in, -not_in, -between, -like, -not_like, -or, and -and.

Returns $self.

join

$q->join({ table => 'dept', on => 'e.dept_id = dept.id', type => 'LEFT' });

# Multiple joins at once
$q->join([
    { table => 'dept',    on => 'e.dept_id    = dept.id'    },
    { table => 'country', on => 'e.country_id = country.id' },
]);

Append one or more JOIN specs. Each spec is a hashref with:

  • table - the table to join (required)

  • on - the join condition, verbatim SQL (required)

  • type - join type: INNER (default), LEFT, RIGHT, FULL, CROSS

Multiple calls accumulate joins. Returns $self.

order_by

$q->order_by('name DESC');
$q->order_by('score DESC, name ASC');

Set the ORDER BY expression. Replaces any previously set ordering. Returns $self.

limit

$q->limit(20);

Set the maximum number of rows to return. Returns $self.

offset

$q->offset(40);

Skip the first N rows (for pagination with "limit"). Returns $self.

group_by

$q->group_by('region', 'month');
$q->group_by(\@cols);
$q->group_by([]);    # clear (grand total)

Set the GROUP BY columns for an aggregate query. Replaces any previously set list. Has no effect unless at least one measure() call follows. Returns $self.

API SPECIFICATION

Arguments

@cols   # one or more column name strings (replaces any previous group_by)

Returns

$self   (the same Query object, for chaining)

measure

$q->measure('amount', 'sum', 'revenue');
$q->measure(undef, 'count', 'orders');       # COUNT(*)
$q->measure({ col => 'c', fn => 'avg', as => 'mean' });

Append a measure to the aggregate query. May be called multiple times; measures accumulate. Accepts either three positional arguments (col, fn, as) or a single hashref with those keys.

fn must be one of sum, avg, min, max, count, count_distinct. col is optional only for count (COUNT(*)).

Returns $self.

API SPECIFICATION

Arguments

$col  # Str | undef -- column to aggregate; omit/undef for COUNT(*)
$fn   # Str         -- one of: sum avg min max count count_distinct
$as   # Str         -- result key alias (required; valid SQL identifier)

Returns

$self   (the same Query object, for chaining)

Calls to measure() accumulate: each call appends one measure to the list. To start over, create a new query builder with $db->query().

having

$q->having(revenue => { '>' => 1000 });
$q->having(\%criteria);

Add post-aggregation filter criteria. Multiple calls are merged with AND semantics, exactly like where(). Keys must be by column names or measure aliases (validated by aggregate()).

Returns $self.

API SPECIFICATION

Arguments

%criteria   # key-value pairs where each key is a by-column name or
            # a measure alias, and each value is a scalar or an
            # operator hashref (same syntax as selectall_arrayref)

Returns

$self   (the same Query object, for chaining)

Multiple calls to having() are merged with AND semantics, exactly like multiple calls to where().

all

my $rows = $q->all();

Terminal method. Executes the assembled query and returns an array reference of hash references, one per matching row. Returns an empty array reference when there are no matches (never undef).

API SPECIFICATION

Arguments

None. All query parameters are set via the builder methods before calling all().

Returns

ArrayRef[HashRef]

An arrayref of row hashrefs. Returns [] (empty arrayref) when no rows match -- never undef. Each hashref is read-only unless the database was constructed with no_fixate => 1.

When measure() has been called, delegates to $db->aggregate() and returns grouped rows (see "aggregate" in Database::Abstraction).

first

my $row = $q->first();    # \%hashref or undef

Terminal method. Executes the query with LIMIT 1 and returns the first matching row as a hash reference, or undef when there is no match. Any limit() or offset() you have set is temporarily overridden for efficiency (only the LIMIT is overridden; offset is still applied).

API SPECIFICATION

Arguments

None.

Returns

HashRef | undef

The first matching row hashref, or undef when no row matches. Internally applies LIMIT 1 for SQL backends; for in-memory backends it returns the first element of the sorted result.

When measure() has been called, returns the first aggregate group row (the same as ->all()->[0]).

count

my $n = $q->count();

Terminal method. Executes SELECT COUNT(*) with the current WHERE and JOIN clauses and returns the integer count. ORDER BY, LIMIT, and OFFSET are ignored for the count query.

API SPECIFICATION

Arguments

None.

Returns

Int (>= 0)

The number of matching rows. Returns 0 when nothing matches -- never undef.

Restriction: croaks with "count() is not available once measure() has been called" if any measure() call has been made on this query builder. Use all() and examine scalar @{$rows} instead.

each

$q->each(\&callback);
$q->each(sub { my $row = shift; print $row->{'name'}, "\n" });

Terminal method. Iterates over matching rows one at a time, calling \&callback once per row with the row hashref as the sole argument. Uses constant memory on the SQL path (see "each_row" in Database::Abstraction).

Applies any where(), join(), order_by(), limit(), and offset() set on the builder.

Returns the number of rows passed to \&callback.

API SPECIFICATION

Arguments

CodeRef   # required: callback invoked with one row hashref per call

Returns

Int (>= 0)

The number of rows visited. Any exception thrown inside the callback is re-thrown after the DBI statement handle is properly closed.

When measure() has been called, each group row is passed to the callback in the same order as all() would return them.

SEE ALSO

Database::Abstraction - the parent module and its "QUERY CRITERIA" in Database::Abstraction section.

AUTHOR

Nigel Horne, <njh at nigelhorne.com>

SUPPORT

Please report bugs at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Database-Abstraction.

COMMON PITFALLS

where() is AND, not OR

Each call to where() ANDs with all previous where() calls. Use -or => [...] inside a single where() call for OR logic.

measure() calls accumulate

Every measure() call appends a new measure to the query. To build a fresh aggregate query call $db->query() again rather than re-using the same builder object.

group_by() replaces, not appends

Calling group_by() a second time replaces the previous group-by columns entirely. It does not accumulate like measure() or where().

count() is unavailable after measure()

Once you have called measure() on a query builder, calling count() will croak. Use all() and check scalar @{$rows}.

Sort column for aggregate queries must use order_by()

To sort aggregate results, call ->order_by('col') on the builder before ->all(). Passing sort_by directly to all() has no effect because the terminal method ignores extra arguments.

FORMAL SPECIFICATION

Query builder state

QueryState == [
    _db       : Database::Abstraction,
    _select   : Str,
    _where    : CRITERIA,
    _joins    : seq JOIN_SPEC,
    _order_by : Str | undef,
    _limit    : Int | undef,
    _offset   : Int | undef,
    _group_by : seq COLNAME,
    _measures : seq MEASURE_SPEC,
    _having   : CRITERIA
]

Initial state Q0 == [
    _select?   = '*',
    _where?    = {},
    _joins?    = [],
    _order_by? = undef,
    _limit?    = undef,
    _offset?   = undef,
    _group_by? = [],
    _measures? = [],
    _having?   = {}
]

Builder method invariants

where(C) ____________________________________________
DeltaQueryState
C? : CRITERIA
────────────────────────────────────────────────────
post: _where' = _where UNION C   (AND semantics)

measure(col, fn, as) ________________________________
DeltaQueryState
────────────────────────────────────────────────────
post: _measures' = _measures CONCAT [{col,fn,as}]

group_by(cols) ______________________________________
DeltaQueryState
────────────────────────────────────────────────────
post: _group_by' = cols  (replaces previous value)

all() dispatch

all _________________________________________________
XiQueryState  (read-only)
────────────────────────────────────────────────────
result == (_measures != empty
           IMPLIES _db.aggregate(_group_by, _measures, _where,
                                 _having, _order_by, _limit, _offset)
           ELSE    _db.selectall_arrayref(_where, _joins,
                                          _order_by, _limit, _offset))
post: result : seq ROW

LICENSE AND COPYRIGHT

Copyright 2026 Nigel Horne.

Usage is subject to the GPL2 licence terms. If you use it, please let me know.