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;
406 As of version 0.04001, there is improved transaction support in
407 L<DBIx::Class::Storage::DBI> and L<DBIx::Class::Schema>. Here is an
408 example of the recommended way to use it:
410 my $genus = $schema->resultset('Genus')->find(12);
413 my ($schema, $genus, $code) = @_;
414 $genus->add_to_species({ name => 'troglodyte' });
417 $schema->txn_do($code, $genus); # Can have a nested transaction
418 return $genus->species;
429 $rs = $schema->txn_do($coderef1, $schema, $genus, $coderef2);
432 if ($@) { # Transaction failed
433 die "the sky is falling!" #
434 if ($@ =~ /Rollback failed/); # Rollback failed
436 deal_with_failed_transaction();
439 Nested transactions will work as expected. That is, only the outermost
440 transaction will actually issue a commit to the $dbh, and a rollback
441 at any level of any transaction will cause the entire nested
442 transaction to fail. Support for savepoints and for true nested
443 transactions (for databases that support them) will hopefully be added
446 =head2 Many-to-many relationships
448 This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:
451 # ... set up connection ...
455 __PACKAGE__->table('user');
456 __PACKAGE__->add_columns(qw/id name/);
457 __PACKAGE__->set_primary_key('id');
458 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
459 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
461 package My::UserAddress;
463 __PACKAGE__->table('user_address');
464 __PACKAGE__->add_columns(qw/user address/);
465 __PACKAGE__->set_primary_key(qw/user address/);
466 __PACKAGE__->belongs_to('user' => 'My::User');
467 __PACKAGE__->belongs_to('address' => 'My::Address');
471 __PACKAGE__->table('address');
472 __PACKAGE__->add_columns(qw/id street town area_code country/);
473 __PACKAGE__->set_primary_key('id');
474 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
475 __PACKAGE__->many_to_many('users' => 'user_address', 'user');
477 $rs = $user->addresses(); # get all addresses for a user
478 $rs = $address->users(); # get all users for an address
480 =head2 Setting default values for a row
482 It's as simple as overriding the C<new> method. Note the use of
486 my ( $class, $attrs ) = @_;
488 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
490 $class->next::method($attrs);
493 For more information about C<next::method>, look in the L<Class::C3>
494 documentation. See also L<DBIx::Class::Manual::Component> for more
495 ways to write your own base classes to do this.
497 People looking for ways to do "triggers" with DBIx::Class are probably
498 just looking for this.
500 =head2 Stringification
502 Employ the standard stringification technique by using the C<overload>
503 module. Replace C<foo> with the column/method of your choice.
505 use overload '""' => 'foo', fallback => 1;
507 =head2 Disconnecting cleanly
509 If you find yourself quitting an app with Control-C a lot during
510 development, you might like to put the following signal handler in
511 your main database class to make sure it disconnects cleanly:
514 __PACKAGE__->storage->disconnect;
517 =head2 Schema import/export
519 This functionality requires you to have L<SQL::Translator> (also known as
520 "SQL Fairy") installed.
522 To create a DBIx::Class schema from an existing database:
525 --to DBIx::Class::File
526 --prefix "MySchema" > MySchema.pm
528 To create a MySQL database from an existing L<DBIx::Class> schema, convert the
529 schema to MySQL's dialect of SQL:
531 sqlt --from DBIx::Class --to MySQL --DBIx::Class "MySchema.pm" > Schema1.sql
533 And import using the mysql client:
535 mysql -h "host" -D "database" -u "user" -p < Schema1.sql
537 =head2 Easy migration from class-based to schema-based setup
539 You want to start using the schema-based approach to L<DBIx::Class>
540 (see L<SchemaIntro.pod>), but have an established class-based setup with lots
541 of existing classes that you don't want to move by hand. Try this nifty script
547 my $schema = MyDB->schema_instance;
549 my $translator = SQL::Translator->new(
550 debug => $debug || 0,
551 trace => $trace || 0,
552 no_comments => $no_comments || 0,
553 show_warnings => $show_warnings || 0,
554 add_drop_table => $add_drop_table || 0,
555 validate => $validate || 0,
557 'DBIx::Schema' => $schema,
560 'prefix' => 'My::Schema',
564 $translator->parser('SQL::Translator::Parser::DBIx::Class');
565 $translator->producer('SQL::Translator::Producer::DBIx::Class::File');
567 my $output = $translator->translate(@args) or die
568 "Error: " . $translator->error;
572 You could use L<Module::Find> to search for all subclasses in the MyDB::*
573 namespace, which is currently left as an exercise for the reader.
575 =head2 Schema versioning
577 The following example shows simplistically how you might use DBIx::Class to
578 deploy versioned schemas to your customers. The basic process is as follows:
584 Create a DBIx::Class schema
596 Modify schema to change functionality
600 Deploy update to customers
604 =head3 Create a DBIx::Class schema
606 This can either be done manually, or generated from an existing database as
607 described under C<Schema import/export>.
609 =head3 Save the schema
611 Use C<sqlt> to transform your schema into an SQL script suitable for your
612 customer's database. E.g. for MySQL:
614 sqlt --from DBIx::Class
616 --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
618 If you need to target databases from multiple vendors, just generate an SQL
619 script suitable for each. To support PostgreSQL too:
621 sqlt --from DBIx::Class
623 --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql
625 =head3 Deploy to customers
627 There are several ways you could deploy your schema. These are probably
628 beyond the scope of this recipe, but might include:
634 Require customer to apply manually using their RDBMS.
638 Package along with your app, making database dump/schema update/tests
639 all part of your install.
643 =head3 Modify the schema to change functionality
645 As your application evolves, it may be necessary to modify your schema to
646 change functionality. Once the changes are made to your schema in DBIx::Class,
647 export the modified schema as before, taking care not to overwrite the original:
649 sqlt --from DBIx::Class
651 --DBIx::Class "Anything.pm" > Schema2.mysql.sql
653 Next, use sqlt-diff to create an SQL script that will update the customer's
656 sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
658 =head3 Deploy update to customers
660 The schema update can be deployed to customers using the same method as before.
662 =head2 Setting limit dialect for SQL::Abstract::Limit
664 In some cases, SQL::Abstract::Limit cannot determine the dialect of the remote
665 SQL-server by looking at the database-handle. This is a common problem when
666 using the DBD::JDBC, since the DBD-driver only know that in has a Java-driver
667 available, not which JDBC-driver the Java component has loaded.
668 This specifically sets the limit_dialect to Microsoft SQL-server (Se more names
669 in SQL::Abstract::Limit -documentation.
671 __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
673 The JDBC-bridge is one way of getting access to a MSSQL-server from a platform
674 that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
676 =head2 Setting quotes for the generated SQL.
678 If the database contains columnames with spaces and/or reserved words, the
679 SQL-query needs to be quoted. This is done using:
681 __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] );
682 __PACKAGE__->storage->sql_maker->name_sep('.');
684 The first sets the quotesymbols. If the quote i "symmetric" as " or '
686 __PACKAGE__->storage->sql_maker->quote_char('"');
688 is enough. If the left quote differs form the right quote, the first
689 notation should be used. name_sep needs to be set to allow the
690 SQL generator to put the quotes the correct place.
692 =head2 Overloading methods
694 L<DBIx::Class> uses the L<Class::C3> package, which provides for redispatch of
695 method calls. You have to use calls to C<next::method> to overload methods.
696 More information on using L<Class::C3> with L<DBIx::Class> can be found in
697 L<DBIx::Class::Manual::Component>.
699 =head3 Changing one field whenever another changes
701 For example, say that you have three columns, C<id>, C<number>, and
702 C<squared>. You would like to make changes to C<number> and have
703 C<squared> be automagically set to the value of C<number> squared.
704 You can accomplish this by overriding C<store_column>:
707 my ( $self, $name, $value ) = @_;
708 if ($name eq 'number') {
709 $self->squared($value * $value);
711 $self->next::method($name, $value);
714 Note that the hard work is done by the call to C<next::method>, which
715 redispatches your call to store_column to the superclass(es).
717 =head3 Automatically creating related objects
719 You might have a class C<Artist> which has many C<CD>s. Further, you
720 want to create a C<CD> object every time you insert an C<Artist> object.
721 You can accomplish this by overriding C<insert>:
724 my ( $class, $args_ref ) = @_;
725 my $self = $class->next::method($args_ref);
726 $self->cds->new({})->fill_from_artist($self)->insert;
730 where C<fill_from_artist> is a method you specify in C<CD> which sets
731 values in C<CD> based on the data in the C<Artist> object you pass in.
733 =head2 Debugging DBIx::Class objects with Data::Dumper
735 L<Data::Dumper> can be a very useful tool for debugging, but sometimes it can
736 be hard to find the pertinent data in all the data it can generate.
737 Specifically, if one naively tries to use it like so,
741 my $cd = $schema->resultset('CD')->find(1);
744 several pages worth of data from the CD object's schema and result source will
745 be dumped to the screen. Since usually one is only interested in a few column
746 values of the object, this is not very helpful.
748 Luckily, it is possible to modify the data before L<Data::Dumper> outputs
749 it. Simply define a hook that L<Data::Dumper> will call on the object before
750 dumping it. For example,
757 result_source => undef,
765 $Data::Dumper::Freezer = '_dumper_hook';
767 my $cd = $schema->resultset('CD')->find(1);
769 # dumps $cd without its ResultSource
771 If the structure of your schema is such that there is a common base class for
772 all your table classes, simply put a method similar to C<_dumper_hook> in the
773 base class and set C<$Data::Dumper::Freezer> to its name and L<Data::Dumper>
774 will automagically clean up your data before printing it. See
775 L<Data::Dumper/EXAMPLES> for more information.
777 =head2 Retrieving a row object's Schema
779 It is possible to get a Schema object from a row object like so,
781 my $schema = $cd->result_source->schema;
782 my $artist_rs = $schema->resultset('Artist');
785 This can be useful when you don't want to pass around a Schema object to every
788 =head2 Getting the value of the primary key for the last database insert
790 AKA getting last_insert_id
792 If you are using PK::Auto, this is straightforward:
794 my $foo = $rs->create(\%blah);
796 my $id = $foo->id; # foo->my_primary_key_field will also work.
798 If you are not using autoincrementing primary keys, this will probably
799 not work, but then you already know the value of the last primary key anyway.