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 | |
35d4fe78 |
11 | my @albums = MyApp::DB::Album->search({ |
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 | |
35d4fe78 |
22 | my @albums = MyApp::DB::Album->search({ |
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 | |
40dbc108 |
52 | See L<DBIx::Class::ResultSet/ATTRIBUTES>. |
87980de7 |
53 | |
54 | =head2 Transactions |
55 | |
56 | As of version 0.04001, there is improved transaction support in |
35d4fe78 |
57 | L<DBIx::Class::Storage::DBI>. Here is an example of the recommended |
40dbc108 |
58 | way to use it: |
87980de7 |
59 | |
35d4fe78 |
60 | my $genus = Genus->find(12); |
61 | eval { |
62 | MyDB->txn_begin; |
63 | $genus->add_to_species({ name => 'troglodyte' }); |
64 | $genus->wings(2); |
65 | $genus->update; |
66 | cromulate($genus); # Can have a nested transation |
67 | MyDB->txn_commit; |
68 | }; |
69 | if ($@) { |
70 | # Rollback might fail, too |
87980de7 |
71 | eval { |
35d4fe78 |
72 | MyDB->txn_rollback |
87980de7 |
73 | }; |
35d4fe78 |
74 | } |
87980de7 |
75 | |
40dbc108 |
76 | Currently, a nested commit will do nothing and a nested rollback will |
35d4fe78 |
77 | die. The code at each level must be sure to call rollback in the case |
40dbc108 |
78 | of an error, to ensure that the rollback will propagate to the top |
35d4fe78 |
79 | level and be issued. Support for savepoints and for true nested |
40dbc108 |
80 | transactions (for databases that support them) will hopefully be added |
81 | in the future. |
ee38fa40 |
82 | |
130c6439 |
83 | =head2 Many-to-many relationships |
ee38fa40 |
84 | |
35d4fe78 |
85 | This is not as easy as it could be, but it's possible. Here's an |
40dbc108 |
86 | example to illustrate: |
87 | |
35d4fe78 |
88 | # Set up inherited connection information |
89 | package MyApp::DBIC; |
90 | use base qw/DBIx::Class/; |
91 | |
92 | __PACKAGE__->load_components(qw/PK::Auto::SQLite Core DB/); |
93 | __PACKAGE__->connection(...); |
94 | |
95 | # Set up a class for the 'authors' table |
96 | package MyApp::DBIC::Author; |
97 | use base qw/MyApp::DBIC/; |
98 | |
99 | __PACKAGE__->table('authors'); |
100 | __PACKAGE__->add_columns(qw/authID first_name last_name/); |
101 | __PACKAGE__->set_primary_key(qw/authID/); |
102 | |
103 | # Define relationship to the link table |
104 | __PACKAGE__->has_many('b2a' => 'MyApp::DBIC::Book2Author', 'authID'); |
105 | |
106 | # Create the accessor for books from the Author class |
107 | sub books { |
108 | my ($self) = @_; |
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. |
115 | ); |
116 | } |
117 | |
118 | # Define the link table class |
119 | package MyApp::DBIC::Book2Author; |
120 | use base qw/MyApp::DBIC/; |
121 | |
122 | __PACKAGE__->table('book2author'); |
123 | __PACKAGE__->add_columns(qw/bookID authID/); |
124 | __PACKAGE__->set_primary_key(qw/bookID authID/); |
125 | |
126 | __PACKAGE__->belongs_to('authID' => 'MyApp::DBIC::Author'); |
127 | __PACKAGE__->belongs_to('bookID' => 'MyApp::DBIC::Book'); |
128 | |
129 | package MyApp::DBIC::Book; |
130 | use base qw/MyApp::DBIC/; |
131 | |
132 | __PACKAGE__->table('books'); |
133 | __PACKAGE__->add_columns(qw/bookID title edition isbn publisher year/); |
134 | __PACKAGE__->set_primary_key(qw/bookID/); |
135 | |
136 | __PACKAGE__->has_many('b2a' => 'MyApp::DBIC::Book2Author', 'bookID'); |
137 | |
138 | |
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) |
142 | sub authors { |
143 | my ($self) = @_; |
144 | return MyApp::DBIC::Author->search( |
145 | { 'b2a.bookID' => $self->bookID }, # WHERE clause |
146 | { join => 'b2a' } # JOIN condition |
147 | ); |
148 | } |
ee38fa40 |
149 | |
a00e1684 |
150 | =head2 Setting default values |
151 | |
35d4fe78 |
152 | It's as simple as overriding the C<new> method. Note the use of |
40dbc108 |
153 | C<next::method>. |
a00e1684 |
154 | |
35d4fe78 |
155 | sub new { |
156 | my ( $class, $attrs ) = @_; |
40dbc108 |
157 | |
35d4fe78 |
158 | $attrs->{foo} = 'bar' unless defined $attrs->{foo}; |
40dbc108 |
159 | |
35d4fe78 |
160 | $class->next::method($attrs); |
161 | } |
a00e1684 |
162 | |
25af00d7 |
163 | =head2 Stringification |
164 | |
40dbc108 |
165 | Employ the standard stringification technique by using the C<overload> |
35d4fe78 |
166 | module. Replace C<foo> with the column/method of your choice. |
25af00d7 |
167 | |
35d4fe78 |
168 | use overload '""' => 'foo', fallback => 1; |
25af00d7 |
169 | |
40dbc108 |
170 | =cut |