Some cleanup
[dbsrgits/DBIx-Class.git] / t / 76joins.t
CommitLineData
70350518 1use strict;
2use warnings;
3
4use Test::More;
5use lib qw(t/lib);
6use DBICTest;
949172b0 7use DBIC::SqlMakerTest;
70350518 8
a47e1233 9my $schema = DBICTest->init_schema();
0567538f 10
4d91ad3f 11my $orig_debug = $schema->storage->debug;
12
0567538f 13use IO::File;
14
15BEGIN {
16 eval "use DBD::SQLite";
17 plan $@
18 ? ( skip_all => 'needs DBD::SQLite for testing' )
0f6fc705 19 : ( tests => 33 );
0567538f 20}
21
22# test the abstract join => SQL generator
6f4ddea1 23my $sa = new DBIx::Class::SQLAHacks;
0567538f 24
25my @j = (
26 { child => 'person' },
27 [ { father => 'person' }, { 'father.person_id' => 'child.father_id' }, ],
28 [ { mother => 'person' }, { 'mother.person_id' => 'child.mother_id' } ],
29);
30my $match = 'person child JOIN person father ON ( father.person_id = '
31 . 'child.father_id ) JOIN person mother ON ( mother.person_id '
32 . '= child.mother_id )'
33 ;
af6aac2d 34is_same_sql(
35 $sa->_recurse_from(@j),
36 $match,
9b459129 37 'join 1 ok'
38);
0567538f 39
40my @j2 = (
41 { mother => 'person' },
42 [ [ { child => 'person' },
43 [ { father => 'person' },
44 { 'father.person_id' => 'child.father_id' }
45 ]
46 ],
47 { 'mother.person_id' => 'child.mother_id' }
48 ],
49);
50$match = 'person mother JOIN (person child JOIN person father ON ('
51 . ' father.person_id = child.father_id )) ON ( mother.person_id = '
52 . 'child.mother_id )'
53 ;
af6aac2d 54is_same_sql(
55 $sa->_recurse_from(@j2),
56 $match,
9b459129 57 'join 2 ok'
58);
59
0567538f 60
61my @j3 = (
62 { child => 'person' },
63 [ { father => 'person', -join_type => 'inner' }, { 'father.person_id' => 'child.father_id' }, ],
64 [ { mother => 'person', -join_type => 'inner' }, { 'mother.person_id' => 'child.mother_id' } ],
65);
66$match = 'person child INNER JOIN person father ON ( father.person_id = '
67 . 'child.father_id ) INNER JOIN person mother ON ( mother.person_id '
68 . '= child.mother_id )'
69 ;
70
af6aac2d 71is_same_sql(
72 $sa->_recurse_from(@j3),
73 $match,
9b459129 74 'join 3 (inner join) ok'
75);
0567538f 76
ca7b9fdf 77my @j4 = (
78 { mother => 'person' },
79 [ [ { child => 'person', -join_type => 'left' },
80 [ { father => 'person', -join_type => 'right' },
81 { 'father.person_id' => 'child.father_id' }
82 ]
83 ],
84 { 'mother.person_id' => 'child.mother_id' }
85 ],
86);
87$match = 'person mother LEFT JOIN (person child RIGHT JOIN person father ON ('
88 . ' father.person_id = child.father_id )) ON ( mother.person_id = '
89 . 'child.mother_id )'
90 ;
af6aac2d 91is_same_sql(
92 $sa->_recurse_from(@j4),
93 $match,
9b459129 94 'join 4 (nested joins + join types) ok'
95);
ca7b9fdf 96
635b9634 97my @j5 = (
98 { child => 'person' },
99 [ { father => 'person' }, { 'father.person_id' => \'!= child.father_id' }, ],
100 [ { mother => 'person' }, { 'mother.person_id' => 'child.mother_id' } ],
101);
102$match = 'person child JOIN person father ON ( father.person_id != '
103 . 'child.father_id ) JOIN person mother ON ( mother.person_id '
104 . '= child.mother_id )'
105 ;
af6aac2d 106is_same_sql(
107 $sa->_recurse_from(@j5),
108 $match,
9b459129 109 'join 5 (SCALAR reference for ON statement) ok'
110);
635b9634 111
112my @j6 = (
113 { child => 'person' },
114 [ { father => 'person' }, { 'father.person_id' => { '!=', '42' } }, ],
115 [ { mother => 'person' }, { 'mother.person_id' => 'child.mother_id' } ],
116);
1177100a 117$match = qr/HASH reference arguments are not supported in JOINS/;
635b9634 118eval { $sa->_recurse_from(@j6) };
119like( $@, $match, 'join 6 (HASH reference for ON statement dies) ok' );
120
f9db5527 121my $rs = $schema->resultset("CD")->search(
0567538f 122 { 'year' => 2001, 'artist.name' => 'Caterwauler McCrae' },
123 { from => [ { 'me' => 'cd' },
124 [
125 { artist => 'artist' },
126 { 'me.artist' => 'artist.artistid' }
127 ] ] }
128 );
129
cb9b5b23 130is( $rs + 0, 1, "Single record in resultset");
0567538f 131
132is($rs->first->title, 'Forkful of bees', 'Correct record returned');
133
f9db5527 134$rs = $schema->resultset("CD")->search(
0567538f 135 { 'year' => 2001, 'artist.name' => 'Caterwauler McCrae' },
136 { join => 'artist' });
137
cb9b5b23 138is( $rs + 0, 1, "Single record in resultset");
0567538f 139
140is($rs->first->title, 'Forkful of bees', 'Correct record returned');
141
f9db5527 142$rs = $schema->resultset("CD")->search(
0567538f 143 { 'artist.name' => 'We Are Goth',
144 'liner_notes.notes' => 'Kill Yourself!' },
145 { join => [ qw/artist liner_notes/ ] });
146
cb9b5b23 147is( $rs + 0, 1, "Single record in resultset");
0567538f 148
149is($rs->first->title, 'Come Be Depressed With Us', 'Correct record returned');
150
8fe164b9 151# when using join attribute, make sure slice()ing all objects has same count as all()
f9db5527 152$rs = $schema->resultset("CD")->search(
8fe164b9 153 { 'artist' => 1 },
154 { join => [qw/artist/], order_by => 'artist.name' }
155);
cb9b5b23 156is( scalar $rs->all, scalar $rs->slice(0, $rs->count - 1), 'slice() with join has same count as all()' );
8fe164b9 157
2bd9c7c0 158ok(!$rs->slice($rs->count+1000, $rs->count+1002)->count,
159 'Slicing beyond end of rs returns a zero count');
160
f9db5527 161$rs = $schema->resultset("Artist")->search(
0567538f 162 { 'liner_notes.notes' => 'Kill Yourself!' },
163 { join => { 'cds' => 'liner_notes' } });
164
cb9b5b23 165is( $rs->count, 1, "Single record in resultset");
0567538f 166
167is($rs->first->name, 'We Are Goth', 'Correct record returned');
168
9f2d17e9 169
0f6fc705 170{
171 $schema->populate('Artist', [
172 [ qw/artistid name/ ],
173 [ 4, 'Another Boy Band' ],
174 ]);
175 $schema->populate('CD', [
176 [ qw/cdid artist title year/ ],
177 [ 6, 2, "Greatest Hits", 2001 ],
178 [ 7, 4, "Greatest Hits", 2005 ],
179 [ 8, 4, "BoyBandBlues", 2008 ],
180 ]);
181 $schema->populate('TwoKeys', [
182 [ qw/artist cd/ ],
183 [ 2, 4 ],
184 [ 2, 6 ],
185 [ 4, 7 ],
186 [ 4, 8 ],
187 ]);
188
189 sub cd_count {
190 return $schema->resultset("CD")->count;
191 }
192 sub tk_count {
193 return $schema->resultset("TwoKeys")->count;
194 }
195
cb9b5b23 196 is(cd_count(), 8, '8 rows in table cd');
197 is(tk_count(), 7, '7 rows in table twokeys');
0f6fc705 198
199 sub artist1 {
200 return $schema->resultset("CD")->search(
201 { 'artist.name' => 'Caterwauler McCrae' },
202 { join => [qw/artist/]}
203 );
204 }
205 sub artist2 {
206 return $schema->resultset("CD")->search(
207 { 'artist.name' => 'Random Boy Band' },
208 { join => [qw/artist/]}
209 );
210 }
211
cb9b5b23 212 is( artist1()->count, 3, '3 Caterwauler McCrae CDs' );
0f6fc705 213 ok( artist1()->delete, 'Successfully deleted 3 CDs' );
cb9b5b23 214 is( artist1()->count, 0, '0 Caterwauler McCrae CDs' );
215 is( artist2()->count, 2, '3 Random Boy Band CDs' );
0f6fc705 216 ok( artist2()->update( { 'artist' => 1 } ) );
cb9b5b23 217 is( artist2()->count, 0, '0 Random Boy Band CDs' );
218 is( artist1()->count, 2, '2 Caterwauler McCrae CDs' );
0f6fc705 219
220 # test update on multi-column-pk
221 sub tk1 {
222 return $schema->resultset("TwoKeys")->search(
223 {
224 'artist.name' => { like => '%Boy Band' },
225 'cd.title' => 'Greatest Hits',
226 },
227 { join => [qw/artist cd/] }
228 );
229 }
230 sub tk2 {
231 return $schema->resultset("TwoKeys")->search(
232 { 'artist.name' => 'Caterwauler McCrae' },
233 { join => [qw/artist/]}
234 );
235 }
cb9b5b23 236 is( tk2()->count, 2, 'TwoKeys count == 2' );
237 is( tk1()->count, 2, 'TwoKeys count == 2' );
0f6fc705 238 ok( tk1()->update( { artist => 1 } ) );
cb9b5b23 239 is( tk1()->count, 0, 'TwoKeys count == 0' );
240 is( tk2()->count, 4, '2 Caterwauler McCrae CDs' );
0f6fc705 241 ok( tk2()->delete, 'Successfully deleted 4 CDs' );
cb9b5b23 242 is(cd_count(), 5, '5 rows in table cd');
243 is(tk_count(), 3, '3 rows in table twokeys');
0f6fc705 244}