3 DBIx::Class::Manual::Cookbook - Miscellaneous recipes
11 When you expect a large number of results, you can ask L<DBIx::Class> for a
12 paged resultset, which will fetch only a small number of records at a time:
14 my $rs = $schema->resultset('Artist')->search(
17 page => 1, # page to return (defaults to 1)
18 rows => 10, # number of results per page
22 return $rs->all(); # all records for page 1
24 The C<page> attribute does not have to be specified in your search:
26 my $rs = $schema->resultset('Artist')->search(
33 return $rs->page(1); # DBIx::Class::ResultSet containing first 10 records
35 In either of the above cases, you can return a L<Data::Page> object for the
36 resultset (suitable for use in e.g. a template) using the C<pager> method:
40 =head3 Complex WHERE clauses
42 Sometimes you need to formulate a query using specific operators:
44 my @albums = $schema->resultset('Album')->search({
45 artist => { 'like', '%Lamb%' },
46 title => { 'like', '%Fear of Fours%' },
49 This results in something like the following C<WHERE> clause:
51 WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%'
53 Other queries might require slightly more complex logic:
55 my @albums = $schema->resultset('Album')->search({
58 artist => { 'like', '%Smashing Pumpkins%' },
59 title => 'Siamese Dream',
61 artist => 'Starchildren',
65 This results in the following C<WHERE> clause:
67 WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' )
68 OR artist = 'Starchildren'
70 For more information on generating complex queries, see
71 L<SQL::Abstract/WHERE CLAUSES>.
73 =head3 Using specific columns
75 When you only want selected columns from a table, you can use C<cols> to
76 specify which ones you need:
78 my $rs = $schema->resultset('Artist')->search(
81 columns => [qw/ name /]
86 # SELECT artist.name FROM artist
88 =head3 Using database functions or stored procedures
90 The combination of C<select> and C<as> can be used to return the result of a
91 database function or stored procedure as a column value. You use C<select> to
92 specify the source for your column value (e.g. a column name, function, or
93 stored procedure name). You then use C<as> to set the column name you will use
94 to access the returned value:
96 my $rs = $schema->resultset('Artist')->search(
99 select => [ 'name', { LENGTH => 'name' } ],
100 as => [qw/ name name_length /],
105 # SELECT name name, LENGTH( name ) name_length
108 If your alias exists as a column in your base class (i.e. it was added with
109 C<add_columns>), you just access it as normal. Our C<Artist> class has a C<name>
110 column, so we just use the C<name> accessor:
112 my $artist = $rs->first();
113 my $name = $artist->name();
115 If on the other hand the alias does not correspond to an existing column, you
116 can get the value using the C<get_column> accessor:
118 my $name_length = $artist->get_column('name_length');
120 If you don't like using C<get_column>, you can always create an accessor for
121 any of your aliases using either of these:
123 # Define accessor manually:
124 sub name_length { shift->get_column('name_length'); }
126 # Or use DBIx::Class::AccessorGroup:
127 __PACKAGE__->mk_group_accessors('column' => 'name_length');
129 =head3 SELECT DISTINCT with multiple columns
131 my $rs = $schema->resultset('Foo')->search(
135 { distinct => [ $source->columns ] }
137 as => [ $source->columns ]
141 my $count = $rs->next->get_column('count');
143 =head3 SELECT COUNT(DISTINCT colname)
145 my $rs = $schema->resultset('Foo')->search(
149 { count => { distinct => 'colname' } }
155 =head3 Grouping results
157 L<DBIx::Class> supports C<GROUP BY> as follows:
159 my $rs = $schema->resultset('Artist')->search(
163 select => [ 'name', { count => 'cds.cdid' } ],
164 as => [qw/ name cd_count /],
165 group_by => [qw/ name /]
170 # SELECT name, COUNT( cds.cdid ) FROM artist me
171 # LEFT JOIN cd cds ON ( cds.artist = me.artistid )
174 =head3 Predefined searches
176 You can write your own DBIx::Class::ResultSet class by inheriting from it
177 and define often used searches as methods:
179 package My::DBIC::ResultSet::CD;
182 use base 'DBIx::Class::ResultSet';
184 sub search_cds_ordered {
187 return $self->search(
189 { order_by => 'name DESC' },
195 To use your resultset, first tell DBIx::Class to create an instance of it
196 for you, in your My::DBIC::Schema::CD class:
198 __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD');
200 Then call your new method in your code:
202 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
205 =head3 Predefined searches without writing a ResultSet class
207 Alternatively you can automatically generate a DBIx::Class::ResultSet
208 class by using the ResultSetManager component and tagging your method
211 __PACKAGE__->load_components(qw/ ResultSetManager Core /);
213 sub search_cds_ordered : ResultSet {
215 return $self->search(
217 { order_by => 'name DESC' },
221 Then call your method in the same way from your code:
223 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
225 =head2 Using joins and prefetch
227 You can use the C<join> attribute to allow searching on, or sorting your
228 results by, one or more columns in a related table. To return all CDs matching
229 a particular artist name:
231 my $rs = $schema->resultset('CD')->search(
233 'artist.name' => 'Bob Marley'
236 join => [qw/artist/], # join the artist table
241 # SELECT cd.* FROM cd
242 # JOIN artist ON cd.artist = artist.id
243 # WHERE artist.name = 'Bob Marley'
245 If required, you can now sort on any column in the related tables by including
246 it in your C<order_by> attribute:
248 my $rs = $schema->resultset('CD')->search(
250 'artist.name' => 'Bob Marley'
253 join => [qw/ artist /],
254 order_by => [qw/ artist.name /]
259 # SELECT cd.* FROM cd
260 # JOIN artist ON cd.artist = artist.id
261 # WHERE artist.name = 'Bob Marley'
262 # ORDER BY artist.name
264 Note that the C<join> attribute should only be used when you need to search or
265 sort using columns in a related table. Joining related tables when you only
266 need columns from the main table will make performance worse!
268 Now let's say you want to display a list of CDs, each with the name of the
269 artist. The following will work fine:
271 while (my $cd = $rs->next) {
272 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
275 There is a problem however. We have searched both the C<cd> and C<artist> tables
276 in our main query, but we have only returned data from the C<cd> table. To get
277 the artist name for any of the CD objects returned, L<DBIx::Class> will go back
280 SELECT artist.* FROM artist WHERE artist.id = ?
282 A statement like the one above will run for each and every CD returned by our
283 main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
286 Thankfully, L<DBIx::Class> has a C<prefetch> attribute to solve this problem.
287 This allows you to fetch results from related tables in advance:
289 my $rs = $schema->resultset('CD')->search(
291 'artist.name' => 'Bob Marley'
294 join => [qw/ artist /],
295 order_by => [qw/ artist.name /],
296 prefetch => [qw/ artist /] # return artist data too!
300 # Equivalent SQL (note SELECT from both "cd" and "artist"):
301 # SELECT cd.*, artist.* FROM cd
302 # JOIN artist ON cd.artist = artist.id
303 # WHERE artist.name = 'Bob Marley'
304 # ORDER BY artist.name
306 The code to print the CD list remains the same:
308 while (my $cd = $rs->next) {
309 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
312 L<DBIx::Class> has now prefetched all matching data from the C<artist> table,
313 so no additional SQL statements are executed. You now have a much more
316 Note that as of L<DBIx::Class> 0.05999_01, C<prefetch> I<can> be used with
317 C<has_many> relationships.
319 Also note that C<prefetch> should only be used when you know you will
320 definitely use data from a related table. Pre-fetching related tables when you
321 only need columns from the main table will make performance worse!
323 =head3 Multi-step joins
325 Sometimes you want to join more than one relationship deep. In this example,
326 we want to find all C<Artist> objects who have C<CD>s whose C<LinerNotes>
327 contain a specific string:
329 # Relationships defined elsewhere:
330 # Artist->has_many('cds' => 'CD', 'artist');
331 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
333 my $rs = $schema->resultset('Artist')->search(
335 'liner_notes.notes' => { 'like', '%some text%' },
339 'cds' => 'liner_notes'
345 # SELECT artist.* FROM artist
346 # JOIN ( cd ON artist.id = cd.artist )
347 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
348 # WHERE liner_notes.notes LIKE '%some text%'
350 Joins can be nested to an arbitrary level. So if we decide later that we
351 want to reduce the number of Artists returned based on who wrote the liner
354 # Relationship defined elsewhere:
355 # LinerNotes->belongs_to('author' => 'Person');
357 my $rs = $schema->resultset('Artist')->search(
359 'liner_notes.notes' => { 'like', '%some text%' },
360 'author.name' => 'A. Writer'
365 'liner_notes' => 'author'
372 # SELECT artist.* FROM artist
373 # JOIN ( cd ON artist.id = cd.artist )
374 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
375 # JOIN ( author ON author.id = liner_notes.author )
376 # WHERE liner_notes.notes LIKE '%some text%'
377 # AND author.name = 'A. Writer'
379 =head2 Multi-step prefetch
381 From 0.04999_05 onwards, C<prefetch> can be nested more than one relationship
382 deep using the same syntax as a multi-step join:
384 my $rs = $schema->resultset('Tag')->search(
394 # SELECT tag.*, cd.*, artist.* FROM tag
395 # JOIN cd ON tag.cd = cd.cdid
396 # JOIN artist ON cd.artist = artist.artistid
398 Now accessing our C<cd> and C<artist> relationships does not need additional
401 my $tag = $rs->first;
402 print $tag->cd->artist->name;
404 =head2 Using relationships
406 =head3 Create a new row in a related table
408 my $book->create_related('author', { name => 'Fred'});
410 =head3 Search in a related table
412 Only searches for books named 'Titanic' by the author in $author.
414 my $author->search_related('books', { name => 'Titanic' });
416 =head3 Delete data in a related table
418 Deletes only the book named Titanic by the author in $author.
420 my $author->delete_related('books', { name => 'Titanic' });
424 As of version 0.04001, there is improved transaction support in
425 L<DBIx::Class::Storage::DBI> and L<DBIx::Class::Schema>. Here is an
426 example of the recommended way to use it:
428 my $genus = $schema->resultset('Genus')->find(12);
436 $genus->add_to_species({ name => 'troglodyte' });
439 $schema->txn_do($coderef2); # Can have a nested transaction
440 return $genus->species;
445 $rs = $schema->txn_do($coderef1);
448 if ($@) { # Transaction failed
449 die "the sky is falling!" #
450 if ($@ =~ /Rollback failed/); # Rollback failed
452 deal_with_failed_transaction();
455 Nested transactions will work as expected. That is, only the outermost
456 transaction will actually issue a commit to the $dbh, and a rollback
457 at any level of any transaction will cause the entire nested
458 transaction to fail. Support for savepoints and for true nested
459 transactions (for databases that support them) will hopefully be added
462 =head2 Many-to-many relationships
464 This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:
467 # ... set up connection ...
471 __PACKAGE__->table('user');
472 __PACKAGE__->add_columns(qw/id name/);
473 __PACKAGE__->set_primary_key('id');
474 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
475 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
477 package My::UserAddress;
479 __PACKAGE__->table('user_address');
480 __PACKAGE__->add_columns(qw/user address/);
481 __PACKAGE__->set_primary_key(qw/user address/);
482 __PACKAGE__->belongs_to('user' => 'My::User');
483 __PACKAGE__->belongs_to('address' => 'My::Address');
487 __PACKAGE__->table('address');
488 __PACKAGE__->add_columns(qw/id street town area_code country/);
489 __PACKAGE__->set_primary_key('id');
490 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
491 __PACKAGE__->many_to_many('users' => 'user_address', 'user');
493 $rs = $user->addresses(); # get all addresses for a user
494 $rs = $address->users(); # get all users for an address
496 =head2 Setting default values for a row
498 It's as simple as overriding the C<new> method. Note the use of
502 my ( $class, $attrs ) = @_;
504 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
506 $class->next::method($attrs);
509 For more information about C<next::method>, look in the L<Class::C3>
510 documentation. See also L<DBIx::Class::Manual::Component> for more
511 ways to write your own base classes to do this.
513 People looking for ways to do "triggers" with DBIx::Class are probably
514 just looking for this.
516 =head2 Stringification
518 Employ the standard stringification technique by using the C<overload>
519 module. Replace C<foo> with the column/method of your choice.
521 use overload '""' => 'foo', fallback => 1;
523 =head2 Disconnecting cleanly
525 If you find yourself quitting an app with Control-C a lot during
526 development, you might like to put the following signal handler in
527 your main database class to make sure it disconnects cleanly:
530 __PACKAGE__->storage->disconnect;
533 =head2 Schema import/export
535 This functionality requires you to have L<SQL::Translator> (also known as
536 "SQL Fairy") installed.
538 To create a DBIx::Class schema from an existing database:
541 --to DBIx::Class::File
542 --prefix "MySchema" > MySchema.pm
544 To create a MySQL database from an existing L<DBIx::Class> schema, convert the
545 schema to MySQL's dialect of SQL:
547 sqlt --from DBIx::Class --to MySQL --DBIx::Class "MySchema.pm" > Schema1.sql
549 And import using the mysql client:
551 mysql -h "host" -D "database" -u "user" -p < Schema1.sql
553 =head2 Easy migration from class-based to schema-based setup
555 You want to start using the schema-based approach to L<DBIx::Class>
556 (see L<SchemaIntro.pod>), but have an established class-based setup with lots
557 of existing classes that you don't want to move by hand. Try this nifty script
563 my $schema = MyDB->schema_instance;
565 my $translator = SQL::Translator->new(
566 debug => $debug || 0,
567 trace => $trace || 0,
568 no_comments => $no_comments || 0,
569 show_warnings => $show_warnings || 0,
570 add_drop_table => $add_drop_table || 0,
571 validate => $validate || 0,
573 'DBIx::Schema' => $schema,
576 'prefix' => 'My::Schema',
580 $translator->parser('SQL::Translator::Parser::DBIx::Class');
581 $translator->producer('SQL::Translator::Producer::DBIx::Class::File');
583 my $output = $translator->translate(@args) or die
584 "Error: " . $translator->error;
588 You could use L<Module::Find> to search for all subclasses in the MyDB::*
589 namespace, which is currently left as an exercise for the reader.
591 =head2 Schema versioning
593 The following example shows simplistically how you might use DBIx::Class to
594 deploy versioned schemas to your customers. The basic process is as follows:
600 Create a DBIx::Class schema
612 Modify schema to change functionality
616 Deploy update to customers
620 =head3 Create a DBIx::Class schema
622 This can either be done manually, or generated from an existing database as
623 described under C<Schema import/export>.
625 =head3 Save the schema
627 Use C<sqlt> to transform your schema into an SQL script suitable for your
628 customer's database. E.g. for MySQL:
630 sqlt --from DBIx::Class
632 --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
634 If you need to target databases from multiple vendors, just generate an SQL
635 script suitable for each. To support PostgreSQL too:
637 sqlt --from DBIx::Class
639 --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql
641 =head3 Deploy to customers
643 There are several ways you could deploy your schema. These are probably
644 beyond the scope of this recipe, but might include:
650 Require customer to apply manually using their RDBMS.
654 Package along with your app, making database dump/schema update/tests
655 all part of your install.
659 =head3 Modify the schema to change functionality
661 As your application evolves, it may be necessary to modify your schema to
662 change functionality. Once the changes are made to your schema in DBIx::Class,
663 export the modified schema as before, taking care not to overwrite the original:
665 sqlt --from DBIx::Class
667 --DBIx::Class "Anything.pm" > Schema2.mysql.sql
669 Next, use sqlt-diff to create an SQL script that will update the customer's
672 sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
674 =head3 Deploy update to customers
676 The schema update can be deployed to customers using the same method as before.
678 =head2 Setting limit dialect for SQL::Abstract::Limit
680 In some cases, SQL::Abstract::Limit cannot determine the dialect of the remote
681 SQL-server by looking at the database-handle. This is a common problem when
682 using the DBD::JDBC, since the DBD-driver only know that in has a Java-driver
683 available, not which JDBC-driver the Java component has loaded.
684 This specifically sets the limit_dialect to Microsoft SQL-server (Se more names
685 in SQL::Abstract::Limit -documentation.
687 __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
689 The JDBC-bridge is one way of getting access to a MSSQL-server from a platform
690 that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
692 =head2 Setting quotes for the generated SQL.
694 If the database contains columnames with spaces and/or reserved words, the
695 SQL-query needs to be quoted. This is done using:
697 __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] );
698 __PACKAGE__->storage->sql_maker->name_sep('.');
700 The first sets the quotesymbols. If the quote i "symmetric" as " or '
702 __PACKAGE__->storage->sql_maker->quote_char('"');
704 is enough. If the left quote differs form the right quote, the first
705 notation should be used. name_sep needs to be set to allow the
706 SQL generator to put the quotes the correct place.
708 =head2 Overloading methods
710 L<DBIx::Class> uses the L<Class::C3> package, which provides for redispatch of
711 method calls. You have to use calls to C<next::method> to overload methods.
712 More information on using L<Class::C3> with L<DBIx::Class> can be found in
713 L<DBIx::Class::Manual::Component>.
715 =head3 Changing one field whenever another changes
717 For example, say that you have three columns, C<id>, C<number>, and
718 C<squared>. You would like to make changes to C<number> and have
719 C<squared> be automagically set to the value of C<number> squared.
720 You can accomplish this by overriding C<store_column>:
723 my ( $self, $name, $value ) = @_;
724 if ($name eq 'number') {
725 $self->squared($value * $value);
727 $self->next::method($name, $value);
730 Note that the hard work is done by the call to C<next::method>, which
731 redispatches your call to store_column to the superclass(es).
733 =head3 Automatically creating related objects
735 You might have a class C<Artist> which has many C<CD>s. Further, you
736 want to create a C<CD> object every time you insert an C<Artist> object.
737 You can accomplish this by overriding C<insert> on your objects:
740 my ( $self, @args ) = @_;
741 $self->next::method(@args);
742 $self->cds->new({})->fill_from_artist($self)->insert;
746 where C<fill_from_artist> is a method you specify in C<CD> which sets
747 values in C<CD> based on the data in the C<Artist> object you pass in.
749 =head2 Debugging DBIx::Class objects with Data::Dumper
751 L<Data::Dumper> can be a very useful tool for debugging, but sometimes it can
752 be hard to find the pertinent data in all the data it can generate.
753 Specifically, if one naively tries to use it like so,
757 my $cd = $schema->resultset('CD')->find(1);
760 several pages worth of data from the CD object's schema and result source will
761 be dumped to the screen. Since usually one is only interested in a few column
762 values of the object, this is not very helpful.
764 Luckily, it is possible to modify the data before L<Data::Dumper> outputs
765 it. Simply define a hook that L<Data::Dumper> will call on the object before
766 dumping it. For example,
773 result_source => undef,
781 local $Data::Dumper::Freezer = '_dumper_hook';
783 my $cd = $schema->resultset('CD')->find(1);
785 # dumps $cd without its ResultSource
787 If the structure of your schema is such that there is a common base class for
788 all your table classes, simply put a method similar to C<_dumper_hook> in the
789 base class and set C<$Data::Dumper::Freezer> to its name and L<Data::Dumper>
790 will automagically clean up your data before printing it. See
791 L<Data::Dumper/EXAMPLES> for more information.
793 =head2 Retrieving a row object's Schema
795 It is possible to get a Schema object from a row object like so,
797 my $schema = $cd->result_source->schema;
798 my $artist_rs = $schema->resultset('Artist');
801 This can be useful when you don't want to pass around a Schema object to every
806 When you enable L<DBIx::Class::Storage::DBI>'s debugging it prints the SQL
807 executed as well as notifications of query completion and transaction
808 begin/commit. If you'd like to profile the SQL you can subclass the
809 L<DBIx::Class::Storage::Statistics> class and write your own profiling
812 package My::Profiler;
815 use base 'DBIx::Class::Storage::Statistics';
817 use Time::HiRes qw(time);
826 print "Executing $sql: ".join(', ', @params)."\n";
835 printf("Execution took %0.4f seconds.\n", time() - $start);
841 You can then install that class as the debugging object:
843 __PACKAGE__->storage()->debugobj(new My::Profiler());
844 __PACKAGE__->storage()->debug(1);
846 A more complicated example might involve storing each execution of SQL in an
854 my $elapsed = time() - $start;
855 push(@{ $calls{$sql} }, {
861 You could then create average, high and low execution times for an SQL
862 statement and dig down to see if certain parameters cause aberrant behavior.
864 =head2 Getting the value of the primary key for the last database insert
866 AKA getting last_insert_id
868 If you are using PK::Auto, this is straightforward:
870 my $foo = $rs->create(\%blah);
872 my $id = $foo->id; # foo->my_primary_key_field will also work.
874 If you are not using autoincrementing primary keys, this will probably
875 not work, but then you already know the value of the last primary key anyway.