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 =head2 Using joins and prefetch
174 You can use the C<join> attribute to allow searching on, or sorting your
175 results by, one or more columns in a related table. To return all CDs matching
176 a particular artist name:
178 my $rs = $schema->resultset('CD')->search(
180 'artist.name' => 'Bob Marley'
183 join => [qw/artist/], # join the artist table
188 # SELECT cd.* FROM cd
189 # JOIN artist ON cd.artist = artist.id
190 # WHERE artist.name = 'Bob Marley'
192 If required, you can now sort on any column in the related tables by including
193 it in your C<order_by> attribute:
195 my $rs = $schema->resultset('CD')->search(
197 'artist.name' => 'Bob Marley'
200 join => [qw/ artist /],
201 order_by => [qw/ artist.name /]
206 # SELECT cd.* FROM cd
207 # JOIN artist ON cd.artist = artist.id
208 # WHERE artist.name = 'Bob Marley'
209 # ORDER BY artist.name
211 Note that the C<join> attribute should only be used when you need to search or
212 sort using columns in a related table. Joining related tables when you only
213 need columns from the main table will make performance worse!
215 Now let's say you want to display a list of CDs, each with the name of the
216 artist. The following will work fine:
218 while (my $cd = $rs->next) {
219 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
222 There is a problem however. We have searched both the C<cd> and C<artist> tables
223 in our main query, but we have only returned data from the C<cd> table. To get
224 the artist name for any of the CD objects returned, L<DBIx::Class> will go back
227 SELECT artist.* FROM artist WHERE artist.id = ?
229 A statement like the one above will run for each and every CD returned by our
230 main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
233 Thankfully, L<DBIx::Class> has a C<prefetch> attribute to solve this problem.
234 This allows you to fetch results from related tables in advance:
236 my $rs = $schema->resultset('CD')->search(
238 'artist.name' => 'Bob Marley'
241 join => [qw/ artist /],
242 order_by => [qw/ artist.name /],
243 prefetch => [qw/ artist /] # return artist data too!
247 # Equivalent SQL (note SELECT from both "cd" and "artist"):
248 # SELECT cd.*, artist.* FROM cd
249 # JOIN artist ON cd.artist = artist.id
250 # WHERE artist.name = 'Bob Marley'
251 # ORDER BY artist.name
253 The code to print the CD list remains the same:
255 while (my $cd = $rs->next) {
256 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
259 L<DBIx::Class> has now prefetched all matching data from the C<artist> table,
260 so no additional SQL statements are executed. You now have a much more
263 Note that as of L<DBIx::Class> 0.04, C<prefetch> cannot be used with
264 C<has_many> relationships. You will get an error along the lines of "No
265 accessor for prefetched ..." if you try.
267 Also note that C<prefetch> should only be used when you know you will
268 definitely use data from a related table. Pre-fetching related tables when you
269 only need columns from the main table will make performance worse!
271 =head3 Multi-step joins
273 Sometimes you want to join more than one relationship deep. In this example,
274 we want to find all C<Artist> objects who have C<CD>s whose C<LinerNotes>
275 contain a specific string:
277 # Relationships defined elsewhere:
278 # Artist->has_many('cds' => 'CD', 'artist');
279 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
281 my $rs = $schema->resultset('Artist')->search(
283 'liner_notes.notes' => { 'like', '%some text%' },
287 'cds' => 'liner_notes'
293 # SELECT artist.* FROM artist
294 # JOIN ( cd ON artist.id = cd.artist )
295 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
296 # WHERE liner_notes.notes LIKE '%some text%'
298 Joins can be nested to an arbitrary level. So if we decide later that we
299 want to reduce the number of Artists returned based on who wrote the liner
302 # Relationship defined elsewhere:
303 # LinerNotes->belongs_to('author' => 'Person');
305 my $rs = $schema->resultset('Artist')->search(
307 'liner_notes.notes' => { 'like', '%some text%' },
308 'author.name' => 'A. Writer'
313 'liner_notes' => 'author'
320 # SELECT artist.* FROM artist
321 # JOIN ( cd ON artist.id = cd.artist )
322 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
323 # JOIN ( author ON author.id = liner_notes.author )
324 # WHERE liner_notes.notes LIKE '%some text%'
325 # AND author.name = 'A. Writer'
327 =head2 Multi-step prefetch
329 From 0.04999_05 onwards, C<prefetch> can be nested more than one relationship
330 deep using the same syntax as a multi-step join:
332 my $rs = $schema->resultset('Tag')->search(
342 # SELECT tag.*, cd.*, artist.* FROM tag
343 # JOIN cd ON tag.cd = cd.cdid
344 # JOIN artist ON cd.artist = artist.artistid
346 Now accessing our C<cd> and C<artist> relationships does not need additional
349 my $tag = $rs->first;
350 print $tag->cd->artist->name;
354 As of version 0.04001, there is improved transaction support in
355 L<DBIx::Class::Storage::DBI> and L<DBIx::Class::Schema>. Here is an
356 example of the recommended way to use it:
358 my $genus = $schema->resultset('Genus')->find(12);
361 my ($schema, $genus, $code) = @_;
362 $genus->add_to_species({ name => 'troglodyte' });
365 $schema->txn_do($code, $genus); # Can have a nested transation
366 return $genus->species;
377 $rs = $schema->txn_do($coderef1, $schema, $genus, $coderef2);
380 if ($@) { # Transaction failed
381 die "the sky is falling!" #
382 if ($@ =~ /Rollback failed/); # Rollback failed
384 deal_with_failed_transaction();
387 Nested transactions will work as expected. That is, only the outermost
388 transaction will actually issue a commit to the $dbh, and a rollback
389 at any level of any transaction will cause the entire nested
390 transaction to fail. Support for savepoints and for true nested
391 transactions (for databases that support them) will hopefully be added
394 =head2 Many-to-many relationships
396 This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:
399 # ... set up connection ...
403 __PACKAGE__->table('user');
404 __PACKAGE__->add_columns(qw/id name/);
405 __PACKAGE__->set_primary_key('id');
406 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
407 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
409 package My::UserAddress;
411 __PACKAGE__->table('user_address');
412 __PACKAGE__->add_columns(qw/user address/);
413 __PACKAGE__->set_primary_key(qw/user address/);
414 __PACKAGE__->belongs_to('user' => 'My::User');
415 __PACKAGE__->belongs_to('address' => 'My::Address');
419 __PACKAGE__->table('address');
420 __PACKAGE__->add_columns(qw/id street town area_code country/);
421 __PACKAGE__->set_primary_key('id');
422 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
423 __PACKAGE__->many_to_many('users' => 'user_address', 'user');
425 $rs = $user->addresses(); # get all addresses for a user
426 $rs = $address->users(); # get all users for an address
428 =head2 Setting default values for a row
430 It's as simple as overriding the C<new> method. Note the use of
434 my ( $class, $attrs ) = @_;
436 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
438 $class->next::method($attrs);
441 =head2 Stringification
443 Employ the standard stringification technique by using the C<overload>
444 module. Replace C<foo> with the column/method of your choice.
446 use overload '""' => 'foo', fallback => 1;
448 =head2 Disconnecting cleanly
450 If you find yourself quitting an app with Control-C a lot during
451 development, you might like to put the following signal handler in
452 your main database class to make sure it disconnects cleanly:
455 __PACKAGE__->storage->disconnect;
458 =head2 Schema import/export
460 This functionality requires you to have L<SQL::Translator> (also known as
461 "SQL Fairy") installed.
463 To create a DBIx::Class schema from an existing database:
466 --to DBIx::Class::File
467 --prefix "MySchema" > MySchema.pm
469 To create a MySQL database from an existing L<DBIx::Class> schema, convert the
470 schema to MySQL's dialect of SQL:
472 sqlt --from DBIx::Class --to MySQL --DBIx::Class "MySchema.pm" > Schema1.sql
474 And import using the mysql client:
476 mysql -h "host" -D "database" -u "user" -p < Schema1.sql
478 =head2 Easy migration from class-based to schema-based setup
480 You want to start using the schema-based approach to L<DBIx::Class>
481 (see L<SchemaIntro.pod>), but have an established class-based setup with lots
482 of existing classes that you don't want to move by hand. Try this nifty script
488 my $schema = MyDB->schema_instance;
490 my $translator = SQL::Translator->new(
491 debug => $debug || 0,
492 trace => $trace || 0,
493 no_comments => $no_comments || 0,
494 show_warnings => $show_warnings || 0,
495 add_drop_table => $add_drop_table || 0,
496 validate => $validate || 0,
498 'DBIx::Schema' => $schema,
501 'prefix' => 'My::Schema',
505 $translator->parser('DBIx::Class');
506 $translator->producer('DBIx::Class::File');
508 my $output = $translator->translate(@args) or die
509 "Error: " . $translator->error;
513 You could use L<Module::Find> to search for all subclasses in the MyDB::*
514 namespace, which is currently left as an excercise for the reader.
516 =head2 Schema versioning
518 The following example shows simplistically how you might use DBIx::Class to
519 deploy versioned schemas to your customers. The basic process is as follows:
525 Create a DBIx::Class schema
537 Modify schema to change functionality
541 Deploy update to customers
545 =head3 Create a DBIx::Class schema
547 This can either be done manually, or generated from an existing database as
548 described under C<Schema import/export>.
550 =head3 Save the schema
552 Use C<sqlt> to transform your schema into an SQL script suitable for your
553 customer's database. E.g. for MySQL:
555 sqlt --from DBIx::Class
557 --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
559 If you need to target databases from multiple vendors, just generate an SQL
560 script suitable for each. To support PostgreSQL too:
562 sqlt --from DBIx::Class
564 --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql
566 =head3 Deploy to customers
568 There are several ways you could deploy your schema. These are probably
569 beyond the scope of this recipe, but might include:
575 Require customer to apply manually using their RDBMS.
579 Package along with your app, making database dump/schema update/tests
580 all part of your install.
584 =head3 Modify the schema to change functionality
586 As your application evolves, it may be necessary to modify your schema to
587 change functionality. Once the changes are made to your schema in DBIx::Class,
588 export the modified schema as before, taking care not to overwrite the original:
590 sqlt --from DBIx::Class
592 --DBIx::Class "Anything.pm" > Schema2.mysql.sql
594 Next, use sqlt-diff to create an SQL script that will update the customer's
597 sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
599 =head3 Deploy update to customers
601 The schema update can be deployed to customers using the same method as before.
603 =head2 Setting limit dialect for SQL::Abstract::Limit
605 In some cases, SQL::Abstract::Limit cannot determine the dialect of the remote
606 SQL-server by looking at the database-handle. This is a common problem when
607 using the DBD::JDBC, since the DBD-driver only know that in has a Java-driver
608 available, not which JDBC-driver the Java component has loaded.
609 This specifically sets the limit_dialect to Microsoft SQL-server (Se more names
610 in SQL::Abstract::Limit -documentation.
612 __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
614 The JDBC-bridge is one way of getting access to a MSSQL-server from a platform
615 that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
617 =head2 Setting quotes for the generated SQL.
619 If the database contains columnames with spaces and/or reserved words, the
620 SQL-query needs to be quoted. This is done using:
622 __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] );
623 __PACKAGE__->storage->sql_maker->name_sep('.');
625 The first sets the quotesymbols. If the quote i "symmetric" as " or '
627 __PACKAGE__->storage->sql_maker->quote_char('"');
629 is enough. If the left qoute differs form the right quote, the first
630 notation should be used. name_sep needs to be set to allow the
631 SQL generator to put the quotes the correct place.