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 =head3 SELECT COUNT(DISTINCT colname)
143 my $rs = $schema->resultset('Foo')->search(
147 { count => { distinct => 'colname' } }
153 =head3 Grouping results
155 L<DBIx::Class> supports C<GROUP BY> as follows:
157 my $rs = $schema->resultset('Artist')->search(
161 select => [ 'name', { count => 'cds.cdid' } ],
162 as => [qw/ name cd_count /],
163 group_by => [qw/ name /]
168 # SELECT name, COUNT( cds.cdid ) FROM artist me
169 # LEFT JOIN cd cds ON ( cds.artist = me.artistid )
172 =head3 Predefined searches
174 You can write your own DBIx::Class::ResultSet class by inheriting from it
175 and define often used searches as methods:
177 package My::DBIC::ResultSet::CD;
180 use base 'DBIx::Class::ResultSet';
182 sub search_cds_ordered {
185 return $self->search(
187 { order_by => 'name DESC' },
193 To use your resultset, first tell DBIx::Class to create an instance of it
194 for you, in your My::DBIC::Schema::CD class:
196 __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD');
198 Then call your new method in your code:
200 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
203 =head3 Predefined searches without writing a ResultSet class
205 Alternatively you can automatically generate a DBIx::Class::ResultSet
206 class by using the ResultSetManager component and tagging your method
209 __PACKAGE__->load_components(qw/ ResultSetManager Core /);
211 sub search_cds_ordered : ResultSet {
213 return $self->search(
215 { order_by => 'name DESC' },
219 Then call your method in the same way from your code:
221 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
223 =head2 Using joins and prefetch
225 You can use the C<join> attribute to allow searching on, or sorting your
226 results by, one or more columns in a related table. To return all CDs matching
227 a particular artist name:
229 my $rs = $schema->resultset('CD')->search(
231 'artist.name' => 'Bob Marley'
234 join => [qw/artist/], # join the artist table
239 # SELECT cd.* FROM cd
240 # JOIN artist ON cd.artist = artist.id
241 # WHERE artist.name = 'Bob Marley'
243 If required, you can now sort on any column in the related tables by including
244 it in your C<order_by> attribute:
246 my $rs = $schema->resultset('CD')->search(
248 'artist.name' => 'Bob Marley'
251 join => [qw/ artist /],
252 order_by => [qw/ artist.name /]
257 # SELECT cd.* FROM cd
258 # JOIN artist ON cd.artist = artist.id
259 # WHERE artist.name = 'Bob Marley'
260 # ORDER BY artist.name
262 Note that the C<join> attribute should only be used when you need to search or
263 sort using columns in a related table. Joining related tables when you only
264 need columns from the main table will make performance worse!
266 Now let's say you want to display a list of CDs, each with the name of the
267 artist. The following will work fine:
269 while (my $cd = $rs->next) {
270 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
273 There is a problem however. We have searched both the C<cd> and C<artist> tables
274 in our main query, but we have only returned data from the C<cd> table. To get
275 the artist name for any of the CD objects returned, L<DBIx::Class> will go back
278 SELECT artist.* FROM artist WHERE artist.id = ?
280 A statement like the one above will run for each and every CD returned by our
281 main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
284 Thankfully, L<DBIx::Class> has a C<prefetch> attribute to solve this problem.
285 This allows you to fetch results from related tables in advance:
287 my $rs = $schema->resultset('CD')->search(
289 'artist.name' => 'Bob Marley'
292 join => [qw/ artist /],
293 order_by => [qw/ artist.name /],
294 prefetch => [qw/ artist /] # return artist data too!
298 # Equivalent SQL (note SELECT from both "cd" and "artist"):
299 # SELECT cd.*, artist.* FROM cd
300 # JOIN artist ON cd.artist = artist.id
301 # WHERE artist.name = 'Bob Marley'
302 # ORDER BY artist.name
304 The code to print the CD list remains the same:
306 while (my $cd = $rs->next) {
307 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
310 L<DBIx::Class> has now prefetched all matching data from the C<artist> table,
311 so no additional SQL statements are executed. You now have a much more
314 Note that as of L<DBIx::Class> 0.04, C<prefetch> cannot be used with
315 C<has_many> relationships. You will get an error along the lines of "No
316 accessor for prefetched ..." if you try.
318 Also note that C<prefetch> should only be used when you know you will
319 definitely use data from a related table. Pre-fetching related tables when you
320 only need columns from the main table will make performance worse!
322 =head3 Multi-step joins
324 Sometimes you want to join more than one relationship deep. In this example,
325 we want to find all C<Artist> objects who have C<CD>s whose C<LinerNotes>
326 contain a specific string:
328 # Relationships defined elsewhere:
329 # Artist->has_many('cds' => 'CD', 'artist');
330 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
332 my $rs = $schema->resultset('Artist')->search(
334 'liner_notes.notes' => { 'like', '%some text%' },
338 'cds' => 'liner_notes'
344 # SELECT artist.* FROM artist
345 # JOIN ( cd ON artist.id = cd.artist )
346 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
347 # WHERE liner_notes.notes LIKE '%some text%'
349 Joins can be nested to an arbitrary level. So if we decide later that we
350 want to reduce the number of Artists returned based on who wrote the liner
353 # Relationship defined elsewhere:
354 # LinerNotes->belongs_to('author' => 'Person');
356 my $rs = $schema->resultset('Artist')->search(
358 'liner_notes.notes' => { 'like', '%some text%' },
359 'author.name' => 'A. Writer'
364 'liner_notes' => 'author'
371 # SELECT artist.* FROM artist
372 # JOIN ( cd ON artist.id = cd.artist )
373 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
374 # JOIN ( author ON author.id = liner_notes.author )
375 # WHERE liner_notes.notes LIKE '%some text%'
376 # AND author.name = 'A. Writer'
378 =head2 Multi-step prefetch
380 From 0.04999_05 onwards, C<prefetch> can be nested more than one relationship
381 deep using the same syntax as a multi-step join:
383 my $rs = $schema->resultset('Tag')->search(
393 # SELECT tag.*, cd.*, artist.* FROM tag
394 # JOIN cd ON tag.cd = cd.cdid
395 # JOIN artist ON cd.artist = artist.artistid
397 Now accessing our C<cd> and C<artist> relationships does not need additional
400 my $tag = $rs->first;
401 print $tag->cd->artist->name;
405 As of version 0.04001, there is improved transaction support in
406 L<DBIx::Class::Storage::DBI> and L<DBIx::Class::Schema>. Here is an
407 example of the recommended way to use it:
409 my $genus = $schema->resultset('Genus')->find(12);
412 my ($schema, $genus, $code) = @_;
413 $genus->add_to_species({ name => 'troglodyte' });
416 $schema->txn_do($code, $genus); # Can have a nested transaction
417 return $genus->species;
428 $rs = $schema->txn_do($coderef1, $schema, $genus, $coderef2);
431 if ($@) { # Transaction failed
432 die "the sky is falling!" #
433 if ($@ =~ /Rollback failed/); # Rollback failed
435 deal_with_failed_transaction();
438 Nested transactions will work as expected. That is, only the outermost
439 transaction will actually issue a commit to the $dbh, and a rollback
440 at any level of any transaction will cause the entire nested
441 transaction to fail. Support for savepoints and for true nested
442 transactions (for databases that support them) will hopefully be added
445 =head2 Many-to-many relationships
447 This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:
450 # ... set up connection ...
454 __PACKAGE__->table('user');
455 __PACKAGE__->add_columns(qw/id name/);
456 __PACKAGE__->set_primary_key('id');
457 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
458 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
460 package My::UserAddress;
462 __PACKAGE__->table('user_address');
463 __PACKAGE__->add_columns(qw/user address/);
464 __PACKAGE__->set_primary_key(qw/user address/);
465 __PACKAGE__->belongs_to('user' => 'My::User');
466 __PACKAGE__->belongs_to('address' => 'My::Address');
470 __PACKAGE__->table('address');
471 __PACKAGE__->add_columns(qw/id street town area_code country/);
472 __PACKAGE__->set_primary_key('id');
473 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
474 __PACKAGE__->many_to_many('users' => 'user_address', 'user');
476 $rs = $user->addresses(); # get all addresses for a user
477 $rs = $address->users(); # get all users for an address
479 =head2 Setting default values for a row
481 It's as simple as overriding the C<new> method. Note the use of
485 my ( $class, $attrs ) = @_;
487 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
489 $class->next::method($attrs);
492 For more information about C<next::method>, look in the L<Class::C3>
493 documentation. See also L<DBIx::Class::Manual::Component> for more
494 ways to write your own base classes to do this.
496 People looking for ways to do "triggers" with DBIx::Class are probably
497 just looking for this.
499 =head2 Stringification
501 Employ the standard stringification technique by using the C<overload>
502 module. Replace C<foo> with the column/method of your choice.
504 use overload '""' => 'foo', fallback => 1;
506 =head2 Disconnecting cleanly
508 If you find yourself quitting an app with Control-C a lot during
509 development, you might like to put the following signal handler in
510 your main database class to make sure it disconnects cleanly:
513 __PACKAGE__->storage->disconnect;
516 =head2 Schema import/export
518 This functionality requires you to have L<SQL::Translator> (also known as
519 "SQL Fairy") installed.
521 To create a DBIx::Class schema from an existing database:
524 --to DBIx::Class::File
525 --prefix "MySchema" > MySchema.pm
527 To create a MySQL database from an existing L<DBIx::Class> schema, convert the
528 schema to MySQL's dialect of SQL:
530 sqlt --from DBIx::Class --to MySQL --DBIx::Class "MySchema.pm" > Schema1.sql
532 And import using the mysql client:
534 mysql -h "host" -D "database" -u "user" -p < Schema1.sql
536 =head2 Easy migration from class-based to schema-based setup
538 You want to start using the schema-based approach to L<DBIx::Class>
539 (see L<SchemaIntro.pod>), but have an established class-based setup with lots
540 of existing classes that you don't want to move by hand. Try this nifty script
546 my $schema = MyDB->schema_instance;
548 my $translator = SQL::Translator->new(
549 debug => $debug || 0,
550 trace => $trace || 0,
551 no_comments => $no_comments || 0,
552 show_warnings => $show_warnings || 0,
553 add_drop_table => $add_drop_table || 0,
554 validate => $validate || 0,
556 'DBIx::Schema' => $schema,
559 'prefix' => 'My::Schema',
563 $translator->parser('DBIx::Class');
564 $translator->producer('DBIx::Class::File');
566 my $output = $translator->translate(@args) or die
567 "Error: " . $translator->error;
571 You could use L<Module::Find> to search for all subclasses in the MyDB::*
572 namespace, which is currently left as an exercise for the reader.
574 =head2 Schema versioning
576 The following example shows simplistically how you might use DBIx::Class to
577 deploy versioned schemas to your customers. The basic process is as follows:
583 Create a DBIx::Class schema
595 Modify schema to change functionality
599 Deploy update to customers
603 =head3 Create a DBIx::Class schema
605 This can either be done manually, or generated from an existing database as
606 described under C<Schema import/export>.
608 =head3 Save the schema
610 Use C<sqlt> to transform your schema into an SQL script suitable for your
611 customer's database. E.g. for MySQL:
613 sqlt --from DBIx::Class
615 --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
617 If you need to target databases from multiple vendors, just generate an SQL
618 script suitable for each. To support PostgreSQL too:
620 sqlt --from DBIx::Class
622 --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql
624 =head3 Deploy to customers
626 There are several ways you could deploy your schema. These are probably
627 beyond the scope of this recipe, but might include:
633 Require customer to apply manually using their RDBMS.
637 Package along with your app, making database dump/schema update/tests
638 all part of your install.
642 =head3 Modify the schema to change functionality
644 As your application evolves, it may be necessary to modify your schema to
645 change functionality. Once the changes are made to your schema in DBIx::Class,
646 export the modified schema as before, taking care not to overwrite the original:
648 sqlt --from DBIx::Class
650 --DBIx::Class "Anything.pm" > Schema2.mysql.sql
652 Next, use sqlt-diff to create an SQL script that will update the customer's
655 sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
657 =head3 Deploy update to customers
659 The schema update can be deployed to customers using the same method as before.
661 =head2 Setting limit dialect for SQL::Abstract::Limit
663 In some cases, SQL::Abstract::Limit cannot determine the dialect of the remote
664 SQL-server by looking at the database-handle. This is a common problem when
665 using the DBD::JDBC, since the DBD-driver only know that in has a Java-driver
666 available, not which JDBC-driver the Java component has loaded.
667 This specifically sets the limit_dialect to Microsoft SQL-server (Se more names
668 in SQL::Abstract::Limit -documentation.
670 __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
672 The JDBC-bridge is one way of getting access to a MSSQL-server from a platform
673 that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
675 =head2 Setting quotes for the generated SQL.
677 If the database contains columnames with spaces and/or reserved words, the
678 SQL-query needs to be quoted. This is done using:
680 __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] );
681 __PACKAGE__->storage->sql_maker->name_sep('.');
683 The first sets the quotesymbols. If the quote i "symmetric" as " or '
685 __PACKAGE__->storage->sql_maker->quote_char('"');
687 is enough. If the left quote differs form the right quote, the first
688 notation should be used. name_sep needs to be set to allow the
689 SQL generator to put the quotes the correct place.