Alessandro Ranellucci > DBIx-Lite-0.14 > DBIx::Lite::ResultSet

Download:
DBIx-Lite-0.14.tar.gz

Dependencies

Annotate this POD

View/Report Bugs
Module Version: 0.14   Source   Latest Release: DBIx-Lite-0.15

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.

syntax highlighting: