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 | )', |
57 | [ [ position => 1 ], [ position => 2 ] ], |
58 | 'count_rs db-side limit applied', |
59 | ); |
60 | } |
61 | |
62 | # has_many prefetch with limit |
63 | { |
64 | my $rs = $schema->resultset("Artist") |
65 | ->search_related('cds', |
66 | { 'tracks.position' => [1,2] }, |
67 | { prefetch => [qw/tracks artist/], rows => 3, offset => 4 }, |
68 | ); |
69 | is ($rs->all, 1, 'Correct number of objects'); |
70 | |
71 | my ($sql, @bind); |
72 | $schema->storage->debugobj(DBIC::DebugObj->new(\$sql, \@bind)); |
73 | $schema->storage->debug(1); |
74 | |
75 | is ($rs->count, 1, 'Correct count via count()'); |
76 | |
77 | is_same_sql_bind ( |
78 | $sql, |
79 | \@bind, |
80 | 'SELECT COUNT( * ) |
81 | FROM ( |
82 | SELECT cds.cdid |
83 | FROM artist me |
c98169a7 |
84 | JOIN cd cds ON cds.artist = me.artistid |
a70f69d1 |
85 | LEFT JOIN track tracks ON tracks.cd = cds.cdid |
86 | JOIN artist artist ON artist.artistid = cds.artist |
87 | WHERE tracks.position = ? OR tracks.position = ? |
88 | GROUP BY cds.cdid |
336feb8e |
89 | ) cds |
a70f69d1 |
90 | ', |
91 | [ qw/'1' '2'/ ], |
92 | 'count softlimit applied', |
93 | ); |
94 | |
95 | my $crs = $rs->count_rs; |
96 | is ($crs->next, 1, 'Correct count via count_rs()'); |
97 | |
98 | is_same_sql_bind ( |
99 | $crs->as_query, |
100 | '(SELECT COUNT( * ) |
101 | FROM ( |
102 | SELECT cds.cdid |
103 | FROM artist me |
c98169a7 |
104 | JOIN cd cds ON cds.artist = me.artistid |
a70f69d1 |
105 | LEFT JOIN track tracks ON tracks.cd = cds.cdid |
106 | JOIN artist artist ON artist.artistid = cds.artist |
107 | WHERE tracks.position = ? OR tracks.position = ? |
108 | GROUP BY cds.cdid |
109 | LIMIT 3 OFFSET 4 |
336feb8e |
110 | ) cds |
a70f69d1 |
111 | )', |
112 | [ [ 'tracks.position' => 1 ], [ 'tracks.position' => 2 ] ], |
113 | 'count_rs db-side limit applied', |
114 | ); |
115 | } |
e493ecb2 |
116 | |
117 | # count with a having clause |
118 | { |
119 | my $rs = $schema->resultset("Artist")->search( |
120 | {}, |
121 | { |
122 | join => 'cds', |
123 | group_by => 'me.artistid', |
124 | '+select' => [ { max => 'cds.year', -as => 'newest_cd_year' } ], |
125 | '+as' => ['newest_cd_year'], |
126 | having => { 'newest_cd_year' => '2001' } |
127 | } |
128 | ); |
129 | |
130 | my $crs = $rs->count_rs; |
131 | |
132 | is_same_sql_bind ( |
133 | $crs->as_query, |
134 | '(SELECT COUNT( * ) |
135 | FROM ( |
dc81dba3 |
136 | SELECT me.artistid, MAX( cds.year ) AS newest_cd_year, |
e493ecb2 |
137 | FROM artist me |
138 | LEFT JOIN cd cds ON cds.artist = me.artistid |
139 | GROUP BY me.artistid |
140 | HAVING newest_cd_year = ? |
141 | ) me |
142 | )', |
143 | [ [ 'newest_cd_year' => '2001' ],], |
144 | 'count with having clause keeps sql as alias', |
145 | ); |
146 | |
147 | is ($crs->next, 2, 'Correct artist count (each with one 2001 cd)'); |
148 | } |
149 | |
150 | done_testing; |