- Add synopsis

[dbsrgits/DBIx-Class.git] / lib / DBIx / Class / Manual / Cookbook.pod

=head1 NAME 

DBIx::Class::Manual::Cookbook - Miscellaneous recipes

=head1 RECIPES

=head2 Paged results

When you expect a large number of results, you can ask DBIx::Class for a paged
resultset, which will fetch only a small number of records at a time:

    $rs = $schema->resultset('Artist')->search(
        {},
        {
            page => 1, # page to return (defaults to 1)
            rows => 10, # number of results per page
        },
    );

    $rs->all(); # return all records for page 1

The "page" attribute does not have to be specified in your search:

    $rs = $schema->resultset('Artist')->search(
        {},
        {
            rows => 10,
        }
    );

    $rs->page(1); # return DBIx::Class::ResultSet containing first 10 records

In either of the above cases, you can return a L<Data::Page> object for the
resultset (suitable for use in a TT template etc) using the pager() method:

    $pager = $rs->pager();

=head2 Complex searches

Sometimes you need to formulate a query using specific operators:

  my @albums = $schema->resultset('Album')->search({
    artist => { 'like', '%Lamb%' },
    title  => { 'like', '%Fear of Fours%' },
  });

This results in something like the following C<WHERE> clause:

  WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%'

Other queries might require slightly more complex logic:

  my @albums = $schema->resultset('Album')->search({
    -or => [
      -and => [
        artist => { 'like', '%Smashing Pumpkins%' },
        title  => 'Siamese Dream',
      ],
      artist => 'Starchildren',
    ],
  });

This results in the following C<WHERE> clause:

  WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' )
    OR artist = 'Starchildren'

For more information on generating complex queries, see
L<SQL::Abstract/WHERE CLAUSES>.

=head2 Disconnecting cleanly

If you find yourself quitting an app with Control-C a lot during
development, you might like to put the following signal handler in
your main database class to make sure it disconnects cleanly:

  $SIG{INT} = sub {
    __PACKAGE__->storage->dbh->disconnect;
  };

=head2 Using cols

When you only want selected columns from a table, you can use "cols" to
specify which ones you need (you could also use "select", but "cols" is the
recommended way):

    $rs = $schema->resultset('Artist')->search(
        {},
        {
            cols => [qw/ name /]
        }
    );

    # e.g.
    # SELECT artist.name FROM artist

=head2 Using select and as

The combination of "select" and "as" is probably most useful when you want to
return the result of a function or stored procedure as a column value. You use
"select" to specify the source for your column value (e.g. a column name,
function or stored procedure name). You then use "as" to set the column name
you will use to access the returned value:

    $rs = $schema->resultset('Artist')->search(
        {},
        {
            select => [ 'name', { LENGTH => 'name' } ],
            as => [qw/ name name_length /],
        }
    );

    # e.g.
    # SELECT name name, LENGTH( name ) name_length
    # FROM artist

If your alias exists as a column in your base class (i.e. it was added with
add_columns()), you just access it as normal. Our Artist class has a "name"
column, so we just use the "name" accessor:

    my $artist = $rs->first();
    my $name = $artist->name();

If on the other hand the alias does not correspond to an existing column, you
can get the value using the get_column() accessor:

    my $name_length = $artist->get_column('name_length');

If you don't like using "get_column()", you can always create an accessor for
any of your aliases using either of these:

    # define accessor manually
    sub name_length { shift->get_column('name_length'); }
    
    # or use DBIx::Class::AccessorGroup
    __PACKAGE__->mk_group_accessors('column' => 'name_length');

=head2 SELECT DISTINCT with multiple columns

    $rs = $schema->resultset('Foo')->search(
        {},
        {
            select => [
                { distinct => [ $source->columns ] }
            ],
            as => [ $source->columns ]
        }
    );

=head2 SELECT COUNT(DISTINCT colname)

    $rs = $schema->resultset('Foo')->search(
        {},
        {
            select => [
                { count => { distinct => 'colname' } }
            ],
            as => [ 'count' ]
        }
    );

=head2 Grouping results

DBIx::Class supports GROUP BY as follows:

    $rs = $schema->resultset('Artist')->search(
        {},
        {
            join => [qw/ cds /],
            select => [ 'name', { count => 'cds.cdid' } ],
            as => [qw/ name cd_count /],
            group_by => [qw/ name /]
        }
    );

    # e.g.
    # SELECT name, COUNT( cds.cdid ) FROM artist me
    # LEFT JOIN cd cds ON ( cds.artist = me.artistid )
    # GROUP BY name

=head2 Using joins and prefetch

You can use the "join" attribute to allow searching on, or sorting your
results by, one or more columns in a related table. To return
all CDs matching a particular artist name:

    my $rs = $schema->resultset('CD')->search(
        {
            'artist.name' => 'Bob Marley'    
        },
        {
            join => [qw/artist/], # join the artist table
        }
    );

    # equivalent SQL:
    # SELECT cd.* FROM cd
    # JOIN artist ON cd.artist = artist.id
    # WHERE artist.name = 'Bob Marley'

If required, you can now sort on any column in the related table(s) by
including it in your "order_by" attribute:

    my $rs = $schema->resultset('CD')->search(
        {
            'artist.name' => 'Bob Marley'
        },
        {
            join => [qw/ artist /],
            order_by => [qw/ artist.name /]
        }
    };

    # equivalent SQL:
    # SELECT cd.* FROM cd
    # JOIN artist ON cd.artist = artist.id
    # WHERE artist.name = 'Bob Marley'
    # ORDER BY artist.name

Note that the "join" attribute should only be used when you need to search or
sort using columns in a related table. Joining related tables when you
only need columns from the main table will make performance worse!

Now let's say you want to display a list of CDs, each with the name of
the artist. The following will work fine:

    while (my $cd = $rs->next) {
        print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
    }

There is a problem however. We have searched both cd and artist tables in our
main query, but we have only returned data from the cd table. To get the artist
name for any of the CD objects returned, DBIx::Class will go back to the
database:

    SELECT artist.* FROM artist WHERE artist.id = ?

A statement like the one above will run for each and every CD returned by our
main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
queries!

Thankfully, DBIx::Class has a "prefetch" attribute to solve this problem. This
allows you to fetch results from a related table as well as the main table
for your class:

    my $rs = $schema->resultset('CD')->search(
        {
            'artist.name' => 'Bob Marley'
        },
        {
            join => [qw/ artist /],
            order_by => [qw/ artist.name /],
            prefetch => [qw/ artist /] # return artist data too!
        }
    );

    # equivalent SQL (note SELECT from both "cd" and "artist")
    # SELECT cd.*, artist.* FROM cd
    # JOIN artist ON cd.artist = artist.id
    # WHERE artist.name = 'Bob Marley'
    # ORDER BY artist.name

The code to print the CD list remains the same:

    while (my $cd = $rs->next) {
        print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
    }

DBIx::Class has now prefetched all matching data from the "artist" table,
so no additional SQL statements are executed. You now have a much more
efficient query.

Note that as of DBIx::Class 0.04, "prefetch" cannot be used with has_many
relationships. You will get an error along the lines of "No accessor for
prefetched ..." if you try.

Note that "prefetch" should only be used when you know you will
definitely use data from a related table. Pre-fetching related tables when you
only need columns from the main table will make performance worse!

=head2 Multi-step joins

Sometimes you want to join more than one relationship deep. In this example,
we want to find all Artist objects who have CDs whose LinerNotes contain a
specific string:

    # Artist->has_many('cds' => 'CD', 'artist');
    # CD->has_one('liner_notes' => 'LinerNotes', 'cd');

    $rs = $schema->resultset('Artist')->search(
        {
            'liner_notes.notes' => { 'like', '%some text%' },
        },
        {
            join => {
                'cds' => 'liner_notes'
            }
        }
    );

    # equivalent SQL
    # SELECT artist.* FROM artist
    # JOIN ( cd ON artist.id = cd.artist )
    # JOIN ( liner_notes ON cd.id = liner_notes.cd )
    # WHERE liner_notes.notes LIKE '%some text%'

Joins can be nested to an arbitrary level. So if we decide later that we
want to reduce the number of Artists returned based on who wrote the liner
notes:

    # LinerNotes->belongs_to('author' => 'Person');

    $rs = $schema->resultset('Artist')->search(
        {
            'liner_notes.notes' => { 'like', '%some text%' },
            'author.name' => 'A. Writer'
        },
        {
            join => {
                'cds' => {
                    'liner_notes' => 'author'
                }
            }
        }
    );

    # equivalent SQL
    # SELECT artist.* FROM artist
    # JOIN ( cd ON artist.id = cd.artist )
    # JOIN ( liner_notes ON cd.id = liner_notes.cd )
    # JOIN ( author ON author.id = liner_notes.author )
    # WHERE liner_notes.notes LIKE '%some text%'
    # AND author.name = 'A. Writer'

=head2 Transactions

As of version 0.04001, there is improved transaction support in
L<DBIx::Class::Storage::DBI>.  Here is an example of the recommended
way to use it:

  my $genus = Genus->find(12);
  eval {
    MyDB->txn_begin;
    $genus->add_to_species({ name => 'troglodyte' });
    $genus->wings(2);
    $genus->update;
    cromulate($genus); # Can have a nested transation
    MyDB->txn_commit;
  };
  if ($@) {
    # Rollback might fail, too
    eval {
      MyDB->txn_rollback
    };
  }

Currently, a nested commit will do nothing and a nested rollback will
die.  The code at each level must be sure to call rollback in the case
of an error, to ensure that the rollback will propagate to the top
level and be issued.  Support for savepoints and for true nested
transactions (for databases that support them) will hopefully be added
in the future.

=head2 Many-to-many relationships

This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:

    package My::DB;
    # set up connection here...

    package My::User;
    use base 'My::DB';
    __PACKAGE__->table('user');
    __PACKAGE__->add_columns(qw/id name/);
    __PACKAGE__->set_primary_key('id');
    __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
    __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');

    package My::UserAddress;
    use base 'My::DB';
    __PACKAGE__->table('user_address');
    __PACKAGE__->add_columns(qw/user address/);
    __PACKAGE__->set_primary_key(qw/user address/);
    __PACKAGE__->belongs_to('user' => 'My::User');
    __PACKAGE__->belongs_to('address' => 'My::Address');

    package My::Address;
    use base 'My::DB';
    __PACKAGE__->table('address');
    __PACKAGE__->add_columns(qw/id street town area_code country/);
    __PACKAGE__->set_primary_key('id');
    __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
    __PACKAGE__->many_to_many('users' => 'user_address', 'user');

    $rs = $user->addresses(); # get all addresses for a user
    $rs = $address->users(); # get all users for an address

=head2 Setting default values

It's as simple as overriding the C<new> method.  Note the use of
C<next::method>.

  sub new {
    my ( $class, $attrs ) = @_;

    $attrs->{foo} = 'bar' unless defined $attrs->{foo};

    $class->next::method($attrs);
  }

=head2 Stringification

Employ the standard stringification technique by using the C<overload>
module.  Replace C<foo> with the column/method of your choice.

  use overload '""' => 'foo', fallback => 1;

=cut