File Coverage

=head1 NAME

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

=head1 VERSION

Version 0.51

=cut

=head1 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();

=head1 DESCRIPTION

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

=over 4

=item *

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

=item *

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

=item *

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

=item *

The C<join> parameter accepts the same spec as the C<join> parameter in
L<Database::Abstraction/QUERY CRITERIA>.

=item *

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

=back

=head1 METHODS

=cut

=head2 new

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

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

=cut

=head2 select

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

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

=cut

=head2 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 L<Database::Abstraction/QUERY CRITERIA>:
plain scalars, wildcard strings, C<undef> (IS NULL), comparison operator
hashrefs (C<< { '>' => n } >>), C<-in>, C<-not_in>, C<-between>,
C<-like>, C<-not_like>, C<-or>, and C<-and>.

Returns C<$self>.

=cut

=head2 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:

=over 4

=item * C<table> - the table to join (required)

=item * C<on> - the join condition, verbatim SQL (required)

=item * C<type> - join type: C<INNER> (default), C<LEFT>, C<RIGHT>, C<FULL>, C<CROSS>

=back

Multiple calls accumulate joins.  Returns C<$self>.

=cut

=head2 order_by

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

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

=cut

=head2 limit

    $q->limit(20);

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

=cut

=head2 offset

    $q->offset(40);

Skip the first N rows (for pagination with L</limit>).  Returns C<$self>.

=cut

=head2 group_by

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

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

=head3 API SPECIFICATION

=head4 Arguments

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

=head4 Returns

    $self   (the same Query object, for chaining)

=cut

=head2 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
(C<col>, C<fn>, C<as>) or a single hashref with those keys.

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

Returns C<$self>.

=head3 API SPECIFICATION

=head4 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)

=head4 Returns

    $self   (the same Query object, for chaining)

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

=cut

=head2 having

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

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

Returns C<$self>.

=head3 API SPECIFICATION

=head4 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)

=head4 Returns

    $self   (the same Query object, for chaining)

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

=cut

=head2 all

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

B<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 C<undef>).

=head3 API SPECIFICATION

=head4 Arguments

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

=head4 Returns

    ArrayRef[HashRef]

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

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

=cut

=head2 first

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

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

=head3 API SPECIFICATION

=head4 Arguments

None.

=head4 Returns

    HashRef | undef

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

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

=cut

=head2 count

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

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

=head3 API SPECIFICATION

=head4 Arguments

None.

=head4 Returns

    Int (>= 0)

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

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

=cut

=head2 each

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

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

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

Returns the number of rows passed to C<\&callback>.

=head3 API SPECIFICATION

=head4 Arguments

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

=head4 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 C<measure()> has been called, each group row is passed to the
callback in the same order as C<all()> would return them.

=cut

=head1 SEE ALSO

L<Database::Abstraction> - the parent module and its L<Database::Abstraction/QUERY CRITERIA> section.

=head1 AUTHOR

Nigel Horne, C<< <njh at nigelhorne.com> >>

=head1 SUPPORT

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

=head1 COMMON PITFALLS

=over 4

=item B<C<where()> is AND, not OR>

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

=item B<C<measure()> calls accumulate>

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

=item B<C<group_by()> replaces, not appends>

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

=item B<C<count()> is unavailable after C<measure()>>

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

=item B<Sort column for aggregate queries must use C<order_by()>>

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

=back

=head1 FORMAL SPECIFICATION

=head2 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?   = {}
    ]

=head2 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)

=head2 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

=head1 LICENSE AND COPYRIGHT

Copyright 2026 Nigel Horne.

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

=cut