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 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>. Here is an example of the recommended
358 my $genus = Genus->find(12);
361 $genus->add_to_species({ name => 'troglodyte' });
364 cromulate($genus); # Can have a nested transation
368 # Rollback might fail, too
374 Currently, a nested commit will do nothing and a nested rollback will
375 die. The code at each level must be sure to call rollback in the case
376 of an error, to ensure that the rollback will propagate to the top
377 level and be issued. Support for savepoints and for true nested
378 transactions (for databases that support them) will hopefully be added
381 =head2 Many-to-many relationships
383 This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:
386 # ... set up connection ...
390 __PACKAGE__->table('user');
391 __PACKAGE__->add_columns(qw/id name/);
392 __PACKAGE__->set_primary_key('id');
393 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
394 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
396 package My::UserAddress;
398 __PACKAGE__->table('user_address');
399 __PACKAGE__->add_columns(qw/user address/);
400 __PACKAGE__->set_primary_key(qw/user address/);
401 __PACKAGE__->belongs_to('user' => 'My::User');
402 __PACKAGE__->belongs_to('address' => 'My::Address');
406 __PACKAGE__->table('address');
407 __PACKAGE__->add_columns(qw/id street town area_code country/);
408 __PACKAGE__->set_primary_key('id');
409 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
410 __PACKAGE__->many_to_many('users' => 'user_address', 'user');
412 $rs = $user->addresses(); # get all addresses for a user
413 $rs = $address->users(); # get all users for an address
415 =head2 Setting default values for a row
417 It's as simple as overriding the C<new> method. Note the use of
421 my ( $class, $attrs ) = @_;
423 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
425 $class->next::method($attrs);
428 =head2 Stringification
430 Employ the standard stringification technique by using the C<overload>
431 module. Replace C<foo> with the column/method of your choice.
433 use overload '""' => 'foo', fallback => 1;
435 =head2 Disconnecting cleanly
437 If you find yourself quitting an app with Control-C a lot during
438 development, you might like to put the following signal handler in
439 your main database class to make sure it disconnects cleanly:
442 __PACKAGE__->storage->disconnect;
445 =head2 Schema import/export
447 This functionality requires you to have L<SQL::Translator> (also known as
448 "SQL Fairy") installed.
450 To create a DBIx::Class schema from an existing database:
453 --to DBIx::Class::File
454 --prefix "MySchema" > MySchema.pm
456 To create a MySQL database from an existing L<DBIx::Class> schema, convert the
457 schema to MySQL's dialect of SQL:
459 sqlt --from DBIx::Class --to MySQL --DBIx::Class "MySchema.pm" > Schema1.sql
461 And import using the mysql client:
463 mysql -h "host" -D "database" -u "user" -p < Schema1.sql
465 =head2 Easy migration from class-based to schema-based setup
467 You want to start using the schema-based approach to L<DBIx::Class>
468 (see L<SchemaIntro.pod>), but have an established class-based setup with lots
469 of existing classes that you don't want to move by hand. Try this nifty script
475 my $schema = MyDB->schema_instance;
477 my $translator = SQL::Translator->new(
478 debug => $debug || 0,
479 trace => $trace || 0,
480 no_comments => $no_comments || 0,
481 show_warnings => $show_warnings || 0,
482 add_drop_table => $add_drop_table || 0,
483 validate => $validate || 0,
485 'DBIx::Schema' => $schema,
488 'prefix' => 'My::Schema',
492 $translator->parser('DBIx::Class');
493 $translator->producer('DBIx::Class::File');
495 my $output = $translator->translate(@args) or die
496 "Error: " . $translator->error;
500 You could use L<Module::Find> to search for all subclasses in the MyDB::*
501 namespace, which is currently left as an excercise for the reader.
503 =head2 Schema versioning
505 The following example shows simplistically how you might use DBIx::Class to
506 deploy versioned schemas to your customers. The basic process is as follows:
512 Create a DBIx::Class schema
524 Modify schema to change functionality
528 Deploy update to customers
532 =head3 Create a DBIx::Class schema
534 This can either be done manually, or generated from an existing database as
535 described under C<Schema import/export>.
537 =head3 Save the schema
539 Use C<sqlt> to transform your schema into an SQL script suitable for your
540 customer's database. E.g. for MySQL:
542 sqlt --from DBIx::Class
544 --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
546 If you need to target databases from multiple vendors, just generate an SQL
547 script suitable for each. To support PostgreSQL too:
549 sqlt --from DBIx::Class
551 --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql
553 =head3 Deploy to customers
555 There are several ways you could deploy your schema. These are probably
556 beyond the scope of this recipe, but might include:
562 Require customer to apply manually using their RDBMS.
566 Package along with your app, making database dump/schema update/tests
567 all part of your install.
571 =head3 Modify the schema to change functionality
573 As your application evolves, it may be necessary to modify your schema to
574 change functionality. Once the changes are made to your schema in DBIx::Class,
575 export the modified schema as before, taking care not to overwrite the original:
577 sqlt --from DBIx::Class
579 --DBIx::Class "Anything.pm" > Schema2.mysql.sql
581 Next, use sqlt-diff to create an SQL script that will update the customer's
584 sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
586 =head3 Deploy update to customers
588 The schema update can be deployed to customers using the same method as before.
590 =head2 Setting limit dialect for SQL::Abstract::Limit
592 In some cases, SQL::Abstract::Limit cannot determine the dialect of the remote
593 SQL-server by looking at the database-handle. This is a common problem when
594 using the DBD::JDBC, since the DBD-driver only know that in has a Java-driver
595 available, not which JDBC-driver the Java component has loaded.
596 This specifically sets the limit_dialect to Microsoft SQL-server (Se more names
597 in SQL::Abstract::Limit -documentation.
599 __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
601 The JDBC-bridge is one way of getting access to a MSSQL-server from a platform
602 that Microsoft doesn't deliver native client libraries for. (e.g. Linux)