/ 
DBIx-QuickORM-0.000020
⭐ Starred 0 / DBIx::QuickORM::Manual::Querying

NAME

DBIx::QuickORM::Manual::Querying - A practical guide to fetching, creating, updating, and deleting rows with handles.

DESCRIPTION

A handle is the object you use to build and run queries against a source (a table, view, or join). It is the rough equivalent of a ResultSet in other ORMs. This guide is a task-oriented tour of the handle. For the exhaustive method-by-method reference see DBIx::QuickORM::Handle.

The mental model is small:

  • Get a handle on a source.

  • Refine it (WHERE, ORDER BY, LIMIT, field selection) to describe the query you want. Each refinement returns a new handle; the original is untouched.

  • Run a terminal method (one, all, insert, update, ...) to actually talk to the database.

Handles do not execute anything until you call a terminal method, so you can compose and pass them around freely.

GETTING A HANDLE

Every handle operates through a DBIx::QuickORM::Connection. The connection itself is the easiest place to make one with $con->handle(...):

my $con = orm('my_orm');

# A handle on the whole 'people' table (no query has run yet).
my $people = $con->handle('people');

# A handle already narrowed by a where clause.
my $smiths = $con->handle('people' => {surname => 'smith'});

The constructor is forgiving about argument shape. A bare string is treated as a source (table) name, a hashref as a WHERE clause, an arrayref as an ORDER BY, and a plain integer as a LIMIT. So all of these are equivalent ways to spell the same query:

$con->handle('people', {surname => 'smith'}, ['first_name'], 10);
$con->handle('people')->where({surname => 'smith'})->order_by('first_name')->limit(10);

See "CONSTRUCTOR ARGS" in DBIx::QuickORM::Handle for the full list of accepted arguments.

CONNECTION SHORTCUTS

For one-off queries you usually do not need to name the intermediate handle. The connection proxies the common terminal methods straight through to a handle, so these shortcuts build a handle and immediately run it:

my @people = $con->all('people');
my $person = $con->one('people' => {id => 1});
my $count  = $con->count('people' => {surname => 'smith'});
my $person = $con->by_id('people' => 1);

$con->insert('people' => {name => 'Bob'});
$con->update('people' => {surname => 'smith'} => {active => 0});
$con->delete('people' => {id => 1});

Each is just $con->handle(@args)->METHOD(...). Reach for an explicit handle when you want to refine in stages or reuse the same base query.

THE IMMUTABLE BUILDER MODEL

Handles are immutable. The builder methods never modify the handle they are called on; they return a brand new handle with the change applied. This makes it safe to derive several queries from a shared base:

my $base   = $con->handle('people');
my $smiths = $base->where({surname => 'smith'});
my $jones  = $base->where({surname => 'jones'});
# $base is still unrestricted.

Because the return value is the whole point, calling a builder method in void context is a mistake and will croak. Always capture (or chain off) the result:

$base->where({surname => 'smith'});            # WRONG: result thrown away, croaks
my $h = $base->where({surname => 'smith'});    # right

The reference doc calls these methods Immutators (always return a new handle) and Immucessors (return the current value when called with no argument, or a new handle when called with one). See "Immutators" in DBIx::QuickORM::Handle and "Immucessors" in DBIx::QuickORM::Handle.

REFINING A HANDLE

WHERE

where sets the WHERE clause; the clause itself is a SQL::Abstract-style structure.

my $h = $con->handle('people')->where({surname => 'smith'});

To extend an existing clause without rebuilding it, use and / or. They combine the new condition with whatever the handle already had:

# WHERE (surname = 'smith') AND (active = 1)
my $h2 = $h->and({active => 1});

# WHERE (surname = 'smith') OR (surname = 'jones')
my $h3 = $h->or({surname => 'jones'});

ORDER BY and LIMIT

my $h = $con->handle('people')
    ->where({active => 1})
    ->order_by(['surname', 'first_name'])
    ->limit(25);

order_by accepts a single field, a list of fields, or an arrayref.

Choosing fields

By default a handle fetches the source's normal field set. You can narrow or change that:

# Only these fields.
my $h = $con->handle('people')->fields(['id', 'first_name']);

# Everything except the given fields. Primary key fields cannot be omitted.
my $h = $con->handle('people')->omit(['bio', 'avatar']);

A source may mark some columns to be left out of the default fetch (for example large blobs). all_fields forces a handle to select every column regardless:

my $h = $con->handle('people')->all_fields;

FETCHING ROWS

These terminal methods run a SELECT and hand back row objects (see DBIx::QuickORM::Row). They accept an optional trailing WHERE (hashref) or row, so you can pass the condition inline instead of calling where first.

$row = $h->one

Fetch exactly one row. Returns undef if nothing matches, and croaks if more than one row matches. Use this when the query should be unique.

my $person = $con->handle('people')->one({id => 1});
$row = $h->first

Like one, but tolerates extra matches and simply returns the first row.

@rows = $h->all

Return every matching row as a list.

my @active = $con->handle('people')->where({active => 1})->all;
$iter = $h->iterator

Return a DBIx::QuickORM::Iterator instead of slurping everything into memory:

my $iter = $con->handle('people')->iterator;
while (my $person = $iter->next) {
    ...;
}
$h->iterate(sub { my $row = shift; ... })

Run a callback once per matching row.

$count = $h->count

Return the number of rows the query would match (a COUNT(*)), without building row objects.

$row = $h->by_id($id)

Fetch a single row by primary key. $id may be a scalar (single-column key), an arrayref of values, or a hashref of field => value pairs for a compound key. If the row is already in the connection's cache no query is run at all.

my $person = $con->handle('people')->by_id(1);

$h->by_ids(@ids) is the batch form, returning an arrayref of rows.

Iterators

DBIx::QuickORM::Iterator pulls rows lazily and caches them, so it can be walked, reset, and walked again. Beyond next it offers first (reset and return the first item), last, list (exhaust and return everything), and ready (true once results are available; always true for synchronous queries). See DBIx::QuickORM::Iterator for details.

CREATING, UPDATING, AND DELETING

Insert

my $row = $con->handle('people')->insert({name => 'Bob', surname => 'smith'});

insert returns a row object. By default only the primary key is read back from the database after the insert; the rest of the row reflects the values you supplied. If triggers or column defaults might change the stored data, use insert_and_refresh (or turn on auto_refresh) to re-read the full row:

my $row = $con->handle('people')->insert_and_refresh({name => 'Bob'});

Upsert

upsert inserts the row, or updates it if a conflicting row already exists, in a single statement (the exact SQL depends on the dialect). upsert_and_refresh adds the post-write refresh.

my $row = $con->handle('people')->upsert({id => 1, name => 'Bob'});

Update

update applies a hashref of changes to every row matching the handle's WHERE clause:

# Deactivate every smith.
$con->handle('people')->where({surname => 'smith'})->update({active => 0});

Passing a row object instead writes that row's pending changes. update cannot be combined with limit or order_by.

Delete

delete removes the rows the handle matches:

$con->handle('people')->where({active => 0})->delete;

With no argument it deletes everything the handle would match. You may also pass a row or a WHERE clause directly, which overrides whatever the handle already carried:

$con->handle('people')->delete({id => 1});

Vivify

vivify builds a row object from a data hashref without touching the database. Nothing is written until you call $row->insert or $row->save on it:

my $row = $con->handle('people')->vivify({name => 'Bob'});
$row->insert;    # now it hits the database

ROW OBJECTS

Fetching and mutating methods return DBIx::QuickORM::Row objects. Briefly, a row gives you $row->field($name) (inflated value) and $row->raw_field($name) (raw stored value) for access, and insert / update / delete / save / refresh / discard for persistence. See DBIx::QuickORM::Row for the full interface.

DATA-ONLY MODE

Sometimes you want plain hashrefs rather than blessed row objects (for reporting, bulk export, or to skip the cache and inflation machinery). data_only gives you a handle that returns hashrefs from its fetch methods:

my @rows = $con->handle('people')->data_only->all;
# each element is a plain hashref, not a row object

data_only applies to the read methods (one, first, all, iterator, iterate); it is not valid for inserts.

SEE ALSO

DBIx::QuickORM::Handle

The complete handle reference: every builder and terminal method, constructor arguments, and flags.

DBIx::QuickORM::Row

What you get back from a fetch, and how to mutate and persist it.

DBIx::QuickORM::Manual::Relations

Defining links (foreign keys), following them between rows, and joining sources. Joins are built from handles ($h->join(...) and friends) but are documented there.

DBIx::QuickORM::Manual::Async

Running queries asynchronously, aside (on a second connection), or forked. Handles toggle these modes with $h->async, $h->aside, and $h->forked; the mechanics live there.

DBIx::QuickORM::Manual::Transactions

Wrapping work in transactions and savepoints, including the internal transactions some multi-query handle operations use.

DBIx::QuickORM::Manual

The documentation hub for the rest of the manual.

SOURCE

The source code repository for DBIx-QuickORM can be found at https://github.com/exodist/DBIx-QuickORM/.

MAINTAINERS

Chad Granum <exodist@cpan.org>

AUTHORS

Chad Granum <exodist@cpan.org>

COPYRIGHT

Copyright Chad Granum <exodist7@gmail.com>.

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

See https://dev.perl.org/licenses/