Commit | Line | Data |
4c2b30d6 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | use Test::Exception; |
6 | use lib qw(t/lib); |
7 | use DBICTest; |
8 | use DBIC::SqlMakerTest; |
9 | |
10 | my $schema = DBICTest->init_schema(); |
11 | my $orig_debug = $schema->storage->debug; |
12 | |
13 | my $cdrs = $schema->resultset('CD')->search({ 'me.artist' => { '!=', 2 }}); |
14 | |
15 | my $cd_data = { map { |
16 | $_->cdid => { |
17 | siblings => $cdrs->search ({ artist => $_->get_column('artist') })->count - 1, |
18 | track_titles => [ map { $_->title } ($_->tracks->all) ], |
19 | }, |
20 | } ( $cdrs->all ) }; |
21 | |
22 | my $queries = 0; |
23 | $schema->storage->debugcb(sub { $queries++; }); |
24 | $schema->storage->debug(1); |
25 | |
26 | my $c_rs = $cdrs->search ({}, { |
27 | prefetch => 'tracks', |
28 | '+columns' => { sibling_count => $cdrs->search( |
29 | { |
30 | 'siblings.artist' => { -ident => 'me.artist' }, |
31 | 'siblings.cdid' => { '!=' => ['-and', { -ident => 'me.cdid' }, 'bogus condition'] }, |
32 | }, { alias => 'siblings' }, |
33 | )->count_rs->as_query, |
34 | }, |
35 | }); |
36 | |
37 | is_same_sql_bind( |
38 | $c_rs->as_query, |
39 | '( |
40 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
41 | (SELECT COUNT( * ) |
42 | FROM cd siblings |
43 | WHERE siblings.artist = me.artist |
44 | AND siblings.cdid != me.cdid |
45 | AND siblings.cdid != ? |
46 | AND me.artist != ? |
47 | ), |
48 | tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at |
49 | FROM cd me |
50 | LEFT JOIN track tracks |
51 | ON tracks.cd = me.cdid |
52 | WHERE me.artist != ? |
53 | ORDER BY tracks.cd |
54 | )', |
55 | [ |
56 | [ 'siblings.cdid' => 'bogus condition' ], |
57 | [ 'me.artist' => 2 ], |
58 | [ 'me.artist' => 2 ], |
59 | ], |
60 | 'Expected SQL on correlated realiased subquery' |
61 | ); |
62 | |
63 | is_deeply ( |
64 | { map |
65 | { $_->cdid => { |
66 | track_titles => [ map { $_->title } ($_->tracks->all) ], |
67 | siblings => $_->get_column ('sibling_count'), |
68 | } } |
69 | $c_rs->all |
70 | }, |
71 | $cd_data, |
72 | 'Proper information retrieved from correlated subquery' |
73 | ); |
74 | |
75 | is ($queries, 1, 'Only 1 query fired to retrieve everything'); |
76 | |
77 | $schema->storage->debug($orig_debug); |
78 | $schema->storage->debugcb(undef); |
79 | |
80 | done_testing; |