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 | |
40dbc108 |
7 | =head2 Complex searches |
2913b2d3 |
8 | |
40dbc108 |
9 | Sometimes you need to formulate a query using specific operators: |
10 | |
ea6309e2 |
11 | my @albums = $schema->resultset('Album')->search({ |
35d4fe78 |
12 | artist => { 'like', '%Lamb%' }, |
13 | title => { 'like', '%Fear of Fours%' }, |
14 | }); |
40dbc108 |
15 | |
16 | This results in something like the following C<WHERE> clause: |
17 | |
35d4fe78 |
18 | WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%' |
40dbc108 |
19 | |
20 | Other queries might require slightly more complex logic: |
21 | |
ea6309e2 |
22 | my @albums = $schema->resultset('Album')->search({ |
35d4fe78 |
23 | -or => [ |
24 | -and => [ |
25 | artist => { 'like', '%Smashing Pumpkins%' }, |
26 | title => 'Siamese Dream', |
27 | ], |
28 | artist => 'Starchildren', |
29 | ], |
30 | }); |
40dbc108 |
31 | |
32 | This results in the following C<WHERE> clause: |
33 | |
35d4fe78 |
34 | WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' ) |
35 | OR artist = 'Starchildren' |
40dbc108 |
36 | |
37 | For more information on generating complex queries, see |
38 | L<SQL::Abstract/WHERE CLAUSES>. |
ee38fa40 |
39 | |
87980de7 |
40 | =head2 Disconnecting cleanly |
ee38fa40 |
41 | |
40dbc108 |
42 | If you find yourself quitting an app with Control-C a lot during |
43 | development, you might like to put the following signal handler in |
44 | your main database class to make sure it disconnects cleanly: |
87980de7 |
45 | |
35d4fe78 |
46 | $SIG{INT} = sub { |
47 | __PACKAGE__->storage->dbh->disconnect; |
48 | }; |
87980de7 |
49 | |
50 | =head2 Using joins and prefetch |
51 | |
ea6309e2 |
52 | You can use the "join" attribute to allow searching on, or sorting your |
53 | results by, one or more columns in a related table. To return |
54 | all CDs matching a particular artist name: |
55 | |
56 | my $rs = $schema->resultset('CD')->search( |
57 | { |
58 | 'artist.name' => 'Bob Marley' |
59 | }, |
60 | { |
61 | join => [qw/artist/], # join the artist table |
62 | } |
63 | ); |
64 | |
65 | # equivalent SQL: |
66 | # SELECT cd.* FROM cd |
67 | # JOIN artist ON cd.artist = artist.id |
68 | # WHERE artist.name = 'Bob Marley' |
69 | |
70 | If required, you can now sort on any column in the related table(s) by |
71 | including it in your "order_by" attribute: |
72 | |
73 | my $rs = $schema->resultset('CD')->search( |
74 | { |
75 | 'artist.name' => 'Bob Marley' |
76 | }, |
77 | { |
78 | join => [qw/ artist /], |
79 | order_by => [qw/ artist.name /] |
80 | } |
81 | }; |
82 | |
83 | # equivalent SQL: |
84 | # SELECT cd.* FROM cd |
85 | # JOIN artist ON cd.artist = artist.id |
86 | # WHERE artist.name = 'Bob Marley' |
87 | # ORDER BY artist.name |
88 | |
89 | Note that the "join" attribute should only be used when you need to search or |
90 | sort using columns in a related table. Joining related tables when you |
91 | only need columns from the main table will make performance worse! |
92 | |
93 | Now let's say you want to display a list of CDs, each with the name of |
94 | the artist. The following will work fine: |
95 | |
96 | while (my $cd = $rs->next) { |
97 | print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; |
98 | } |
99 | |
100 | There is a problem however. We have searched both cd and artist tables in our |
101 | main query, but we have only returned data from the cd table. To get the artist |
102 | name for any of the CD objects returned, DBIx::Class will go back to the |
103 | database: |
104 | |
105 | SELECT artist.* FROM artist WHERE artist.id = ? |
106 | |
107 | A statement like the one above will run for each and every CD returned by our |
108 | main query. Five CDs, five extra queries. A hundred CDs, one hundred extra |
109 | queries! |
110 | |
111 | Thankfully, DBIx::Class has a "prefetch" attribute to solve this problem. This |
112 | allows you to fetch results from a related table as well as the main table |
113 | for your class: |
114 | |
115 | my $rs = $schema->resultset('CD')->search( |
116 | { |
117 | 'artist.name' => 'Bob Marley' |
118 | }, |
119 | { |
120 | join => [qw/ artist /], |
121 | order_by => [qw/ artist.name /], |
122 | prefetch => [qw/ artist /] # return artist data too! |
123 | } |
124 | ); |
125 | |
126 | # equivalent SQL (note SELECT from both "cd" and "artist") |
127 | # SELECT cd.*, artist.* FROM cd |
128 | # JOIN artist ON cd.artist = artist.id |
129 | # WHERE artist.name = 'Bob Marley' |
130 | # ORDER BY artist.name |
131 | |
132 | The code to print the CD list remains the same: |
133 | |
134 | while (my $cd = $rs->next) { |
135 | print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; |
136 | } |
137 | |
138 | DBIx::Class has now prefetched all matching data from the "artist" table, |
139 | so no additional SQL statements are executed. You now have a much more |
140 | efficient query. |
141 | |
142 | Note that as of DBIx::Class 0.04, "prefetch" cannot be used with has_many |
143 | relationships. You will get an error along the lines of "No accessor for |
144 | prefetched ..." if you try. |
145 | |
146 | Note that "prefetch" should only be used when you know you will |
147 | definitely use data from a related table. Pre-fetching related tables when you |
148 | only need columns from the main table will make performance worse! |
149 | |
150 | =head2 Multi-step joins |
151 | |
152 | Sometimes you want to join more than one relationship deep. In this example, |
153 | we want to find all Artist objects who have CDs whose LinerNotes contain a |
154 | specific string: |
155 | |
156 | # Artist->has_many('cds' => 'CD', 'artist'); |
157 | # CD->has_one('liner_notes' => 'LinerNotes', 'cd'); |
158 | |
159 | $rs = $schema->resultset('Artist')->search( |
160 | { |
161 | 'liner_notes.notes' => { 'like', '%some text%' }, |
162 | }, |
163 | { |
164 | join => { |
165 | 'cds' => 'liner_notes' |
166 | } |
167 | } |
168 | ); |
169 | |
170 | # equivalent SQL |
171 | # SELECT artist.* FROM artist |
172 | # JOIN ( cd ON artist.id = cd.artist ) |
173 | # JOIN ( liner_notes ON cd.id = liner_notes.cd ) |
174 | # WHERE liner_notes.notes LIKE '%some text%' |
175 | |
176 | Joins can be nested to an arbitrary level. So if we decide later that we |
177 | want to reduce the number of Artists returned based on who wrote the liner |
178 | notes: |
179 | |
180 | # LinerNotes->has_one('author' => 'Person'); |
181 | |
182 | $rs = $schema->resultset('Artist')->search( |
183 | { |
184 | 'liner_notes.notes' => { 'like', '%some text%' }, |
185 | 'author.name' => 'A. Writer' |
186 | }, |
187 | { |
188 | join => { |
189 | 'cds' => { |
190 | 'liner_notes' => 'author' |
191 | } |
192 | } |
193 | } |
194 | ); |
195 | |
196 | # equivalent SQL |
197 | # SELECT artist.* FROM artist |
198 | # JOIN ( cd ON artist.id = cd.artist ) |
199 | # JOIN ( liner_notes ON cd.id = liner_notes.cd ) |
200 | # JOIN ( author ON author.id = liner_notes.author ) |
201 | # WHERE liner_notes.notes LIKE '%some text%' |
202 | # AND author.name = 'A. Writer' |
87980de7 |
203 | |
204 | =head2 Transactions |
205 | |
206 | As of version 0.04001, there is improved transaction support in |
35d4fe78 |
207 | L<DBIx::Class::Storage::DBI>. Here is an example of the recommended |
40dbc108 |
208 | way to use it: |
87980de7 |
209 | |
35d4fe78 |
210 | my $genus = Genus->find(12); |
211 | eval { |
212 | MyDB->txn_begin; |
213 | $genus->add_to_species({ name => 'troglodyte' }); |
214 | $genus->wings(2); |
215 | $genus->update; |
216 | cromulate($genus); # Can have a nested transation |
217 | MyDB->txn_commit; |
218 | }; |
219 | if ($@) { |
220 | # Rollback might fail, too |
87980de7 |
221 | eval { |
35d4fe78 |
222 | MyDB->txn_rollback |
87980de7 |
223 | }; |
35d4fe78 |
224 | } |
87980de7 |
225 | |
40dbc108 |
226 | Currently, a nested commit will do nothing and a nested rollback will |
35d4fe78 |
227 | die. The code at each level must be sure to call rollback in the case |
40dbc108 |
228 | of an error, to ensure that the rollback will propagate to the top |
35d4fe78 |
229 | level and be issued. Support for savepoints and for true nested |
40dbc108 |
230 | transactions (for databases that support them) will hopefully be added |
231 | in the future. |
ee38fa40 |
232 | |
130c6439 |
233 | =head2 Many-to-many relationships |
ee38fa40 |
234 | |
ea6309e2 |
235 | This is straightforward using L<DBIx::Class::Relationship::ManyToMany>: |
236 | |
237 | package My::DB; |
238 | # set up connection here... |
239 | |
240 | package My::User; |
241 | use base 'My::DB'; |
242 | __PACKAGE__->table('user'); |
243 | __PACKAGE__->add_columns(qw/id name/); |
244 | __PACKAGE__->set_primary_key('id'); |
245 | __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user'); |
246 | __PACKAGE__->many_to_many('addresses' => 'user_address', 'address'); |
247 | |
248 | package My::UserAddress; |
249 | use base 'My::DB'; |
250 | __PACKAGE__->table('user_address'); |
251 | __PACKAGE__->add_columns(qw/user address/); |
252 | __PACKAGE__->set_primary_key(qw/user address/); |
253 | __PACKAGE__->belongs_to('user' => 'My::User'); |
254 | __PACKAGE__->belongs_to('address' => 'My::Address'); |
255 | |
256 | package My::Address; |
257 | use base 'My::DB'; |
258 | __PACKAGE__->table('address'); |
259 | __PACKAGE__->add_columns(qw/id street town area_code country/); |
260 | __PACKAGE__->set_primary_key('id'); |
261 | __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address'); |
262 | __PACKAGE__->many_to_many('users' => 'user_address', 'user'); |
263 | |
264 | $rs = $user->addresses(); # get all addresses for a user |
265 | $rs = $address->users(); # get all users for an address |
ee38fa40 |
266 | |
a00e1684 |
267 | =head2 Setting default values |
268 | |
35d4fe78 |
269 | It's as simple as overriding the C<new> method. Note the use of |
40dbc108 |
270 | C<next::method>. |
a00e1684 |
271 | |
35d4fe78 |
272 | sub new { |
273 | my ( $class, $attrs ) = @_; |
40dbc108 |
274 | |
35d4fe78 |
275 | $attrs->{foo} = 'bar' unless defined $attrs->{foo}; |
40dbc108 |
276 | |
35d4fe78 |
277 | $class->next::method($attrs); |
278 | } |
a00e1684 |
279 | |
25af00d7 |
280 | =head2 Stringification |
281 | |
40dbc108 |
282 | Employ the standard stringification technique by using the C<overload> |
35d4fe78 |
283 | module. Replace C<foo> with the column/method of your choice. |
25af00d7 |
284 | |
35d4fe78 |
285 | use overload '""' => 'foo', fallback => 1; |
25af00d7 |
286 | |
40dbc108 |
287 | =cut |