Commit | Line | Data |
a70f69d1 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use lib qw(t/lib); |
5 | |
6 | use Test::More; |
7 | use DBICTest; |
8 | use DBIC::SqlMakerTest; |
9 | use DBIC::DebugObj; |
10 | |
a70f69d1 |
11 | my $schema = DBICTest->init_schema(); |
12 | |
13 | # non-collapsing prefetch (no multi prefetches) |
14 | { |
15 | my $rs = $schema->resultset("CD") |
16 | ->search_related('tracks', |
17 | { position => [1,2] }, |
18 | { prefetch => [qw/disc lyrics/], rows => 3, offset => 8 }, |
19 | ); |
20 | is ($rs->all, 2, 'Correct number of objects'); |
21 | |
22 | |
23 | my ($sql, @bind); |
24 | $schema->storage->debugobj(DBIC::DebugObj->new(\$sql, \@bind)); |
25 | $schema->storage->debug(1); |
26 | |
27 | is ($rs->count, 2, 'Correct count via count()'); |
28 | |
29 | is_same_sql_bind ( |
30 | $sql, |
31 | \@bind, |
32 | 'SELECT COUNT( * ) |
33 | FROM cd me |
c98169a7 |
34 | JOIN track tracks ON tracks.cd = me.cdid |
a70f69d1 |
35 | JOIN cd disc ON disc.cdid = tracks.cd |
a70f69d1 |
36 | WHERE ( ( position = ? OR position = ? ) ) |
37 | ', |
38 | [ qw/'1' '2'/ ], |
39 | 'count softlimit applied', |
40 | ); |
41 | |
42 | my $crs = $rs->count_rs; |
43 | is ($crs->next, 2, 'Correct count via count_rs()'); |
44 | |
45 | is_same_sql_bind ( |
46 | $crs->as_query, |
47 | '(SELECT COUNT( * ) |
48 | FROM ( |
49 | SELECT tracks.trackid |
50 | FROM cd me |
c98169a7 |
51 | JOIN track tracks ON tracks.cd = me.cdid |
a70f69d1 |
52 | JOIN cd disc ON disc.cdid = tracks.cd |
a70f69d1 |
53 | WHERE ( ( position = ? OR position = ? ) ) |
54 | LIMIT 3 OFFSET 8 |
336feb8e |
55 | ) tracks |
a70f69d1 |
56 | )', |
0e773352 |
57 | [ |
58 | [ { sqlt_datatype => 'int', dbic_colname => 'position' } |
59 | => 1 ], |
60 | [ { sqlt_datatype => 'int', dbic_colname => 'position' } |
61 | => 2 ], |
62 | ], |
a70f69d1 |
63 | 'count_rs db-side limit applied', |
64 | ); |
65 | } |
66 | |
67 | # has_many prefetch with limit |
68 | { |
69 | my $rs = $schema->resultset("Artist") |
70 | ->search_related('cds', |
71 | { 'tracks.position' => [1,2] }, |
72 | { prefetch => [qw/tracks artist/], rows => 3, offset => 4 }, |
73 | ); |
74 | is ($rs->all, 1, 'Correct number of objects'); |
75 | |
76 | my ($sql, @bind); |
77 | $schema->storage->debugobj(DBIC::DebugObj->new(\$sql, \@bind)); |
78 | $schema->storage->debug(1); |
79 | |
80 | is ($rs->count, 1, 'Correct count via count()'); |
81 | |
82 | is_same_sql_bind ( |
83 | $sql, |
84 | \@bind, |
85 | 'SELECT COUNT( * ) |
86 | FROM ( |
87 | SELECT cds.cdid |
88 | FROM artist me |
c98169a7 |
89 | JOIN cd cds ON cds.artist = me.artistid |
a70f69d1 |
90 | LEFT JOIN track tracks ON tracks.cd = cds.cdid |
91 | JOIN artist artist ON artist.artistid = cds.artist |
92 | WHERE tracks.position = ? OR tracks.position = ? |
93 | GROUP BY cds.cdid |
336feb8e |
94 | ) cds |
a70f69d1 |
95 | ', |
96 | [ qw/'1' '2'/ ], |
97 | 'count softlimit applied', |
98 | ); |
99 | |
100 | my $crs = $rs->count_rs; |
101 | is ($crs->next, 1, 'Correct count via count_rs()'); |
102 | |
103 | is_same_sql_bind ( |
104 | $crs->as_query, |
105 | '(SELECT COUNT( * ) |
106 | FROM ( |
107 | SELECT cds.cdid |
108 | FROM artist me |
c98169a7 |
109 | JOIN cd cds ON cds.artist = me.artistid |
a70f69d1 |
110 | LEFT JOIN track tracks ON tracks.cd = cds.cdid |
111 | JOIN artist artist ON artist.artistid = cds.artist |
112 | WHERE tracks.position = ? OR tracks.position = ? |
113 | GROUP BY cds.cdid |
114 | LIMIT 3 OFFSET 4 |
336feb8e |
115 | ) cds |
a70f69d1 |
116 | )', |
0e773352 |
117 | [ |
118 | [ { sqlt_datatype => 'int', dbic_colname => 'tracks.position' } |
119 | => 1 ], |
120 | [ { sqlt_datatype => 'int', dbic_colname => 'tracks.position' } |
121 | => 2 ], |
122 | ], |
a70f69d1 |
123 | 'count_rs db-side limit applied', |
124 | ); |
125 | } |
e493ecb2 |
126 | |
127 | # count with a having clause |
128 | { |
129 | my $rs = $schema->resultset("Artist")->search( |
130 | {}, |
131 | { |
132 | join => 'cds', |
133 | group_by => 'me.artistid', |
134 | '+select' => [ { max => 'cds.year', -as => 'newest_cd_year' } ], |
135 | '+as' => ['newest_cd_year'], |
136 | having => { 'newest_cd_year' => '2001' } |
137 | } |
138 | ); |
139 | |
140 | my $crs = $rs->count_rs; |
141 | |
142 | is_same_sql_bind ( |
143 | $crs->as_query, |
144 | '(SELECT COUNT( * ) |
145 | FROM ( |
dc81dba3 |
146 | SELECT me.artistid, MAX( cds.year ) AS newest_cd_year, |
e493ecb2 |
147 | FROM artist me |
148 | LEFT JOIN cd cds ON cds.artist = me.artistid |
149 | GROUP BY me.artistid |
150 | HAVING newest_cd_year = ? |
151 | ) me |
152 | )', |
0e773352 |
153 | [ [ { dbic_colname => 'newest_cd_year' } |
154 | => '2001' ] ], |
e493ecb2 |
155 | 'count with having clause keeps sql as alias', |
156 | ); |
157 | |
158 | is ($crs->next, 2, 'Correct artist count (each with one 2001 cd)'); |
159 | } |
160 | |
161 | done_testing; |