3 DBIx::Class::Manual::Cookbook - Miscellaneous recipes
7 =head2 Complex searches
9 Sometimes you need to formulate a query using specific operators:
11 my @albums = $schema->resultset('Album')->search({
12 artist => { 'like', '%Lamb%' },
13 title => { 'like', '%Fear of Fours%' },
16 This results in something like the following C<WHERE> clause:
18 WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%'
20 Other queries might require slightly more complex logic:
22 my @albums = $schema->resultset('Album')->search({
25 artist => { 'like', '%Smashing Pumpkins%' },
26 title => 'Siamese Dream',
28 artist => 'Starchildren',
32 This results in the following C<WHERE> clause:
34 WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' )
35 OR artist = 'Starchildren'
37 For more information on generating complex queries, see
38 L<SQL::Abstract/WHERE CLAUSES>.
40 =head2 Disconnecting cleanly
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:
47 __PACKAGE__->storage->dbh->disconnect;
50 =head2 Using joins and prefetch
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:
56 my $rs = $schema->resultset('CD')->search(
58 'artist.name' => 'Bob Marley'
61 join => [qw/artist/], # join the artist table
67 # JOIN artist ON cd.artist = artist.id
68 # WHERE artist.name = 'Bob Marley'
70 If required, you can now sort on any column in the related table(s) by
71 including it in your "order_by" attribute:
73 my $rs = $schema->resultset('CD')->search(
75 'artist.name' => 'Bob Marley'
78 join => [qw/ artist /],
79 order_by => [qw/ artist.name /]
85 # JOIN artist ON cd.artist = artist.id
86 # WHERE artist.name = 'Bob Marley'
87 # ORDER BY artist.name
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!
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:
96 while (my $cd = $rs->next) {
97 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
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
105 SELECT artist.* FROM artist WHERE artist.id = ?
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
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
115 my $rs = $schema->resultset('CD')->search(
117 'artist.name' => 'Bob Marley'
120 join => [qw/ artist /],
121 order_by => [qw/ artist.name /],
122 prefetch => [qw/ artist /] # return artist data too!
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
132 The code to print the CD list remains the same:
134 while (my $cd = $rs->next) {
135 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
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
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.
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!
150 =head2 Multi-step joins
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
156 # Artist->has_many('cds' => 'CD', 'artist');
157 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
159 $rs = $schema->resultset('Artist')->search(
161 'liner_notes.notes' => { 'like', '%some text%' },
165 'cds' => 'liner_notes'
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%'
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
180 # LinerNotes->has_one('author' => 'Person');
182 $rs = $schema->resultset('Artist')->search(
184 'liner_notes.notes' => { 'like', '%some text%' },
185 'author.name' => 'A. Writer'
190 'liner_notes' => 'author'
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'
206 As of version 0.04001, there is improved transaction support in
207 L<DBIx::Class::Storage::DBI>. Here is an example of the recommended
210 my $genus = Genus->find(12);
213 $genus->add_to_species({ name => 'troglodyte' });
216 cromulate($genus); # Can have a nested transation
220 # Rollback might fail, too
226 Currently, a nested commit will do nothing and a nested rollback will
227 die. The code at each level must be sure to call rollback in the case
228 of an error, to ensure that the rollback will propagate to the top
229 level and be issued. Support for savepoints and for true nested
230 transactions (for databases that support them) will hopefully be added
233 =head2 Many-to-many relationships
235 This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:
238 # set up connection here...
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');
248 package My::UserAddress;
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');
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');
264 $rs = $user->addresses(); # get all addresses for a user
265 $rs = $address->users(); # get all users for an address
267 =head2 Setting default values
269 It's as simple as overriding the C<new> method. Note the use of
273 my ( $class, $attrs ) = @_;
275 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
277 $class->next::method($attrs);
280 =head2 Stringification
282 Employ the standard stringification technique by using the C<overload>
283 module. Replace C<foo> with the column/method of your choice.
285 use overload '""' => 'foo', fallback => 1;