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 | |
87980de7 |
162 | =head2 Using joins and prefetch |
163 | |
ea6309e2 |
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: |
167 | |
168 | my $rs = $schema->resultset('CD')->search( |
169 | { |
170 | 'artist.name' => 'Bob Marley' |
171 | }, |
172 | { |
173 | join => [qw/artist/], # join the artist table |
174 | } |
175 | ); |
176 | |
177 | # equivalent SQL: |
178 | # SELECT cd.* FROM cd |
179 | # JOIN artist ON cd.artist = artist.id |
180 | # WHERE artist.name = 'Bob Marley' |
181 | |
182 | If required, you can now sort on any column in the related table(s) by |
183 | including it in your "order_by" attribute: |
184 | |
185 | my $rs = $schema->resultset('CD')->search( |
186 | { |
187 | 'artist.name' => 'Bob Marley' |
188 | }, |
189 | { |
190 | join => [qw/ artist /], |
191 | order_by => [qw/ artist.name /] |
192 | } |
193 | }; |
194 | |
195 | # equivalent SQL: |
196 | # SELECT cd.* FROM cd |
197 | # JOIN artist ON cd.artist = artist.id |
198 | # WHERE artist.name = 'Bob Marley' |
199 | # ORDER BY artist.name |
200 | |
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! |
204 | |
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: |
207 | |
208 | while (my $cd = $rs->next) { |
209 | print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; |
210 | } |
211 | |
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 |
215 | database: |
216 | |
217 | SELECT artist.* FROM artist WHERE artist.id = ? |
218 | |
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 |
221 | queries! |
222 | |
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 |
225 | for your class: |
226 | |
227 | my $rs = $schema->resultset('CD')->search( |
228 | { |
229 | 'artist.name' => 'Bob Marley' |
230 | }, |
231 | { |
232 | join => [qw/ artist /], |
233 | order_by => [qw/ artist.name /], |
234 | prefetch => [qw/ artist /] # return artist data too! |
235 | } |
236 | ); |
237 | |
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 |
243 | |
244 | The code to print the CD list remains the same: |
245 | |
246 | while (my $cd = $rs->next) { |
247 | print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; |
248 | } |
249 | |
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 |
252 | efficient query. |
253 | |
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. |
257 | |
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! |
261 | |
262 | =head2 Multi-step joins |
263 | |
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 |
266 | specific string: |
267 | |
268 | # Artist->has_many('cds' => 'CD', 'artist'); |
269 | # CD->has_one('liner_notes' => 'LinerNotes', 'cd'); |
270 | |
271 | $rs = $schema->resultset('Artist')->search( |
272 | { |
273 | 'liner_notes.notes' => { 'like', '%some text%' }, |
274 | }, |
275 | { |
276 | join => { |
277 | 'cds' => 'liner_notes' |
278 | } |
279 | } |
280 | ); |
281 | |
282 | # equivalent SQL |
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%' |
287 | |
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 |
290 | notes: |
291 | |
faf62551 |
292 | # LinerNotes->belongs_to('author' => 'Person'); |
ea6309e2 |
293 | |
294 | $rs = $schema->resultset('Artist')->search( |
295 | { |
296 | 'liner_notes.notes' => { 'like', '%some text%' }, |
297 | 'author.name' => 'A. Writer' |
298 | }, |
299 | { |
300 | join => { |
301 | 'cds' => { |
302 | 'liner_notes' => 'author' |
303 | } |
304 | } |
305 | } |
306 | ); |
307 | |
308 | # equivalent SQL |
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' |
87980de7 |
315 | |
316 | =head2 Transactions |
317 | |
318 | As of version 0.04001, there is improved transaction support in |
35d4fe78 |
319 | L<DBIx::Class::Storage::DBI>. Here is an example of the recommended |
40dbc108 |
320 | way to use it: |
87980de7 |
321 | |
35d4fe78 |
322 | my $genus = Genus->find(12); |
323 | eval { |
324 | MyDB->txn_begin; |
325 | $genus->add_to_species({ name => 'troglodyte' }); |
326 | $genus->wings(2); |
327 | $genus->update; |
328 | cromulate($genus); # Can have a nested transation |
329 | MyDB->txn_commit; |
330 | }; |
331 | if ($@) { |
332 | # Rollback might fail, too |
87980de7 |
333 | eval { |
35d4fe78 |
334 | MyDB->txn_rollback |
87980de7 |
335 | }; |
35d4fe78 |
336 | } |
87980de7 |
337 | |
40dbc108 |
338 | Currently, a nested commit will do nothing and a nested rollback will |
35d4fe78 |
339 | die. The code at each level must be sure to call rollback in the case |
40dbc108 |
340 | of an error, to ensure that the rollback will propagate to the top |
35d4fe78 |
341 | level and be issued. Support for savepoints and for true nested |
40dbc108 |
342 | transactions (for databases that support them) will hopefully be added |
343 | in the future. |
ee38fa40 |
344 | |
130c6439 |
345 | =head2 Many-to-many relationships |
ee38fa40 |
346 | |
ea6309e2 |
347 | This is straightforward using L<DBIx::Class::Relationship::ManyToMany>: |
348 | |
349 | package My::DB; |
350 | # set up connection here... |
351 | |
352 | package My::User; |
353 | use base 'My::DB'; |
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'); |
359 | |
360 | package My::UserAddress; |
361 | use base 'My::DB'; |
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'); |
367 | |
368 | package My::Address; |
369 | use base 'My::DB'; |
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'); |
375 | |
376 | $rs = $user->addresses(); # get all addresses for a user |
377 | $rs = $address->users(); # get all users for an address |
ee38fa40 |
378 | |
a00e1684 |
379 | =head2 Setting default values |
380 | |
35d4fe78 |
381 | It's as simple as overriding the C<new> method. Note the use of |
40dbc108 |
382 | C<next::method>. |
a00e1684 |
383 | |
35d4fe78 |
384 | sub new { |
385 | my ( $class, $attrs ) = @_; |
40dbc108 |
386 | |
35d4fe78 |
387 | $attrs->{foo} = 'bar' unless defined $attrs->{foo}; |
40dbc108 |
388 | |
35d4fe78 |
389 | $class->next::method($attrs); |
390 | } |
a00e1684 |
391 | |
25af00d7 |
392 | =head2 Stringification |
393 | |
40dbc108 |
394 | Employ the standard stringification technique by using the C<overload> |
35d4fe78 |
395 | module. Replace C<foo> with the column/method of your choice. |
25af00d7 |
396 | |
35d4fe78 |
397 | use overload '""' => 'foo', fallback => 1; |
25af00d7 |
398 | |
40dbc108 |
399 | =cut |