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(
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 a related table as well as the main table
237 my $rs = $schema->resultset('CD')->search(
239 'artist.name' => 'Bob Marley'
242 join => [qw/ artist /],
243 order_by => [qw/ artist.name /],
244 prefetch => [qw/ artist /] # return artist data too!
248 # Equivalent SQL (note SELECT from both "cd" and "artist"):
249 # SELECT cd.*, artist.* FROM cd
250 # JOIN artist ON cd.artist = artist.id
251 # WHERE artist.name = 'Bob Marley'
252 # ORDER BY artist.name
254 The code to print the CD list remains the same:
256 while (my $cd = $rs->next) {
257 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
260 L<DBIx::Class> has now prefetched all matching data from the C<artist> table,
261 so no additional SQL statements are executed. You now have a much more
264 Note that as of L<DBIx::Class> 0.04, C<prefetch> cannot be used with
265 C<has_many> relationships. You will get an error along the lines of "No
266 accessor for prefetched ..." if you try.
268 Also note that C<prefetch> should only be used when you know you will
269 definitely use data from a related table. Pre-fetching related tables when you
270 only need columns from the main table will make performance worse!
272 =head3 Multi-step joins
274 Sometimes you want to join more than one relationship deep. In this example,
275 we want to find all C<Artist> objects who have C<CD>s whose C<LinerNotes>
276 contain a specific string:
278 # Relationships defined elsewhere:
279 # Artist->has_many('cds' => 'CD', 'artist');
280 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
282 my $rs = $schema->resultset('Artist')->search(
284 'liner_notes.notes' => { 'like', '%some text%' },
288 'cds' => 'liner_notes'
294 # SELECT artist.* FROM artist
295 # JOIN ( cd ON artist.id = cd.artist )
296 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
297 # WHERE liner_notes.notes LIKE '%some text%'
299 Joins can be nested to an arbitrary level. So if we decide later that we
300 want to reduce the number of Artists returned based on who wrote the liner
303 # Relationship defined elsewhere:
304 # LinerNotes->belongs_to('author' => 'Person');
306 my $rs = $schema->resultset('Artist')->search(
308 'liner_notes.notes' => { 'like', '%some text%' },
309 'author.name' => 'A. Writer'
314 'liner_notes' => 'author'
321 # SELECT artist.* FROM artist
322 # JOIN ( cd ON artist.id = cd.artist )
323 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
324 # JOIN ( author ON author.id = liner_notes.author )
325 # WHERE liner_notes.notes LIKE '%some text%'
326 # AND author.name = 'A. Writer'
330 As of version 0.04001, there is improved transaction support in
331 L<DBIx::Class::Storage::DBI>. Here is an example of the recommended
334 my $genus = Genus->find(12);
337 $genus->add_to_species({ name => 'troglodyte' });
340 cromulate($genus); # Can have a nested transation
344 # Rollback might fail, too
350 Currently, a nested commit will do nothing and a nested rollback will
351 die. The code at each level must be sure to call rollback in the case
352 of an error, to ensure that the rollback will propagate to the top
353 level and be issued. Support for savepoints and for true nested
354 transactions (for databases that support them) will hopefully be added
357 =head2 Many-to-many relationships
359 This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:
362 # ... set up connection ...
366 __PACKAGE__->table('user');
367 __PACKAGE__->add_columns(qw/id name/);
368 __PACKAGE__->set_primary_key('id');
369 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
370 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
372 package My::UserAddress;
374 __PACKAGE__->table('user_address');
375 __PACKAGE__->add_columns(qw/user address/);
376 __PACKAGE__->set_primary_key(qw/user address/);
377 __PACKAGE__->belongs_to('user' => 'My::User');
378 __PACKAGE__->belongs_to('address' => 'My::Address');
382 __PACKAGE__->table('address');
383 __PACKAGE__->add_columns(qw/id street town area_code country/);
384 __PACKAGE__->set_primary_key('id');
385 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
386 __PACKAGE__->many_to_many('users' => 'user_address', 'user');
388 $rs = $user->addresses(); # get all addresses for a user
389 $rs = $address->users(); # get all users for an address
391 =head2 Setting default values for a row
393 It's as simple as overriding the C<new> method. Note the use of
397 my ( $class, $attrs ) = @_;
399 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
401 $class->next::method($attrs);
404 =head2 Stringification
406 Employ the standard stringification technique by using the C<overload>
407 module. Replace C<foo> with the column/method of your choice.
409 use overload '""' => 'foo', fallback => 1;
411 =head2 Disconnecting cleanly
413 If you find yourself quitting an app with Control-C a lot during
414 development, you might like to put the following signal handler in
415 your main database class to make sure it disconnects cleanly:
418 __PACKAGE__->storage->dbh->disconnect;
421 =head2 Schema import/export
423 This functionality requires you to have L<SQL::Translator> (also known as
424 "SQL Fairy") installed.
426 To create a DBIx::Class schema from an existing database:
429 --to DBIx::Class::File
430 --prefix "MySchema" > MySchema.pm
432 To create a MySQL database from an existing L<DBIx::Class> schema, convert the
433 schema to MySQL's dialect of SQL:
435 sqlt --from DBIx::Class --to MySQL --DBIx::Class "MySchema.pm" > Schema1.sql
437 And import using the mysql client:
439 mysql -h "host" -D "database" -u "user" -p < Schema1.sql
441 =head2 Easy migration from class-based to schema-based setup
443 You want to start using the schema-based approach to L<DBIx::Class>
444 (see L<SchemaIntro.pod>), but have an established class-based setup with lots
445 of existing classes that you don't want to move by hand. Try this nifty script
451 my $schema = MyDB->schema_instance;
453 my $translator = SQL::Translator->new(
454 debug => $debug || 0,
455 trace => $trace || 0,
456 no_comments => $no_comments || 0,
457 show_warnings => $show_warnings || 0,
458 add_drop_table => $add_drop_table || 0,
459 validate => $validate || 0,
461 'DBIx::Schema' => $schema,
464 'prefix' => 'My::Schema',
468 $translator->parser('DBIx::Class');
469 $translator->producer('DBIx::Class::File');
471 my $output = $translator->translate(@args) or die
472 "Error: " . $translator->error;
476 You could use L<Module::Find> to search for all subclasses in the MyDB::*
477 namespace, which is currently left as an excercise for the reader.
479 =head2 Schema versioning
481 The following example shows simplistically how you might use DBIx::Class to
482 deploy versioned schemas to your customers. The basic process is as follows:
484 1) Create a DBIx::Class schema
486 3) Deploy to customers
487 4) Modify schema to change functionality
488 5) Deploy update to customers
490 =head3 Create a DBIx::Class schema
492 This can either be done manually, or generated from an existing database as
493 described under C<Schema import/export>.
495 =head3 Save the schema
497 Use C<sqlt> to transform your schema into an SQL script suitable for your
498 customer's database. E.g. for MySQL:
500 sqlt --from DBIx::Class
502 --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
504 If you need to target databases from multiple vendors, just generate an SQL
505 script suitable for each. To support PostgreSQL too:
507 sqlt --from DBIx::Class
509 --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql
511 =head3 Deploy to customers
513 There are several ways you could deploy your schema. These are probably
514 beyond the scope of this recipe, but might include:
516 1) Require customer to apply manually using their RDBMS.
517 2) Package along with your app, making database dump/schema update/tests
518 all part of your install.
520 =head3 Modify the schema to change functionality
522 As your application evolves, it may be necessary to modify your schema to
523 change functionality. Once the changes are made to your schema in DBIx::Class,
524 export the modified schema as before, taking care not to overwrite the original:
526 sqlt --from DBIx::Class
528 --DBIx::Class "Anything.pm" > Schema2.mysql.sql
530 Next, use sqlt-diff to create an SQL script that will update the customer's
533 sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
535 =head3 Deploy update to customers
537 The schema update can be deployed to customers using the same method as before.