Commit | Line | Data |
3b44ccc6 |
1 | =head1 NAME |
9c82c181 |
2 | |
40dbc108 |
3 | DBIx::Class::Manual::Cookbook - Miscellaneous recipes |
ee38fa40 |
4 | |
40dbc108 |
5 | =head1 RECIPES |
2913b2d3 |
6 | |
faf62551 |
7 | =head2 Paged results |
8 | |
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: |
11 | |
12 | $rs = $schema->resultset('Artist')->search( |
13 | {}, |
14 | { |
15 | page => 1, # page to return (defaults to 1) |
16 | rows => 10, # number of results per page |
17 | }, |
18 | ); |
19 | |
20 | $rs->all(); # return all records for page 1 |
21 | |
22 | The "page" attribute does not have to be specified in your search: |
23 | |
24 | $rs = $schema->resultset('Artist')->search( |
25 | {}, |
26 | { |
27 | rows => 10, |
28 | } |
29 | ); |
30 | |
31 | $rs->page(1); # return DBIx::Class::ResultSet containing first 10 records |
32 | |
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: |
35 | |
36 | $pager = $rs->pager(); |
37 | |
40dbc108 |
38 | =head2 Complex searches |
2913b2d3 |
39 | |
40dbc108 |
40 | Sometimes you need to formulate a query using specific operators: |
41 | |
ea6309e2 |
42 | my @albums = $schema->resultset('Album')->search({ |
35d4fe78 |
43 | artist => { 'like', '%Lamb%' }, |
44 | title => { 'like', '%Fear of Fours%' }, |
45 | }); |
40dbc108 |
46 | |
47 | This results in something like the following C<WHERE> clause: |
48 | |
35d4fe78 |
49 | WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%' |
40dbc108 |
50 | |
51 | Other queries might require slightly more complex logic: |
52 | |
ea6309e2 |
53 | my @albums = $schema->resultset('Album')->search({ |
35d4fe78 |
54 | -or => [ |
55 | -and => [ |
56 | artist => { 'like', '%Smashing Pumpkins%' }, |
57 | title => 'Siamese Dream', |
58 | ], |
59 | artist => 'Starchildren', |
60 | ], |
61 | }); |
40dbc108 |
62 | |
63 | This results in the following C<WHERE> clause: |
64 | |
35d4fe78 |
65 | WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' ) |
66 | OR artist = 'Starchildren' |
40dbc108 |
67 | |
68 | For more information on generating complex queries, see |
69 | L<SQL::Abstract/WHERE CLAUSES>. |
ee38fa40 |
70 | |
87980de7 |
71 | =head2 Disconnecting cleanly |
ee38fa40 |
72 | |
40dbc108 |
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: |
87980de7 |
76 | |
35d4fe78 |
77 | $SIG{INT} = sub { |
78 | __PACKAGE__->storage->dbh->disconnect; |
79 | }; |
87980de7 |
80 | |
faf62551 |
81 | =head2 Using cols |
82 | |
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 |
85 | recommended way): |
86 | |
87 | $rs = $schema->resultset('Artist')->search( |
88 | {}, |
89 | { |
90 | cols => [qw/ name /] |
91 | } |
92 | ); |
93 | |
94 | # e.g. |
95 | # SELECT artist.name FROM artist |
96 | |
97 | =head2 Using select and as |
98 | |
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: |
104 | |
105 | $rs = $schema->resultset('Artist')->search( |
106 | {}, |
107 | { |
108 | select => [ 'name', { LENGTH => 'name' } ], |
109 | as => [qw/ name name_length /], |
110 | } |
111 | ); |
112 | |
113 | # e.g. |
114 | # SELECT name name, LENGTH( name ) name_length |
115 | # FROM artist |
116 | |
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: |
120 | |
121 | my $artist = $rs->first(); |
122 | my $name = $artist->name(); |
123 | |
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: |
126 | |
127 | my $name_length = $artist->get_column('name_length'); |
128 | |
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: |
131 | |
132 | # define accessor manually |
133 | sub name_length { shift->get_column('name_length'); } |
134 | |
135 | # or use DBIx::Class::AccessorGroup |
136 | __PACKAGE__->mk_group_accessors('column' => 'name_length'); |
137 | |
138 | =head2 SELECT DISTINCT with multiple columns |
139 | |
140 | $rs = $schema->resultset('Foo')->search( |
141 | {}, |
142 | { |
143 | select => [ |
144 | { distinct => [ $source->columns ] } |
145 | ], |
146 | as => [ $source->columns ] |
147 | } |
148 | ); |
149 | |
150 | =head2 SELECT COUNT(DISTINCT colname) |
151 | |
152 | $rs = $schema->resultset('Foo')->search( |
153 | {}, |
154 | { |
155 | select => [ |
156 | { count => { distinct => 'colname' } } |
157 | ], |
158 | as => [ 'count' ] |
159 | } |
160 | ); |
161 | |
6607ee1b |
162 | =head2 Grouping results |
163 | |
164 | DBIx::Class supports GROUP BY as follows: |
165 | |
166 | $rs = $schema->resultset('Artist')->search( |
167 | {}, |
168 | { |
169 | join => [qw/ cds /], |
170 | select => [ 'name', { count => 'cds.cdid' } ], |
171 | as => [qw/ name cd_count /], |
172 | group_by => [qw/ name /] |
173 | } |
174 | ); |
175 | |
176 | # e.g. |
177 | # SELECT name, COUNT( cds.cdid ) FROM artist me |
178 | # LEFT JOIN cd cds ON ( cds.artist = me.artistid ) |
179 | # GROUP BY name |
180 | |
87980de7 |
181 | =head2 Using joins and prefetch |
182 | |
ea6309e2 |
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: |
186 | |
187 | my $rs = $schema->resultset('CD')->search( |
188 | { |
189 | 'artist.name' => 'Bob Marley' |
190 | }, |
191 | { |
192 | join => [qw/artist/], # join the artist table |
193 | } |
194 | ); |
195 | |
196 | # equivalent SQL: |
197 | # SELECT cd.* FROM cd |
198 | # JOIN artist ON cd.artist = artist.id |
199 | # WHERE artist.name = 'Bob Marley' |
200 | |
201 | If required, you can now sort on any column in the related table(s) by |
202 | including it in your "order_by" attribute: |
203 | |
204 | my $rs = $schema->resultset('CD')->search( |
205 | { |
206 | 'artist.name' => 'Bob Marley' |
207 | }, |
208 | { |
209 | join => [qw/ artist /], |
210 | order_by => [qw/ artist.name /] |
211 | } |
212 | }; |
213 | |
214 | # equivalent SQL: |
215 | # SELECT cd.* FROM cd |
216 | # JOIN artist ON cd.artist = artist.id |
217 | # WHERE artist.name = 'Bob Marley' |
218 | # ORDER BY artist.name |
219 | |
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! |
223 | |
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: |
226 | |
227 | while (my $cd = $rs->next) { |
228 | print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; |
229 | } |
230 | |
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 |
234 | database: |
235 | |
236 | SELECT artist.* FROM artist WHERE artist.id = ? |
237 | |
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 |
240 | queries! |
241 | |
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 |
244 | for your class: |
245 | |
246 | my $rs = $schema->resultset('CD')->search( |
247 | { |
248 | 'artist.name' => 'Bob Marley' |
249 | }, |
250 | { |
251 | join => [qw/ artist /], |
252 | order_by => [qw/ artist.name /], |
253 | prefetch => [qw/ artist /] # return artist data too! |
254 | } |
255 | ); |
256 | |
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 |
262 | |
263 | The code to print the CD list remains the same: |
264 | |
265 | while (my $cd = $rs->next) { |
266 | print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; |
267 | } |
268 | |
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 |
271 | efficient query. |
272 | |
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. |
276 | |
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! |
280 | |
281 | =head2 Multi-step joins |
282 | |
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 |
285 | specific string: |
286 | |
287 | # Artist->has_many('cds' => 'CD', 'artist'); |
288 | # CD->has_one('liner_notes' => 'LinerNotes', 'cd'); |
289 | |
290 | $rs = $schema->resultset('Artist')->search( |
291 | { |
292 | 'liner_notes.notes' => { 'like', '%some text%' }, |
293 | }, |
294 | { |
295 | join => { |
296 | 'cds' => 'liner_notes' |
297 | } |
298 | } |
299 | ); |
300 | |
301 | # equivalent SQL |
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%' |
306 | |
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 |
309 | notes: |
310 | |
faf62551 |
311 | # LinerNotes->belongs_to('author' => 'Person'); |
ea6309e2 |
312 | |
313 | $rs = $schema->resultset('Artist')->search( |
314 | { |
315 | 'liner_notes.notes' => { 'like', '%some text%' }, |
316 | 'author.name' => 'A. Writer' |
317 | }, |
318 | { |
319 | join => { |
320 | 'cds' => { |
321 | 'liner_notes' => 'author' |
322 | } |
323 | } |
324 | } |
325 | ); |
326 | |
327 | # equivalent SQL |
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' |
87980de7 |
334 | |
335 | =head2 Transactions |
336 | |
337 | As of version 0.04001, there is improved transaction support in |
35d4fe78 |
338 | L<DBIx::Class::Storage::DBI>. Here is an example of the recommended |
40dbc108 |
339 | way to use it: |
87980de7 |
340 | |
35d4fe78 |
341 | my $genus = Genus->find(12); |
342 | eval { |
343 | MyDB->txn_begin; |
344 | $genus->add_to_species({ name => 'troglodyte' }); |
345 | $genus->wings(2); |
346 | $genus->update; |
347 | cromulate($genus); # Can have a nested transation |
348 | MyDB->txn_commit; |
349 | }; |
350 | if ($@) { |
351 | # Rollback might fail, too |
87980de7 |
352 | eval { |
35d4fe78 |
353 | MyDB->txn_rollback |
87980de7 |
354 | }; |
35d4fe78 |
355 | } |
87980de7 |
356 | |
40dbc108 |
357 | Currently, a nested commit will do nothing and a nested rollback will |
35d4fe78 |
358 | die. The code at each level must be sure to call rollback in the case |
40dbc108 |
359 | of an error, to ensure that the rollback will propagate to the top |
35d4fe78 |
360 | level and be issued. Support for savepoints and for true nested |
40dbc108 |
361 | transactions (for databases that support them) will hopefully be added |
362 | in the future. |
ee38fa40 |
363 | |
130c6439 |
364 | =head2 Many-to-many relationships |
ee38fa40 |
365 | |
ea6309e2 |
366 | This is straightforward using L<DBIx::Class::Relationship::ManyToMany>: |
367 | |
368 | package My::DB; |
369 | # set up connection here... |
370 | |
371 | package My::User; |
372 | use base 'My::DB'; |
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'); |
378 | |
379 | package My::UserAddress; |
380 | use base 'My::DB'; |
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'); |
386 | |
387 | package My::Address; |
388 | use base 'My::DB'; |
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'); |
394 | |
395 | $rs = $user->addresses(); # get all addresses for a user |
396 | $rs = $address->users(); # get all users for an address |
ee38fa40 |
397 | |
a00e1684 |
398 | =head2 Setting default values |
399 | |
35d4fe78 |
400 | It's as simple as overriding the C<new> method. Note the use of |
40dbc108 |
401 | C<next::method>. |
a00e1684 |
402 | |
35d4fe78 |
403 | sub new { |
404 | my ( $class, $attrs ) = @_; |
40dbc108 |
405 | |
35d4fe78 |
406 | $attrs->{foo} = 'bar' unless defined $attrs->{foo}; |
40dbc108 |
407 | |
35d4fe78 |
408 | $class->next::method($attrs); |
409 | } |
a00e1684 |
410 | |
25af00d7 |
411 | =head2 Stringification |
412 | |
40dbc108 |
413 | Employ the standard stringification technique by using the C<overload> |
35d4fe78 |
414 | module. Replace C<foo> with the column/method of your choice. |
25af00d7 |
415 | |
35d4fe78 |
416 | use overload '""' => 'foo', fallback => 1; |
25af00d7 |
417 | |
40dbc108 |
418 | =cut |