Commit | Line | Data |
4c2b30d6 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
4c2b30d6 |
5 | use lib qw(t/lib); |
6 | use DBICTest; |
7 | use DBIC::SqlMakerTest; |
8 | |
9 | my $schema = DBICTest->init_schema(); |
10 | my $orig_debug = $schema->storage->debug; |
11 | |
12 | my $cdrs = $schema->resultset('CD')->search({ 'me.artist' => { '!=', 2 }}); |
13 | |
14 | my $cd_data = { map { |
15 | $_->cdid => { |
16 | siblings => $cdrs->search ({ artist => $_->get_column('artist') })->count - 1, |
17 | track_titles => [ map { $_->title } ($_->tracks->all) ], |
18 | }, |
19 | } ( $cdrs->all ) }; |
20 | |
4c2b30d6 |
21 | my $c_rs = $cdrs->search ({}, { |
22 | prefetch => 'tracks', |
23 | '+columns' => { sibling_count => $cdrs->search( |
24 | { |
25 | 'siblings.artist' => { -ident => 'me.artist' }, |
0e773352 |
26 | 'siblings.cdid' => { '!=' => ['-and', { -ident => 'me.cdid' }, 23414] }, |
4c2b30d6 |
27 | }, { alias => 'siblings' }, |
28 | )->count_rs->as_query, |
29 | }, |
30 | }); |
31 | |
32 | is_same_sql_bind( |
33 | $c_rs->as_query, |
34 | '( |
35 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
36 | (SELECT COUNT( * ) |
37 | FROM cd siblings |
38 | WHERE siblings.artist = me.artist |
39 | AND siblings.cdid != me.cdid |
40 | AND siblings.cdid != ? |
41 | AND me.artist != ? |
42 | ), |
43 | tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at |
44 | FROM cd me |
45 | LEFT JOIN track tracks |
46 | ON tracks.cd = me.cdid |
47 | WHERE me.artist != ? |
48 | ORDER BY tracks.cd |
49 | )', |
50 | [ |
36fd7f07 |
51 | |
52 | # subselect |
0e773352 |
53 | [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' } |
54 | => 23414 ], |
55 | |
56 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
57 | => 2 ], |
36fd7f07 |
58 | |
59 | # outher WHERE |
0e773352 |
60 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
61 | => 2 ], |
4c2b30d6 |
62 | ], |
63 | 'Expected SQL on correlated realiased subquery' |
64 | ); |
65 | |
36fd7f07 |
66 | my $queries = 0; |
67 | $schema->storage->debugcb(sub { $queries++; }); |
68 | $schema->storage->debug(1); |
69 | |
4c2b30d6 |
70 | is_deeply ( |
71 | { map |
72 | { $_->cdid => { |
73 | track_titles => [ map { $_->title } ($_->tracks->all) ], |
74 | siblings => $_->get_column ('sibling_count'), |
75 | } } |
76 | $c_rs->all |
77 | }, |
78 | $cd_data, |
79 | 'Proper information retrieved from correlated subquery' |
80 | ); |
81 | |
82 | is ($queries, 1, 'Only 1 query fired to retrieve everything'); |
83 | |
84 | $schema->storage->debug($orig_debug); |
85 | $schema->storage->debugcb(undef); |
86 | |
36fd7f07 |
87 | # now add an unbalanced select/as pair |
88 | $c_rs = $c_rs->search ({}, { |
89 | '+select' => $cdrs->search( |
90 | { 'siblings.artist' => { -ident => 'me.artist' } }, |
91 | { alias => 'siblings', columns => [ |
92 | { first_year => { min => 'year' }}, |
93 | { last_year => { max => 'year' }}, |
94 | ]}, |
95 | )->as_query, |
96 | '+as' => [qw/active_from active_to/], |
97 | }); |
98 | |
36fd7f07 |
99 | is_same_sql_bind( |
100 | $c_rs->as_query, |
101 | '( |
102 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
103 | (SELECT COUNT( * ) |
104 | FROM cd siblings |
105 | WHERE siblings.artist = me.artist |
106 | AND siblings.cdid != me.cdid |
107 | AND siblings.cdid != ? |
108 | AND me.artist != ? |
109 | ), |
110 | (SELECT MIN( year ), MAX( year ) |
111 | FROM cd siblings |
112 | WHERE siblings.artist = me.artist |
113 | AND me.artist != ? |
114 | ), |
f7f53a89 |
115 | tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at |
36fd7f07 |
116 | FROM cd me |
117 | LEFT JOIN track tracks |
118 | ON tracks.cd = me.cdid |
119 | WHERE me.artist != ? |
120 | ORDER BY tracks.cd |
121 | )', |
122 | [ |
123 | |
124 | # first subselect |
0e773352 |
125 | [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' } |
126 | => 23414 ], |
127 | |
128 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
129 | => 2 ], |
36fd7f07 |
130 | |
131 | # second subselect |
0e773352 |
132 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
133 | => 2 ], |
36fd7f07 |
134 | |
36fd7f07 |
135 | # outher WHERE |
0e773352 |
136 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
137 | => 2 ], |
36fd7f07 |
138 | ], |
139 | 'Expected SQL on correlated realiased subquery' |
140 | ); |
141 | |
4c2b30d6 |
142 | done_testing; |