NAME

DBIx::Lite::ResultSet

VERSION

version 0.33

OVERVIEW

This class is not supposed to be instantiated manually. You usually get your first ResultSet object by calling the "table()" method on your DBIx::Lite object:

    my $books_rs = $dbix->table('books');

and then you can chain methods on it to build your query:

    my $old_books_rs = $books_rs
        ->search({ year => { '<' => 1920 } })
        ->order_by('year');

BUILDING THE QUERY

search

This method accepts a search condition using the SQL::Abstract syntax and returns a DBIx::Lite::ResultSet object with the condition applied.

    my $young_authors_rs = $authors_rs->search({ age => { '<' => 18 } });

Multiple "search()" methods can be chained; they will be merged using the "AND" operator:

    my $rs = $books_rs->search({ year => 2012 })->search({ genre => 'philosophy' });

clear_search

This method returns a DBIx::Lite::ResultSet object based on the current one but with no search conditions.

select

This method accepts a list of column names to retrieve. The default is "*", so all columns will be retrieved. It returns a DBIx::Lite::ResultSet object to allow for further method chaining.

    my $rs = $books_rs->select('title', 'year');

If you want to rename a column, pass it as an arrayref:

    my $rs = $books_rs->select(['title' => 'book_title'], 'year');
    # SELECT title AS book_title, year FROM books ...

select_also

This method works like select but it adds the passed columns to the ones already selected. It is useful when joining:

    my $books_authors_rs = $books_rs
        ->left_join('authors', { author_id => 'id' })
        ->select_also(['authors.name' => 'author_name']);

This method accepts a list of columns for sorting. It returns a DBIx::Lite::ResultSet object to allow for further method chaining. Columns can be prefixed with "+" or "-" to indicate sorting direction ("+" is "ASC", "-" is "DESC") or they can be expressed using the SQL::Abstract syntax ("<{-asc =" $column_name}>>).

    my $rs = $books_rs->order_by('year');
    my $rs = $books_rs->order_by('+genre', '-year');

group_by

This method accepts a list of columns to insert in the SQL "GROUP BY" clause. It returns a DBIx::Lite::ResultSet object to allow for further method chaining.

    my $rs = $dbix
        ->table('books')
        ->select('genre', \ 'COUNT(*)')
        ->group_by('genre');

having

This method accepts a search condition to insert in the SQL "HAVING" clause (in combination with group_by). It returns a DBIx::Lite::ResultSet object to allow for further method chaining.

    my $rs = $dbix
        ->table('books')
        ->select('genre', \ 'COUNT(*)')
        ->group_by('genre')
        ->having({ year => 2012 });

limit

This method accepts a number of rows to insert in the SQL "LIMIT" clause (or whatever your RDBMS dialect uses for that purpose). See the page method too if you want an easier interface for pagination. It returns a DBIx::Lite::ResultSet object to allow for further method chaining.

    my $rs = $books_rs->limit(5);

offset

This method accepts the index of the first row to retrieve; it will be used in the SQL "OFFSET" clause (or whatever your RDBMS dialect used for that purpose). See the page method too if you want an easier interface for pagination. It returns a DBIx::Lite::ResultSet object to allow for further method chaining.

    my $rs = $books_rs->limit(5)->offset(10);

for_update

This method accepts no argument. It enables the addition of the SQL "FOR UPDATE" clause at the end of the query, which allows to fetch data and lock it for updating. It returns a DBIx::Lite::ResultSet object to allow for further method chaining. Note that no records are actually locked until the query is executed with single(), all() or next().

    $dbix->txn(sub {
        my $author = $dbix->table('authors')->find($id)->for_update->single
            or die "Author not found";
        
        $author->update({ age => 30 });
    });

This is actually a shortcut for the for method described below:

    my $authors = $dbix->table('authors')->for('UPDATE');

for

This method accepts a string which will be appended to the "FOR" keyword at the end of the SQL query.

    my $authors = $dbix->table('authors')->for('UPDATE SKIP LOCKED');

inner_join

This method accepts the name of a column to join and a set of join conditions. It returns a DBIx::Lite::ResultSet object to allow for further method chaining.

    my $rs = $books_rs->inner_join('authors', { author_id => 'id' });

The second argument (join conditions) is a normal search hashref like the one supported by search and SQL::Abstract. However, values are assumed to be column names instead of actual values. Unless you specify your own table aliases using the dot notation, the hashref keys are considered to be column names belonging to the left table and the hashref values are considered to be column names belonging to the joined table:

    my $rs = $books_rs->inner_join($other_table, { $my_column => $other_table_column });

In the above example, we're selecting from the books table to the authors table, so the join condition maps my "author_id" column to their "id" column. In order to use more sophisticated join conditions you can use the normal SQL::Abstract syntax including literal SQL:

    my $rs = $books_rs->inner_join('authors', { author_id => 'id', 'authors.age' => { '<' => $age } });
    my $rs = $books_rs->inner_join('authors', { author_id => 'id', 'authors.age' => \"< 18" });

The third, optional, argument can be a hashref with options. The only supported one is currently prevent_duplicates: set this to true to have DBIx::Lite check whether you already joined the same table in this query. If you did, this join will be skipped:

    my $rs = $books_rs->inner_join('authors', { author_id => 'id' }, { prevent_duplicates => 1 });

If you want to specify a table alias, just supply an arrayref. In this case, the prevent_duplicates option will only check whether the supplied table alias was already used, thus allowing to join the same table multiple times using different table aliases.

    my $rs = $books_rs->inner_join(['authors' => 't1'], { author_id => 'id' });

left_join

This method works like inner_join except it applies a "LEFT JOIN" instead of an "INNER JOIN".

clear_joins

This method returns a DBIx::Lite::ResultSet object based on the current one but with no joins.

with

This method accepts a hash of CTE definitions supported by PostgreSQL and other RDBMS. Definitions can be supplied as scalar refs or refs to arrayrefs:

    my $authors = $dbix->table('authors')->with(
        t  => \"SELECT * FROM foo",
        t2 => \"SELECT * FROM bar",
    );
    # The above will produce:
    # WITH (t AS (SELECT * FROM foo), t2 AS (SELECT * FROM bar))
    # SELECT * FROM authors
    
    my $authors = $dbix->table('authors')
        ->with(t => \["SELECT * FROM foo WHERE bar = ?", $bindval]);
    
    my $authors = $dbix->table('authors')
        ->with(t => \[ $dbix->table('foo')->select_sql ]);

Subsequent calls to this method will replace the entire with block.

with_also

This methods works like with but it adds CTEs to the list instead of replacing the existing ones (except when the same alias is reused).

from

This method allows to replace the "FROM" expression in order to use subqueries or CTEs.

    my $books = $dbix->table('books')
        ->with(t => \[ $dbix->table('books')->select_sql ])
        ->from('t');

If you supply a scalarref, it will be treated like literal SQL.

Usage of from is not currently compatible with joins.

RETRIEVING RESULTS

all

This method will execute the "SELECT" query and will return a list of DBIx::Lite::Row objects.

    my @books = $books_rs->all;

single

This method will execute the "SELECT" query and will return a DBIx::Lite::Row object populated with the first row found; if none is found, undef is returned.

    my $book = $dbix->table('books')->search({ id => 20 })->single;

find

This method is a shortcut for search and single. The following statement is equivalent to the one in the previous example:

    my $book = $dbix->table('books')->find({ id => 20 });

If you specified a primary key for the table (see the docs for DBIx::Lite::Schema) you can just pass its value(s) to "find":

    $dbix->schema->table('books')->pk('id');
    my $book = $dbix->table('books')->find(20);

count

This method will execute a "SELECT COUNT(*)" query and will return the resulting number.

    my $book_count = $books_rs->count;

This method is a convenient iterator to retrieve your results efficiently without loading all of them in memory.

    while (my $book = $books_rs->next) {
        ...
    }

Note that you have to store your query before iteratingm like in the example above. The following syntax will always retrieve just the first row in an endless loop:

    while (my $book = $dbix->table('books')->next) {
        ...
    }

column_names

This method returns a list of column names. It returns array on list context and array reference on scalar context.

    my @book_columns = $books_rs->column_names;
    my $book_columns = $books_rs->column_names; # array reference

get_column

This method accepts a column name to fetch. It will execute a "SELECT" query to retrieve that column only and it will return a list with the values.

    my @book_titles = $books_rs->get_column('title');

single_value

This method returns the value of the first cell of the first row. It's useful in situations like this:

    my $max = $books_rs->select(\"MAX(pages)")->single_value;

MANIPULATING ROWS

insert

This method accepts a hashref with column values to pass to the "INSERT" SQL command. It returns the inserted DBIx::Lite::Row object (note that the returned object will not contain any value set as default by your RDBMS such as ones populated by triggers). If you specified an autoincrementing primary key for this table and your database driver is supported, DBIx::Lite will retrieve such value and populate the resulting object accordingly.

    my $book = $dbix
        ->table('books')
        ->insert({ name => 'Camel Tales', year => 2012 });

Note that joins have no effect on "INSERT" commands and DBIx::Lite will throw a warning.

find_or_insert

This method works like insert but it will perform a find search to check that no row already exists for the supplied column values. If a row is found it is returned, otherwise a SQL "INSERT" is performed and the inserted row is returned.

    my $book = $dbix
        ->table('books')
        ->find_or_insert({ name => 'Camel Tales', year => 2012 });

update

This method accepts a hashref with column values to pass to the "UPDATE" SQL command. It returns the number of affected rows.

    $dbix->table('books')
        ->search({ year => { '<' => 1920 } })
        ->update({ very_old => 1 });

delete

This method performs a "DELETE" SQL command. It returns the number of affected rows.

    $books_rs->delete;

select_sql

This method returns a list having the SQL "SELECT" statement as the first item, and bind values as subsequent values. No query is executed. This method also works when no $dbh or connection data is supplied to DBIx::Lite.

    my ($sql, @bind) = $books_rs->select_sql;

select_sth

This methods prepares the SQL "SELECT" statement and returns it along with bind values.

    my ($sth, @bind) = $books_rs->select_sth;

insert_sql

This method works like insert but it will just return a list having the SQL statement as the first item, and bind values as subsequent values. No query is executed. This method also works when no $dbh or connection data is supplied to DBIx::Lite.

    my ($sql, @bind) = $dbix
        ->table('books')
        ->insert_sql({ name => 'Camel Tales', year => 2012 });

insert_sth

This methods prepares the SQL "INSERT" statement and returns it along with bind values.

   my ($sth, @bind) = $dbix
        ->table('books')
        ->insert_sth({ name => 'Camel Tales', year => 2012 });

update_sql

This method works like update but it will just return a list having the SQL statement as the first item, and bind values as subsequent values. No query is executed. This method also works when no $dbh or connection data is supplied to DBIx::Lite.

    my ($sql, @bind) = $books_rs->update_sql({ genre => 'tennis' });

update_sth

This method prepares the SQL "UPDATE" statement and returns it along with bind values.

    my ($sth, @bind) = $books_rs->update_sth({ genre => 'tennis' });

delete_sql

This method works like delete but it will just return a list having the SQL statement as the first item, and bind values as subsequent values. No query is executed. This method also works when no $dbh or connection data is supplied to DBIx::Lite.

    my ($sql, @bind) = $books_rs->delete_sql;

delete_sth

This method prepares the SQL "DELETE" statement and returns it along with bind values.

    my ($sth, @bind) = $books_rs->delete_sth;

where_sql

This method generates just the "WHERE" clause that you can embed in other statements. It will return a list having the SQL string as the first item, and bind values as subsequent values. No query is executed. This method also works when no $dbh or connection data is supplied to DBIx::Lite.

    my ($sql, @bind) = $books_rs->where_sql;

PAGING

page

This method accepts a page number. It defaults to 0, meaning no pagination. First page has index 1. Usage of this method implies limit and offset, so don't call them. It returns a DBIx::Lite::ResultSet object to allow for further method chaining.

    my $rs = $books_rs->page(3);

rows_per_page

This method accepts the number of rows for each page. It defaults to 10, and it has no effect unless page is also called. The undef value means that all records will be put on a single page. It returns a DBIx::Lite::ResultSet object to allow for further method chaining.

    my $rs = $books_rs->rows_per_page(50)->page(3);

This method returns a Data::Page object already configured for the current query. Calling this method will execute a count query to retrieve the total number of rows.

    my $rs = $books_rs->rows_per_page(50)->page(3);
    my $page = $rs->pager;
    printf "Showing results %d - %d (total: %d)\n",
        $page->first, $page->last, $page->total_entries;
    while (my $book = $rs->next) {
        ...
    }

AUTHOR

Alessandro Ranellucci <aar@cpan.org>

COPYRIGHT AND LICENSE

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