NAME
DBIx::Lite::ResultSet
VERSION
version 0.14
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' });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']);order_by
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);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 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.
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 });left_join
This method works like "inner join" except it applies a LEFT JOIN instead of an INNER JOIN.
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;next
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) {
...
}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. If you specified an autoincrementing primary key and your database driver is supported, DBIx::Lite will retrieve it and populate the resulting object accordingly.
my $book = $dbix
->table('books')
->insert({ name => 'Camel Tales', year => 2012 });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.
$dbix->table('books')
->search({ year => { '<' => 1920 } })
->update({ very_old => 1 });delete
This method performs a DELETE SQL command.
$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;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. It returns a DBIx::Lite::ResultSet object to allow for further method chaining.
my $rs = $books_rs->rows_per_page(50)->page(3);pager
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 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.