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 = MyApp::DB::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 = MyApp::DB::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 See L<DBIx::Class::ResultSet/ATTRIBUTES>.
56 As of version 0.04001, there is improved transaction support in
57 L<DBIx::Class::Storage::DBI>. Here is an example of the recommended
60 my $genus = Genus->find(12);
63 $genus->add_to_species({ name => 'troglodyte' });
66 cromulate($genus); # Can have a nested transation
70 # Rollback might fail, too
76 Currently, a nested commit will do nothing and a nested rollback will
77 die. The code at each level must be sure to call rollback in the case
78 of an error, to ensure that the rollback will propagate to the top
79 level and be issued. Support for savepoints and for true nested
80 transactions (for databases that support them) will hopefully be added
83 =head2 Many-to-many relationships
85 This is not as easy as it could be, but it's possible. Here's an
86 example to illustrate:
88 # Set up inherited connection information
90 use base qw/DBIx::Class/;
92 __PACKAGE__->load_components(qw/PK::Auto::SQLite Core DB/);
93 __PACKAGE__->connection(...);
95 # Set up a class for the 'authors' table
96 package MyApp::DBIC::Author;
97 use base qw/MyApp::DBIC/;
99 __PACKAGE__->table('authors');
100 __PACKAGE__->add_columns(qw/authID first_name last_name/);
101 __PACKAGE__->set_primary_key(qw/authID/);
103 # Define relationship to the link table
104 __PACKAGE__->has_many('b2a' => 'MyApp::DBIC::Book2Author', 'authID');
106 # Create the accessor for books from the Author class
109 return MyApp::DBIC::Book->search(
110 { 'b2a.authID' => $self->authID }, # WHERE clause
111 { join => 'b2a' } # join condition (part of search attrs)
112 # 'b2a' refers to the relationship named earlier in the Author class.
113 # 'b2a.authID' refers to the authID column of the b2a relationship,
114 # which becomes accessible in the search by being joined.
118 # Define the link table class
119 package MyApp::DBIC::Book2Author;
120 use base qw/MyApp::DBIC/;
122 __PACKAGE__->table('book2author');
123 __PACKAGE__->add_columns(qw/bookID authID/);
124 __PACKAGE__->set_primary_key(qw/bookID authID/);
126 __PACKAGE__->belongs_to('authID' => 'MyApp::DBIC::Author');
127 __PACKAGE__->belongs_to('bookID' => 'MyApp::DBIC::Book');
129 package MyApp::DBIC::Book;
130 use base qw/MyApp::DBIC/;
132 __PACKAGE__->table('books');
133 __PACKAGE__->add_columns(qw/bookID title edition isbn publisher year/);
134 __PACKAGE__->set_primary_key(qw/bookID/);
136 __PACKAGE__->has_many('b2a' => 'MyApp::DBIC::Book2Author', 'bookID');
139 # Returns an author record where the bookID field of the
140 # book2author table equals the bookID of the books (using the
141 # bookID relationship table)
144 return MyApp::DBIC::Author->search(
145 { 'b2a.bookID' => $self->bookID }, # WHERE clause
146 { join => 'b2a' } # JOIN condition
150 =head2 Setting default values
152 It's as simple as overriding the C<new> method. Note the use of
156 my ( $class, $attrs ) = @_;
158 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
160 $class->next::method($attrs);
163 =head2 Stringification
165 Employ the standard stringification technique by using the C<overload>
166 module. Replace C<foo> with the column/method of your choice.
168 use overload '""' => 'foo', fallback => 1;