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

NAME

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

VERSION

Version 0.36

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

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

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

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.

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.

LICENSE AND COPYRIGHT

Copyright 2026 Nigel Horne.

Usage is subject to the GPL2 licence terms.