The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
DBIx-Class-0.04999_03
River stage three • 342 direct dependents • 452 total dependents
289 ++
/ DBIx::Class::Manual::Cookbook

NAME

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

RECIPES

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 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 WHERE clause:

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

For more information on generating complex queries, see "WHERE CLAUSES" in SQL::Abstract.

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;
  };

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!

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->has_one('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'

Transactions

As of version 0.04001, there is improved transaction support in 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.

Many-to-many relationships

This is straightforward using 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

Setting default values

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

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

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

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

Stringification

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

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