Commit | Line | Data |
0a3441ee |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | |
6 | use lib qw(t/lib); |
7 | use DBIC::SqlMakerTest; |
8 | use DBICTest; |
fcb7fcbb |
9 | use DBIx::Class::SQLMaker::LimitDialects; |
10 | |
11 | my ($ROWS, $OFFSET) = ( |
12 | DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype, |
13 | DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype, |
14 | ); |
0a3441ee |
15 | |
16 | my $schema = DBICTest->init_schema(); |
17 | |
18 | my $artist_rs = $schema->resultset('Artist'); |
19 | my $ar = $artist_rs->current_source_alias; |
20 | |
21 | my $filtered_cd_rs = $artist_rs->search_related('cds_unordered', |
22 | { "$ar.rank" => 13 }, |
23 | { |
24 | prefetch => [ 'tracks' ], |
25 | order_by => [ { -asc => "$ar.name" }, "$ar.artistid DESC" ], |
26 | offset => 3, |
27 | rows => 3, |
28 | }, |
29 | ); |
30 | |
31 | is_same_sql_bind( |
32 | $filtered_cd_rs->as_query, |
33 | q{( |
34 | SELECT cds_unordered.cdid, cds_unordered.artist, cds_unordered.title, cds_unordered.year, cds_unordered.genreid, cds_unordered.single_track, |
3d98c75e |
35 | tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at |
0a3441ee |
36 | FROM artist me |
37 | JOIN ( |
38 | SELECT cds_unordered.cdid, cds_unordered.artist, cds_unordered.title, cds_unordered.year, cds_unordered.genreid, cds_unordered.single_track |
39 | FROM artist me |
40 | JOIN cd cds_unordered |
41 | ON cds_unordered.artist = me.artistid |
42 | WHERE ( me.rank = ? ) |
0a3441ee |
43 | ORDER BY me.name ASC, me.artistid DESC |
fcb7fcbb |
44 | LIMIT ? |
45 | OFFSET ? |
0a3441ee |
46 | ) cds_unordered |
47 | ON cds_unordered.artist = me.artistid |
48 | LEFT JOIN track tracks |
49 | ON tracks.cd = cds_unordered.cdid |
50 | WHERE ( me.rank = ? ) |
51 | ORDER BY me.name ASC, me.artistid DESC, tracks.cd |
52 | )}, |
fcb7fcbb |
53 | [ |
54 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' } => 13 ], |
55 | [ $ROWS => 3 ], |
56 | [ $OFFSET => 3 ], |
57 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' } => 13 ], |
0e773352 |
58 | ], |
0a3441ee |
59 | 'correct SQL on limited prefetch over search_related ordered by root', |
60 | ); |
61 | |
62 | # note: we only requested "get all cds of all artists with rank 13 then order |
63 | # by the artist name and give me the fourth, fifth and sixth", consequently the |
64 | # cds that belong to the same artist are unordered; fortunately we know that |
65 | # the first artist have 3 cds and the second and third artist both have only |
66 | # one, so the first 3 cds belong to the first artist and the fourth and fifth |
67 | # cds belong to the second and third artist, respectively, and there's no sixth |
68 | # row |
69 | is_deeply ( |
70 | [ $filtered_cd_rs->hri_dump ], |
71 | [ |
72 | { |
73 | 'artist' => '2', |
74 | 'cdid' => '4', |
75 | 'genreid' => undef, |
76 | 'single_track' => undef, |
77 | 'title' => 'Generic Manufactured Singles', |
78 | 'tracks' => [ |
79 | { |
80 | 'cd' => '4', |
81 | 'last_updated_at' => undef, |
82 | 'last_updated_on' => undef, |
83 | 'position' => '1', |
0a3441ee |
84 | 'title' => 'Boring Name', |
85 | 'trackid' => '10' |
86 | }, |
87 | { |
88 | 'cd' => '4', |
89 | 'last_updated_at' => undef, |
90 | 'last_updated_on' => undef, |
91 | 'position' => '2', |
0a3441ee |
92 | 'title' => 'Boring Song', |
93 | 'trackid' => '11' |
94 | }, |
95 | { |
96 | 'cd' => '4', |
97 | 'last_updated_at' => undef, |
98 | 'last_updated_on' => undef, |
99 | 'position' => '3', |
0a3441ee |
100 | 'title' => 'No More Ideas', |
101 | 'trackid' => '12' |
102 | } |
103 | ], |
104 | 'year' => '2001' |
105 | }, |
106 | { |
107 | 'artist' => '3', |
108 | 'cdid' => '5', |
109 | 'genreid' => undef, |
110 | 'single_track' => undef, |
111 | 'title' => 'Come Be Depressed With Us', |
112 | 'tracks' => [ |
113 | { |
114 | 'cd' => '5', |
115 | 'last_updated_at' => undef, |
116 | 'last_updated_on' => undef, |
117 | 'position' => '1', |
0a3441ee |
118 | 'title' => 'Sad', |
119 | 'trackid' => '13' |
120 | }, |
121 | { |
122 | 'cd' => '5', |
123 | 'last_updated_at' => undef, |
124 | 'last_updated_on' => undef, |
125 | 'position' => '3', |
0a3441ee |
126 | 'title' => 'Suicidal', |
127 | 'trackid' => '15' |
128 | }, |
129 | { |
130 | 'cd' => '5', |
131 | 'last_updated_at' => undef, |
132 | 'last_updated_on' => undef, |
133 | 'position' => '2', |
0a3441ee |
134 | 'title' => 'Under The Weather', |
135 | 'trackid' => '14' |
136 | } |
137 | ], |
138 | 'year' => '1998' |
139 | } |
140 | ], |
141 | 'Correctly ordered result', |
142 | ); |
143 | |
144 | done_testing; |