1use strict;
2use warnings;
3
4use lib qw(t/lib);
5
6use Test::More;
7use DBICTest;
8use DBIC::SqlMakerTest;
9use DBIC::DebugObj;
10
11plan tests => 10;
12
13my $schema = DBICTest->init_schema();
14
15# non-collapsing prefetch (no multi prefetches)
16{
17  my $rs = $schema->resultset("CD")
18            ->search_related('tracks',
19                { position => [1,2] },
20                { prefetch => [qw/disc lyrics/], rows => 3, offset => 8 },
21            );
22  is ($rs->all, 2, 'Correct number of objects');
23
24
25  my ($sql, @bind);
26  $schema->storage->debugobj(DBIC::DebugObj->new(\$sql, \@bind));
27  $schema->storage->debug(1);
28
29  is ($rs->count, 2, 'Correct count via count()');
30
31  is_same_sql_bind (
32    $sql,
33    \@bind,
34    'SELECT COUNT( * )
35      FROM cd me
36      JOIN track tracks ON tracks.cd = me.cdid
37      JOIN cd disc ON disc.cdid = tracks.cd
38     WHERE ( ( position = ? OR position = ? ) )
39    ',
40    [ qw/'1' '2'/ ],
41    'count softlimit applied',
42  );
43
44  my $crs = $rs->count_rs;
45  is ($crs->next, 2, 'Correct count via count_rs()');
46
47  is_same_sql_bind (
48    $crs->as_query,
49    '(SELECT COUNT( * )
50       FROM (
51        SELECT tracks.trackid
52          FROM cd me
53          JOIN track tracks ON tracks.cd = me.cdid
54          JOIN cd disc ON disc.cdid = tracks.cd
55        WHERE ( ( position = ? OR position = ? ) )
56        LIMIT 3 OFFSET 8
57       ) count_subq
58    )',
59    [ [ position => 1 ], [ position => 2 ] ],
60    'count_rs db-side limit applied',
61  );
62}
63
64# has_many prefetch with limit
65{
66  my $rs = $schema->resultset("Artist")
67            ->search_related('cds',
68                { 'tracks.position' => [1,2] },
69                { prefetch => [qw/tracks artist/], rows => 3, offset => 4 },
70            );
71  is ($rs->all, 1, 'Correct number of objects');
72
73  my ($sql, @bind);
74  $schema->storage->debugobj(DBIC::DebugObj->new(\$sql, \@bind));
75  $schema->storage->debug(1);
76
77  is ($rs->count, 1, 'Correct count via count()');
78
79  is_same_sql_bind (
80    $sql,
81    \@bind,
82    'SELECT COUNT( * )
83      FROM (
84        SELECT cds.cdid
85          FROM artist me
86          JOIN cd cds ON cds.artist = me.artistid
87          LEFT JOIN track tracks ON tracks.cd = cds.cdid
88          JOIN artist artist ON artist.artistid = cds.artist
89        WHERE tracks.position = ? OR tracks.position = ?
90        GROUP BY cds.cdid
91      ) count_subq
92    ',
93    [ qw/'1' '2'/ ],
94    'count softlimit applied',
95  );
96
97  my $crs = $rs->count_rs;
98  is ($crs->next, 1, 'Correct count via count_rs()');
99
100  is_same_sql_bind (
101    $crs->as_query,
102    '(SELECT COUNT( * )
103      FROM (
104        SELECT cds.cdid
105          FROM artist me
106          JOIN cd cds ON cds.artist = me.artistid
107          LEFT JOIN track tracks ON tracks.cd = cds.cdid
108          JOIN artist artist ON artist.artistid = cds.artist
109        WHERE tracks.position = ? OR tracks.position = ?
110        GROUP BY cds.cdid
111        LIMIT 3 OFFSET 4
112      ) count_subq
113    )',
114    [ [ 'tracks.position' => 1 ], [ 'tracks.position' => 2 ] ],
115    'count_rs db-side limit applied',
116  );
117}
118