1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
|
use strict;
use warnings;
use Test::More;
use Test::Exception;
use lib qw(t/lib);
use DBICTest ':DiffSQL';
my $schema = DBICTest->init_schema();
# The tag Blue is assigned to cds 1 2 3 and 5
# The tag Cheesy is assigned to cds 2 4 and 5
#
# This combination should make some interesting group_by's
#
my $rs;
my $in_rs = $schema->resultset('Tag')->search({ tag => [ 'Blue', 'Cheesy' ] });
for my $get_count (
sub { shift->count },
sub { my $crs = shift->count_rs; isa_ok ($crs, 'DBIx::Class::ResultSetColumn'); $crs->next }
) {
$rs = $schema->resultset('Tag')->search({ tag => 'Blue' });
is($get_count->($rs), 4, 'Count without DISTINCT');
$rs = $schema->resultset('Tag')->search({ tag => [ 'Blue', 'Cheesy' ] }, { group_by => 'tag' });
is($get_count->($rs), 2, 'Count with single column group_by');
$rs = $schema->resultset('Tag')->search({ tag => [ 'Blue', 'Cheesy' ] }, { group_by => 'cd' });
is($get_count->($rs), 5, 'Count with another single column group_by');
$rs = $schema->resultset('Tag')->search({ tag => 'Blue' }, { group_by => [ qw/tag cd/ ]});
is($get_count->($rs), 4, 'Count with multiple column group_by');
$rs = $schema->resultset('Tag')->search({ tag => 'Blue' }, { distinct => 1 });
is($get_count->($rs), 4, 'Count with single column distinct');
$rs = $schema->resultset('Tag')->search({ tag => { -in => $in_rs->get_column('tag')->as_query } });
is($get_count->($rs), 7, 'Count with IN subquery');
$rs = $schema->resultset('Tag')->search({ tag => { -in => $in_rs->get_column('tag')->as_query } }, { group_by => 'tag' });
is($get_count->($rs), 2, 'Count with IN subquery with outside group_by');
$rs = $schema->resultset('Tag')->search({ tag => { -in => $in_rs->get_column('tag')->as_query } }, { distinct => 1 });
is($get_count->($rs), 7, 'Count with IN subquery with outside distinct');
$rs = $schema->resultset('Tag')->search({ tag => { -in => $in_rs->get_column('tag')->as_query } }, { distinct => 1, select => 'tag' }),
is($get_count->($rs), 2, 'Count with IN subquery with outside distinct on a single column');
$rs = $schema->resultset('Tag')->search({ tag => { -in => $in_rs->search({}, { group_by => 'tag' })->get_column('tag')->as_query } });
is($get_count->($rs), 7, 'Count with IN subquery with single group_by');
$rs = $schema->resultset('Tag')->search({ tag => { -in => $in_rs->search({}, { group_by => 'cd' })->get_column('tag')->as_query } });
is($get_count->($rs), 7, 'Count with IN subquery with another single group_by');
$rs = $schema->resultset('Tag')->search({ tag => { -in => $in_rs->search({}, { group_by => [ qw/tag cd/ ] })->get_column('tag')->as_query } });
is($get_count->($rs), 7, 'Count with IN subquery with multiple group_by');
$rs = $schema->resultset('Tag')->search({ tag => \"= 'Blue'" });
is($get_count->($rs), 4, 'Count without DISTINCT, using literal SQL');
$rs = $schema->resultset('Tag')->search({ tag => \" IN ('Blue', 'Cheesy')" }, { group_by => 'tag' });
is($get_count->($rs), 2, 'Count with literal SQL and single group_by');
$rs = $schema->resultset('Tag')->search({ tag => \" IN ('Blue', 'Cheesy')" }, { group_by => 'cd' });
is($get_count->($rs), 5, 'Count with literal SQL and another single group_by');
$rs = $schema->resultset('Tag')->search({ tag => \" IN ('Blue', 'Cheesy')" }, { group_by => [ qw/tag cd/ ] });
is($get_count->($rs), 7, 'Count with literal SQL and multiple group_by');
$rs = $schema->resultset('Tag')->search({ tag => 'Blue' }, { '+select' => { max => 'tagid' }, distinct => 1 });
is($get_count->($rs), 4, 'Count with +select aggreggate');
$rs = $schema->resultset('Tag')->search({}, { select => [\'length(me.tag)'], distinct => 1 });
is($get_count->($rs), 3, 'Count by distinct function result as select literal');
}
throws_ok(
sub { my $row = $schema->resultset('Tag')->search({}, { select => { distinct => [qw/tag cd/] } })->first },
qr/\Qselect => { distinct => ... } syntax is not supported for multiple columns/,
'throw on unsupported syntax'
);
# make sure distinct+func works
{
my $rs = $schema->resultset('Artist')->search(
{},
{
join => 'cds',
distinct => 1,
'+select' => [ { count => 'cds.cdid', -as => 'amount_of_cds' } ],
'+as' => [qw/num_cds/],
order_by => { -desc => 'amount_of_cds' },
}
);
is_same_sql_bind (
$rs->as_query,
'(
SELECT me.artistid, me.name, me.rank, me.charfield, COUNT( cds.cdid ) AS amount_of_cds
FROM artist me LEFT JOIN cd cds ON cds.artist = me.artistid
GROUP BY me.artistid, me.name, me.rank, me.charfield
ORDER BY amount_of_cds DESC
)',
[],
);
is ($rs->next->get_column ('num_cds'), 3, 'Function aliased correctly');
}
# and check distinct has_many join count
{
my $rs = $schema->resultset('Artist')->search(
{ 'cds.title' => { '!=', 'fooooo' } },
{
join => 'cds',
distinct => 1,
'+select' => [ { count => 'cds.cdid', -as => 'amount_of_cds' } ],
'+as' => [qw/num_cds/],
order_by => { -desc => 'amount_of_cds' },
}
);
is_same_sql_bind (
$rs->as_query,
'(
SELECT me.artistid, me.name, me.rank, me.charfield, COUNT( cds.cdid ) AS amount_of_cds
FROM artist me
LEFT JOIN cd cds
ON cds.artist = me.artistid
WHERE cds.title != ?
GROUP BY me.artistid, me.name, me.rank, me.charfield
ORDER BY amount_of_cds DESC
)',
[
[{
sqlt_datatype => 'varchar',
dbic_colname => 'cds.title',
sqlt_size => 100,
} => 'fooooo' ],
],
);
is_same_sql_bind (
$rs->count_rs->as_query,
'(
SELECT COUNT( * )
FROM (
SELECT me.artistid, me.name, me.rank, me.charfield
FROM artist me
LEFT JOIN cd cds
ON cds.artist = me.artistid
WHERE cds.title != ?
GROUP BY me.artistid, me.name, me.rank, me.charfield
) me
)',
[
[{
sqlt_datatype => 'varchar',
dbic_colname => 'cds.title',
sqlt_size => 100,
} => 'fooooo' ],
],
);
is ($rs->next->get_column ('num_cds'), 3, 'Function aliased correctly');
}
# These two rely on the database to throw an exception. This might not be the case one day. Please revise.
dies_ok(sub { my $count = $schema->resultset('Tag')->search({}, { '+select' => \'tagid AS tag_id', distinct => 1 })->count }, 'expecting to die');
done_testing;
|
