view release on metacpan or  search on metacpan

lib/DBIx/Lite/ResultSet.pm  view on Meta::CPAN

(C<<{-asc => $column_name}>>).

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

=head2 group_by

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

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

=head2 having

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

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

=head2 limit

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

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

=head2 offset

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

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

=head2 distinct

This method sets the DISTINCT flag in the SQL query. If one or more columns (as
plain strings) or expressions (as scalar refs) are passed to the method, they will
be used as part of a C<DISTINCT ON> clause (PostgreSQL only).

    my $authors = $dbix->table('authors')->select('name')->distinct;
    my $authors = $dbix->table('authors')->select('name')->distinct('name');
    my $authors = $dbix->table('authors')->select('name')->distinct(\'lower(name)');

=head2 for_update

This method accepts no argument. It enables the addition of the SQL C<FOR UPDATE>
clause at the end of the query, which allows to fetch data and lock it for updating.
It returns a L<DBIx::Lite::ResultSet> object to allow for further method chaining.
Note that no records are actually locked until the query is executed with L<single()>,
L<all()> or L<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 L<for> method described below:

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

=head2 for

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

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

=head2 inner_join

This method accepts the name of a column to join and a set of join conditions.
It returns a L<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 L<search> and L<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 I<books> table to the I<authors> table, so 
the join condition maps I<my> C<author_id> column to I<their> C<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 I<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
I<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' });

=head2 left_join

This method works like L<inner_join> except it applies a C<LEFT JOIN> instead of an
C<INNER JOIN>.