7 use DBIC::SqlMakerTest;
12 my $schema = DBICTest->init_schema();
13 my $sdebug = $schema->storage->debug;
15 my $cd_rs = $schema->resultset('CD')->search (
16 { 'tracks.cd' => { '!=', undef } },
17 { prefetch => 'tracks' },
20 # Database sanity check
21 is($cd_rs->count, 5, 'CDs with tracks count');
23 is ($_->tracks->count, 3, '3 tracks for CD' . $_->id );
26 # Test a belongs_to prefetch of a has_many
28 my $track_rs = $schema->resultset ('Track')->search (
29 { 'me.cd' => { -in => [ $cd_rs->get_column ('cdid')->all ] } },
31 # the select/as is deliberately silly to test both funcs and refs below
34 { count => 'me.trackid' },
40 group_by => [qw/me.cd/],
45 is($track_rs->count, 5, 'Prefetched count with groupby');
46 is($track_rs->all, 5, 'Prefetched objects with groupby');
50 $schema->storage->debugcb ( sub { $query_cnt++ } );
51 $schema->storage->debug (1);
54 while (my $collapsed_track = $track_rs->next) {
55 my $cdid = $collapsed_track->get_column('cd');
56 is($collapsed_track->get_column('track_count'), 3, "Correct count of tracks for CD $cdid" );
57 ok($collapsed_track->cd->title, "Prefetched title for CD $cdid" );
60 is ($query_cnt, 0, 'No queries on prefetched titles');
61 $schema->storage->debugcb (undef);
62 $schema->storage->debug ($sdebug);
65 # Test sql by hand, as the sqlite db will simply paper over
66 # improper group/select combinations
68 # the exploded IN needs fixing below, coming in another branch
71 $track_rs->count_rs->as_query,
77 JOIN cd cd ON cd.cdid = me.cd
78 WHERE ( me.cd IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) )
83 [ map { [ 'me.cd' => $_] } ($cd_rs->get_column ('cdid')->all) ],
84 'count() query generated expected SQL',
90 SELECT me.cd, me.track_count, cd.cdid, cd.artist, cd.title, cd.year, cd.genreid, cd.single_track
92 SELECT me.cd, COUNT (me.trackid) AS track_count,
94 JOIN cd cd ON cd.cdid = me.cd
95 WHERE ( me.cd IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) )
98 JOIN cd cd ON cd.cdid = me.cd
99 WHERE ( me.cd IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) )
101 [ map { [ 'me.cd' => $_] } ( ($cd_rs->get_column ('cdid')->all) x 2 ) ],
102 'next() query generated expected SQL',
106 # add an extra track to one of the cds, and then make sure we can get it on top
107 # (check if limit works)
108 my $top_cd = $cd_rs->slice (1,1)->next;
109 $top_cd->create_related ('tracks', {
110 title => 'over the top',
113 my $top_cd_collapsed_track = $track_rs->search ({}, {
116 { -desc => 'track_count' },
120 is ($top_cd_collapsed_track->count, 2);
124 $top_cd_collapsed_track->first->cd->title,
125 'Correct collapsed track with prefetched CD returned on top'
129 # test a has_many/might_have prefetch at the same level
130 # Note that one of the CDs now has 4 tracks instead of 3
132 my $most_tracks_rs = $cd_rs->search ({}, {
133 prefetch => 'liner_notes', # tracks are alredy prefetched
134 select => ['me.cdid', { count => 'tracks.trackid' } ],
135 as => [qw/cdid track_count/],
136 group_by => 'me.cdid',
137 order_by => { -desc => 'track_count' },
142 $most_tracks_rs->count_rs->as_query,
148 LEFT JOIN track tracks ON tracks.cd = me.cdid
149 LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid
150 WHERE ( tracks.cd IS NOT NULL )
156 'count() query generated expected SQL',
160 $most_tracks_rs->as_query,
162 SELECT me.cdid, me.track_count, tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, liner_notes.liner_id, liner_notes.notes
164 SELECT me.cdid, COUNT( tracks.trackid ) AS track_count
166 LEFT JOIN track tracks ON tracks.cd = me.cdid
167 WHERE ( tracks.cd IS NOT NULL )
169 ORDER BY track_count DESC
172 LEFT JOIN track tracks ON tracks.cd = me.cdid
173 LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid
174 WHERE ( tracks.cd IS NOT NULL )
175 ORDER BY track_count DESC, tracks.cd
178 'next() query generated expected SQL',
181 is ($most_tracks_rs->count, 2, 'Limit works');
182 my $top_cd = $most_tracks_rs->first;
183 is ($top_cd->id, 2, 'Correct cd fetched on top'); # 2 because of the slice(1,1) above
186 $schema->storage->debugcb ( sub { $query_cnt++ } );
187 $schema->storage->debug (1);
189 is ($top_cd->get_column ('track_count'), 4, 'Track count fetched correctly');
190 is ($top_cd->tracks->count, 4, 'Count of prefetched tracks rs still correct');
191 is ($top_cd->tracks->all, 4, 'Number of prefetched track objects still correct');
193 $top_cd->liner_notes->notes,
195 'Correct liner pre-fetched with top cd',
198 is ($query_cnt, 0, 'No queries executed during prefetched data access');
199 $schema->storage->debugcb (undef);
200 $schema->storage->debug ($sdebug);