DBIx::Lite::ResultSet
version 0.11
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:
table()
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');
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:
search()
AND
my $rs = $books_rs->search({ year => 2012 })->search({ genre => 'philosophy' });
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');
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}>).
+
-
ASC
DESC
{-asc =
my $rs = $books_rs->order_by('year'); my $rs = $books_rs->order_by('+genre', '-year');
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.
GROUP BY
my $rs = $dbix ->table('books') ->select('genre', \ 'COUNT(*)') ->group_by('genre');
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.
HAVING
my $rs = $dbix ->table('books') ->select('genre', \ 'COUNT(*)') ->group_by('genre') ->having({ year => 2012 });
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.
LIMIT
my $rs = $books_rs->limit(5);
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.
OFFSET
my $rs = $books_rs->limit(5)->offset(10);
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 join conditions are in the form my columns => their columns. 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.
author_id
id
This method works like "inner join" except it applies a LEFT JOIN instead of an INNER JOIN.
LEFT JOIN
INNER JOIN
This method will execute the SELECT query and will return a list of DBIx::Lite::Row objects.
SELECT
my @books = $books_rs->all;
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;
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:
find
$dbix->schema->table('books')->pk('id'); my $book = $dbix->table('books')->find(20);
This method will execute a SELECT COUNT(*) query and will return the resulting number.
SELECT COUNT(*)
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) { ... }
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');
This method accepts a hashref with column values to pass to the INSERT SQL command. It returns the inserted DBIx::Lite::Row object. If you specified an autoincrementing primary key and your database driver is supported, DBIx::Lite will retrieve it and populate the resulting object accordingly.
INSERT
my $book = $dbix ->table('books') ->insert({ name => 'Camel Tales', year => 2012 });
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 });
This method accepts a hashref with column values to pass to the UPDATE SQL command.
UPDATE
$dbix->table('books') ->search({ year => { '<' => 1920 } }) ->update({ very_old => 1 });
This method performs a DELETE SQL command.
DELETE
$books_rs->delete;
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.
$dbh
my ($sql, @bind) = $books_rs->select_sql;
This methods executes the SQL SELECT statement and returns it.
my $sth = $books_rs->select_sth;
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 });
This methods executes the SQL INSERT statement and returns it.
my $sth = $dbix ->table('books') ->insert_sth({ name => 'Camel Tales', year => 2012 });
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' });
This method executes the SQL UPDATE statement and returns it.
my $sth = $books_rs->update_sth({ genre => 'tennis' });
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;
This method executes the SQL DELETE statement and returns it.
my $sth = $books_rs->delete_sth;
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);
This method accepts the number of rows for each page. It defaults to 10, and it has no effect unless page is also called. 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) { ... }
Alessandro Ranellucci <aar@cpan.org>
This software is copyright (c) 2012 by Alessandro Ranellucci.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.
To install DBIx::Lite, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Lite
CPAN shell
perl -MCPAN -e shell install DBIx::Lite
For more information on module installation, please visit the detailed CPAN module installation guide.