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 (but see DBIx::Mint::Table for getting objects from a specific class).

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

CONSTRUCTOR

new

It expects two arguments:

table

Used as the table to start building queries. You will join to this table or fetch data from this table. Required.

instance

Name of the DBIx::Mint instance to use.

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 feed the SQL::Abstract::More select method with their respective clause.

page, set_rows_per_page

These methods help in pagination of query results. They let you set the number of records per page (set_rows_per_page) and to fetch a given page. The default for set_rows_per_page is 10 records.

They work by setting LIMIT and OFFSET in the SQL query.

TABLE 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' })
    ->left_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 its values refer to the table that will be joined. If you don't use aliases, joins always refer to the initial table.

FETCHING DATA

select_sql

This method will return a SQL select statement and a list of values to bind, most helpful when debugging:

 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 a list with 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;
 }

This is the most efficient way to retrieve records.

count

This method will return the number of records matching your query. Internally, it builds a new query with the same search criteria as your original one, and asks for the count of matching records. Use it before adding pagination or other result-limiting constaints:

 $rs = $rs->select( 'name', 'slogan', 'logo' )->search({ group => 'A'});
 my $count = $rs->count;
 my @records = $rs->set_rows_per_page(10)->page(5)->all;

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