NAME

DBIx::Mint::ResultSet - DBIx::Mint class to build database queries

SYNOPSIS

 # Create your ResultSet object:
 my $rs = DBIx::Mint::ResultSet->new( table => 'teams' );
 
 # Now, build your query:
 $rs = $rs->select( 'name', 'slogan', 'logo' )->search({ group => 'A'});
 
 # Join tables
 $rs = DBIx::Mint::ResultSet
          ->new( table => 'teams' )
          ->inner_join('players', { id => 'teams'});
 
 # Fetch data
 $rs->set_target_class( 'Bloodbowl::Team' );
 my @teams   = $rs->all;
 my $team    = $rs->single;
 
 $rs->as_iterator;
 while (my $team = $rs->next) {
     say $team->slogan;
 }
 

DESCRIPTION

Objects of this class allow you to fetch information from the database. ResultSet objects do not know about the database schema, which means that you can use them without one and that you must use table names directly.

Query creation and join methods return a clone of the original ResultSet object. This makes them chaineable.

Records can be returned as hash references or they can be inflated to the target class you set. You can get a single result, a list of all results or an iterator.

METHODS

QUERY CREATION METHODS

select

Takes a list of field names to fetch from the given table or join. This method can be called several times to add different fields.

search

Builds the 'where' part of the query. It takes a data structure defined per the syntax of SQL::Abstract.

order_by, limit, offset, group_by, having

These methods simply feed the SQL::Abstract::More select method with their respective clause.

page, set_rows_per_page

These methods simply specify limits and offsets suitable for pagination. You set the number of records that you want per page, and the page that you need to fetch from the database.

JOINS

DBIx::Mint::ResultSet offers inner and left joins between tables. The syntax is quite simple:

 $rs->new( table => 'coaches' )->inner_join( 'teams', { id => 'coach' });

The above call would produce a join between the tables 'coaches' and 'teams' using the fields 'id' from coaches and 'coach' from teams.

 $rs->new( table => 'coaches' )
    ->inner_join( ['teams',   't'], { 'me.id'  => 't.coach' })
    ->inner_join( ['players', 'p'], { 't.id'   => 'p.team'  });

You can alias the table names. 'me' always refers to the starting table (coaches in the example above).

Note that the first example does not include table aliases. In this case, the keys of the hash reference are fields of the starting table (coaches) and values of the hash reference refer to the table specified in the same call. This is valid for longer joins.

FETCHING DATA

To actually execute the query and fetch data you have a few methods:

select_sql

This method will simply return a SQL select statement and a list of values to bind:

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

set_taget_class

While not precisely a fetching method, it does define the class to bless fetched records. It is called like this:

 $rs = $rs->set_target_class('Bloodbowl::Coach');

single

This method will return a single record from your query. It sets LIMIT to 1 and calls finish on the DBI statement holder. It returns a blessed object if you have set a target class earlier.

all

Returns all the records that result from your query. The records will be inflated to the target class if it was set earlier.

as_iterator

This will add an iterator to the ResultSet object, over which you must call 'next' to fetch a record:

 $rs->as_iterator;
 while (my $record = $rs->next ) {
     say $record->name;
 }

SEE ALSO

This module is part of DBIx::Mint.

ACKNOWLEDGEMENTS

This module is heavily based on DBIx::Lite, by Alessandro Ranellucci.

AUTHOR

Julio Fraire, <julio.fraire@gmail.com>

LICENCE AND COPYRIGHT

Copyright (c) 2013, Julio Fraire. All rights reserved.

LICENSE

This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself. See perlartistic.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

cpanm DBIx::Mint

perl -MCPAN -e shell
install DBIx::Mint