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 Using joins and prefetch
164 You can use the "join" attribute to allow searching on, or sorting your
165 results by, one or more columns in a related table. To return
166 all CDs matching a particular artist name:
168 my $rs = $schema->resultset('CD')->search(
170 'artist.name' => 'Bob Marley'
173 join => [qw/artist/], # join the artist table
178 # SELECT cd.* FROM cd
179 # JOIN artist ON cd.artist = artist.id
180 # WHERE artist.name = 'Bob Marley'
182 If required, you can now sort on any column in the related table(s) by
183 including it in your "order_by" attribute:
185 my $rs = $schema->resultset('CD')->search(
187 'artist.name' => 'Bob Marley'
190 join => [qw/ artist /],
191 order_by => [qw/ artist.name /]
196 # SELECT cd.* FROM cd
197 # JOIN artist ON cd.artist = artist.id
198 # WHERE artist.name = 'Bob Marley'
199 # ORDER BY artist.name
201 Note that the "join" attribute should only be used when you need to search or
202 sort using columns in a related table. Joining related tables when you
203 only need columns from the main table will make performance worse!
205 Now let's say you want to display a list of CDs, each with the name of
206 the artist. The following will work fine:
208 while (my $cd = $rs->next) {
209 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
212 There is a problem however. We have searched both cd and artist tables in our
213 main query, but we have only returned data from the cd table. To get the artist
214 name for any of the CD objects returned, DBIx::Class will go back to the
217 SELECT artist.* FROM artist WHERE artist.id = ?
219 A statement like the one above will run for each and every CD returned by our
220 main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
223 Thankfully, DBIx::Class has a "prefetch" attribute to solve this problem. This
224 allows you to fetch results from a related table as well as the main table
227 my $rs = $schema->resultset('CD')->search(
229 'artist.name' => 'Bob Marley'
232 join => [qw/ artist /],
233 order_by => [qw/ artist.name /],
234 prefetch => [qw/ artist /] # return artist data too!
238 # equivalent SQL (note SELECT from both "cd" and "artist")
239 # SELECT cd.*, artist.* FROM cd
240 # JOIN artist ON cd.artist = artist.id
241 # WHERE artist.name = 'Bob Marley'
242 # ORDER BY artist.name
244 The code to print the CD list remains the same:
246 while (my $cd = $rs->next) {
247 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
250 DBIx::Class has now prefetched all matching data from the "artist" table,
251 so no additional SQL statements are executed. You now have a much more
254 Note that as of DBIx::Class 0.04, "prefetch" cannot be used with has_many
255 relationships. You will get an error along the lines of "No accessor for
256 prefetched ..." if you try.
258 Note that "prefetch" should only be used when you know you will
259 definitely use data from a related table. Pre-fetching related tables when you
260 only need columns from the main table will make performance worse!
262 =head2 Multi-step joins
264 Sometimes you want to join more than one relationship deep. In this example,
265 we want to find all Artist objects who have CDs whose LinerNotes contain a
268 # Artist->has_many('cds' => 'CD', 'artist');
269 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
271 $rs = $schema->resultset('Artist')->search(
273 'liner_notes.notes' => { 'like', '%some text%' },
277 'cds' => 'liner_notes'
283 # SELECT artist.* FROM artist
284 # JOIN ( cd ON artist.id = cd.artist )
285 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
286 # WHERE liner_notes.notes LIKE '%some text%'
288 Joins can be nested to an arbitrary level. So if we decide later that we
289 want to reduce the number of Artists returned based on who wrote the liner
292 # LinerNotes->belongs_to('author' => 'Person');
294 $rs = $schema->resultset('Artist')->search(
296 'liner_notes.notes' => { 'like', '%some text%' },
297 'author.name' => 'A. Writer'
302 'liner_notes' => 'author'
309 # SELECT artist.* FROM artist
310 # JOIN ( cd ON artist.id = cd.artist )
311 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
312 # JOIN ( author ON author.id = liner_notes.author )
313 # WHERE liner_notes.notes LIKE '%some text%'
314 # AND author.name = 'A. Writer'
318 As of version 0.04001, there is improved transaction support in
319 L<DBIx::Class::Storage::DBI>. Here is an example of the recommended
322 my $genus = Genus->find(12);
325 $genus->add_to_species({ name => 'troglodyte' });
328 cromulate($genus); # Can have a nested transation
332 # Rollback might fail, too
338 Currently, a nested commit will do nothing and a nested rollback will
339 die. The code at each level must be sure to call rollback in the case
340 of an error, to ensure that the rollback will propagate to the top
341 level and be issued. Support for savepoints and for true nested
342 transactions (for databases that support them) will hopefully be added
345 =head2 Many-to-many relationships
347 This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:
350 # set up connection here...
354 __PACKAGE__->table('user');
355 __PACKAGE__->add_columns(qw/id name/);
356 __PACKAGE__->set_primary_key('id');
357 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
358 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
360 package My::UserAddress;
362 __PACKAGE__->table('user_address');
363 __PACKAGE__->add_columns(qw/user address/);
364 __PACKAGE__->set_primary_key(qw/user address/);
365 __PACKAGE__->belongs_to('user' => 'My::User');
366 __PACKAGE__->belongs_to('address' => 'My::Address');
370 __PACKAGE__->table('address');
371 __PACKAGE__->add_columns(qw/id street town area_code country/);
372 __PACKAGE__->set_primary_key('id');
373 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
374 __PACKAGE__->many_to_many('users' => 'user_address', 'user');
376 $rs = $user->addresses(); # get all addresses for a user
377 $rs = $address->users(); # get all users for an address
379 =head2 Setting default values
381 It's as simple as overriding the C<new> method. Note the use of
385 my ( $class, $attrs ) = @_;
387 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
389 $class->next::method($attrs);
392 =head2 Stringification
394 Employ the standard stringification technique by using the C<overload>
395 module. Replace C<foo> with the column/method of your choice.
397 use overload '""' => 'foo', fallback => 1;