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 | |
4c2b30d6 |
22 | my $c_rs = $cdrs->search ({}, { |
23 | prefetch => 'tracks', |
24 | '+columns' => { sibling_count => $cdrs->search( |
25 | { |
26 | 'siblings.artist' => { -ident => 'me.artist' }, |
0e773352 |
27 | 'siblings.cdid' => { '!=' => ['-and', { -ident => 'me.cdid' }, 23414] }, |
4c2b30d6 |
28 | }, { alias => 'siblings' }, |
29 | )->count_rs->as_query, |
30 | }, |
31 | }); |
32 | |
33 | is_same_sql_bind( |
34 | $c_rs->as_query, |
35 | '( |
36 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
37 | (SELECT COUNT( * ) |
38 | FROM cd siblings |
39 | WHERE siblings.artist = me.artist |
40 | AND siblings.cdid != me.cdid |
41 | AND siblings.cdid != ? |
42 | AND me.artist != ? |
43 | ), |
44 | tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at |
45 | FROM cd me |
46 | LEFT JOIN track tracks |
47 | ON tracks.cd = me.cdid |
48 | WHERE me.artist != ? |
49 | ORDER BY tracks.cd |
50 | )', |
51 | [ |
36fd7f07 |
52 | |
53 | # subselect |
0e773352 |
54 | [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' } |
55 | => 23414 ], |
56 | |
57 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
58 | => 2 ], |
36fd7f07 |
59 | |
60 | # outher WHERE |
0e773352 |
61 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
62 | => 2 ], |
4c2b30d6 |
63 | ], |
64 | 'Expected SQL on correlated realiased subquery' |
65 | ); |
66 | |
36fd7f07 |
67 | my $queries = 0; |
68 | $schema->storage->debugcb(sub { $queries++; }); |
69 | $schema->storage->debug(1); |
70 | |
4c2b30d6 |
71 | is_deeply ( |
72 | { map |
73 | { $_->cdid => { |
74 | track_titles => [ map { $_->title } ($_->tracks->all) ], |
75 | siblings => $_->get_column ('sibling_count'), |
76 | } } |
77 | $c_rs->all |
78 | }, |
79 | $cd_data, |
80 | 'Proper information retrieved from correlated subquery' |
81 | ); |
82 | |
83 | is ($queries, 1, 'Only 1 query fired to retrieve everything'); |
84 | |
85 | $schema->storage->debug($orig_debug); |
86 | $schema->storage->debugcb(undef); |
87 | |
36fd7f07 |
88 | # try to unbalance the select |
89 | |
90 | # first add a lone non-as-ed select |
91 | # it should be reordered to appear at the end without throwing prefetch/bind off |
0e773352 |
92 | $c_rs = $c_rs->search({}, { '+select' => \[ 'me.cdid + ?', [ \ 'inTEger' => 1 ] ] }); |
36fd7f07 |
93 | |
94 | # now add an unbalanced select/as pair |
95 | $c_rs = $c_rs->search ({}, { |
96 | '+select' => $cdrs->search( |
97 | { 'siblings.artist' => { -ident => 'me.artist' } }, |
98 | { alias => 'siblings', columns => [ |
99 | { first_year => { min => 'year' }}, |
100 | { last_year => { max => 'year' }}, |
101 | ]}, |
102 | )->as_query, |
103 | '+as' => [qw/active_from active_to/], |
104 | }); |
105 | |
106 | |
107 | is_same_sql_bind( |
108 | $c_rs->as_query, |
109 | '( |
110 | SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, |
111 | (SELECT COUNT( * ) |
112 | FROM cd siblings |
113 | WHERE siblings.artist = me.artist |
114 | AND siblings.cdid != me.cdid |
115 | AND siblings.cdid != ? |
116 | AND me.artist != ? |
117 | ), |
118 | (SELECT MIN( year ), MAX( year ) |
119 | FROM cd siblings |
120 | WHERE siblings.artist = me.artist |
121 | AND me.artist != ? |
122 | ), |
123 | tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, |
124 | me.cdid + ? |
125 | FROM cd me |
126 | LEFT JOIN track tracks |
127 | ON tracks.cd = me.cdid |
128 | WHERE me.artist != ? |
129 | ORDER BY tracks.cd |
130 | )', |
131 | [ |
132 | |
133 | # first subselect |
0e773352 |
134 | [ { sqlt_datatype => 'integer', dbic_colname => 'siblings.cdid' } |
135 | => 23414 ], |
136 | |
137 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
138 | => 2 ], |
36fd7f07 |
139 | |
140 | # second subselect |
0e773352 |
141 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
142 | => 2 ], |
36fd7f07 |
143 | |
144 | # the addition |
0e773352 |
145 | [ { sqlt_datatype => 'inTEger' } |
146 | => 1 ], |
36fd7f07 |
147 | |
148 | # outher WHERE |
0e773352 |
149 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.artist' } |
150 | => 2 ], |
36fd7f07 |
151 | ], |
152 | 'Expected SQL on correlated realiased subquery' |
153 | ); |
154 | |
4c2b30d6 |
155 | done_testing; |