3 DBIx::Class::Manual::Cookbook - Miscellaneous recipes
9 When you expect a large number of results, you can ask DBIx::Class for a paged
10 resultset, which will fetch only a small number of records at a time:
12 $rs = $schema->resultset('Artist')->search(
15 page => 1, # page to return (defaults to 1)
16 rows => 10, # number of results per page
20 $rs->all(); # return all records for page 1
22 The "page" attribute does not have to be specified in your search:
24 $rs = $schema->resultset('Artist')->search(
31 $rs->page(1); # return DBIx::Class::ResultSet containing first 10 records
33 In either of the above cases, you can return a L<Data::Page> object for the
34 resultset (suitable for use in a TT template etc) using the pager() method:
36 $pager = $rs->pager();
38 =head2 Complex searches
40 Sometimes you need to formulate a query using specific operators:
42 my @albums = $schema->resultset('Album')->search({
43 artist => { 'like', '%Lamb%' },
44 title => { 'like', '%Fear of Fours%' },
47 This results in something like the following C<WHERE> clause:
49 WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%'
51 Other queries might require slightly more complex logic:
53 my @albums = $schema->resultset('Album')->search({
56 artist => { 'like', '%Smashing Pumpkins%' },
57 title => 'Siamese Dream',
59 artist => 'Starchildren',
63 This results in the following C<WHERE> clause:
65 WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' )
66 OR artist = 'Starchildren'
68 For more information on generating complex queries, see
69 L<SQL::Abstract/WHERE CLAUSES>.
71 =head2 Disconnecting cleanly
73 If you find yourself quitting an app with Control-C a lot during
74 development, you might like to put the following signal handler in
75 your main database class to make sure it disconnects cleanly:
78 __PACKAGE__->storage->dbh->disconnect;
83 When you only want selected columns from a table, you can use "cols" to
84 specify which ones you need (you could also use "select", but "cols" is the
87 $rs = $schema->resultset('Artist')->search(
95 # SELECT artist.name FROM artist
97 =head2 Using select and as
99 The combination of "select" and "as" is probably most useful when you want to
100 return the result of a function or stored procedure as a column value. You use
101 "select" to specify the source for your column value (e.g. a column name,
102 function or stored procedure name). You then use "as" to set the column name
103 you will use to access the returned value:
105 $rs = $schema->resultset('Artist')->search(
108 select => [ 'name', { LENGTH => 'name' } ],
109 as => [qw/ name name_length /],
114 # SELECT name name, LENGTH( name ) name_length
117 If your alias exists as a column in your base class (i.e. it was added with
118 add_columns()), you just access it as normal. Our Artist class has a "name"
119 column, so we just use the "name" accessor:
121 my $artist = $rs->first();
122 my $name = $artist->name();
124 If on the other hand the alias does not correspond to an existing column, you
125 can get the value using the get_column() accessor:
127 my $name_length = $artist->get_column('name_length');
129 If you don't like using "get_column()", you can always create an accessor for
130 any of your aliases using either of these:
132 # define accessor manually
133 sub name_length { shift->get_column('name_length'); }
135 # or use DBIx::Class::AccessorGroup
136 __PACKAGE__->mk_group_accessors('column' => 'name_length');
138 =head2 SELECT DISTINCT with multiple columns
140 $rs = $schema->resultset('Foo')->search(
144 { distinct => [ $source->columns ] }
146 as => [ $source->columns ]
150 =head2 SELECT COUNT(DISTINCT colname)
152 $rs = $schema->resultset('Foo')->search(
156 { count => { distinct => 'colname' } }
162 =head2 Grouping results
164 DBIx::Class supports GROUP BY as follows:
166 $rs = $schema->resultset('Artist')->search(
170 select => [ 'name', { count => 'cds.cdid' } ],
171 as => [qw/ name cd_count /],
172 group_by => [qw/ name /]
177 # SELECT name, COUNT( cds.cdid ) FROM artist me
178 # LEFT JOIN cd cds ON ( cds.artist = me.artistid )
181 =head2 Using joins and prefetch
183 You can use the "join" attribute to allow searching on, or sorting your
184 results by, one or more columns in a related table. To return
185 all CDs matching a particular artist name:
187 my $rs = $schema->resultset('CD')->search(
189 'artist.name' => 'Bob Marley'
192 join => [qw/artist/], # join the artist table
197 # SELECT cd.* FROM cd
198 # JOIN artist ON cd.artist = artist.id
199 # WHERE artist.name = 'Bob Marley'
201 If required, you can now sort on any column in the related table(s) by
202 including it in your "order_by" attribute:
204 my $rs = $schema->resultset('CD')->search(
206 'artist.name' => 'Bob Marley'
209 join => [qw/ artist /],
210 order_by => [qw/ artist.name /]
215 # SELECT cd.* FROM cd
216 # JOIN artist ON cd.artist = artist.id
217 # WHERE artist.name = 'Bob Marley'
218 # ORDER BY artist.name
220 Note that the "join" attribute should only be used when you need to search or
221 sort using columns in a related table. Joining related tables when you
222 only need columns from the main table will make performance worse!
224 Now let's say you want to display a list of CDs, each with the name of
225 the artist. The following will work fine:
227 while (my $cd = $rs->next) {
228 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
231 There is a problem however. We have searched both cd and artist tables in our
232 main query, but we have only returned data from the cd table. To get the artist
233 name for any of the CD objects returned, DBIx::Class will go back to the
236 SELECT artist.* FROM artist WHERE artist.id = ?
238 A statement like the one above will run for each and every CD returned by our
239 main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
242 Thankfully, DBIx::Class has a "prefetch" attribute to solve this problem. This
243 allows you to fetch results from a related table as well as the main table
246 my $rs = $schema->resultset('CD')->search(
248 'artist.name' => 'Bob Marley'
251 join => [qw/ artist /],
252 order_by => [qw/ artist.name /],
253 prefetch => [qw/ artist /] # return artist data too!
257 # equivalent SQL (note SELECT from both "cd" and "artist")
258 # SELECT cd.*, artist.* FROM cd
259 # JOIN artist ON cd.artist = artist.id
260 # WHERE artist.name = 'Bob Marley'
261 # ORDER BY artist.name
263 The code to print the CD list remains the same:
265 while (my $cd = $rs->next) {
266 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
269 DBIx::Class has now prefetched all matching data from the "artist" table,
270 so no additional SQL statements are executed. You now have a much more
273 Note that as of DBIx::Class 0.04, "prefetch" cannot be used with has_many
274 relationships. You will get an error along the lines of "No accessor for
275 prefetched ..." if you try.
277 Note that "prefetch" should only be used when you know you will
278 definitely use data from a related table. Pre-fetching related tables when you
279 only need columns from the main table will make performance worse!
281 =head2 Multi-step joins
283 Sometimes you want to join more than one relationship deep. In this example,
284 we want to find all Artist objects who have CDs whose LinerNotes contain a
287 # Artist->has_many('cds' => 'CD', 'artist');
288 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
290 $rs = $schema->resultset('Artist')->search(
292 'liner_notes.notes' => { 'like', '%some text%' },
296 'cds' => 'liner_notes'
302 # SELECT artist.* FROM artist
303 # JOIN ( cd ON artist.id = cd.artist )
304 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
305 # WHERE liner_notes.notes LIKE '%some text%'
307 Joins can be nested to an arbitrary level. So if we decide later that we
308 want to reduce the number of Artists returned based on who wrote the liner
311 # LinerNotes->belongs_to('author' => 'Person');
313 $rs = $schema->resultset('Artist')->search(
315 'liner_notes.notes' => { 'like', '%some text%' },
316 'author.name' => 'A. Writer'
321 'liner_notes' => 'author'
328 # SELECT artist.* FROM artist
329 # JOIN ( cd ON artist.id = cd.artist )
330 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
331 # JOIN ( author ON author.id = liner_notes.author )
332 # WHERE liner_notes.notes LIKE '%some text%'
333 # AND author.name = 'A. Writer'
337 As of version 0.04001, there is improved transaction support in
338 L<DBIx::Class::Storage::DBI>. Here is an example of the recommended
341 my $genus = Genus->find(12);
344 $genus->add_to_species({ name => 'troglodyte' });
347 cromulate($genus); # Can have a nested transation
351 # Rollback might fail, too
357 Currently, a nested commit will do nothing and a nested rollback will
358 die. The code at each level must be sure to call rollback in the case
359 of an error, to ensure that the rollback will propagate to the top
360 level and be issued. Support for savepoints and for true nested
361 transactions (for databases that support them) will hopefully be added
364 =head2 Many-to-many relationships
366 This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:
369 # set up connection here...
373 __PACKAGE__->table('user');
374 __PACKAGE__->add_columns(qw/id name/);
375 __PACKAGE__->set_primary_key('id');
376 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
377 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
379 package My::UserAddress;
381 __PACKAGE__->table('user_address');
382 __PACKAGE__->add_columns(qw/user address/);
383 __PACKAGE__->set_primary_key(qw/user address/);
384 __PACKAGE__->belongs_to('user' => 'My::User');
385 __PACKAGE__->belongs_to('address' => 'My::Address');
389 __PACKAGE__->table('address');
390 __PACKAGE__->add_columns(qw/id street town area_code country/);
391 __PACKAGE__->set_primary_key('id');
392 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
393 __PACKAGE__->many_to_many('users' => 'user_address', 'user');
395 $rs = $user->addresses(); # get all addresses for a user
396 $rs = $address->users(); # get all users for an address
398 =head2 Setting default values
400 It's as simple as overriding the C<new> method. Note the use of
404 my ( $class, $attrs ) = @_;
406 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
408 $class->next::method($attrs);
411 =head2 Stringification
413 Employ the standard stringification technique by using the C<overload>
414 module. Replace C<foo> with the column/method of your choice.
416 use overload '""' => 'foo', fallback => 1;