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' ], |
908aa1bb |
25 | order_by => [ 'tracks.position DESC', { -asc => "$ar.name" }, "$ar.artistid DESC" ], |
26 | offset => 13, |
0a3441ee |
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 |
908aa1bb |
42 | LEFT JOIN track tracks |
43 | ON tracks.cd = cds_unordered.cdid |
0a3441ee |
44 | WHERE ( me.rank = ? ) |
908aa1bb |
45 | ORDER BY tracks.position DESC, me.name ASC, me.artistid DESC |
fcb7fcbb |
46 | LIMIT ? |
47 | OFFSET ? |
0a3441ee |
48 | ) cds_unordered |
49 | ON cds_unordered.artist = me.artistid |
50 | LEFT JOIN track tracks |
51 | ON tracks.cd = cds_unordered.cdid |
52 | WHERE ( me.rank = ? ) |
908aa1bb |
53 | ORDER BY tracks.position DESC, me.name ASC, me.artistid DESC |
0a3441ee |
54 | )}, |
fcb7fcbb |
55 | [ |
56 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' } => 13 ], |
57 | [ $ROWS => 3 ], |
908aa1bb |
58 | [ $OFFSET => 13 ], |
fcb7fcbb |
59 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' } => 13 ], |
0e773352 |
60 | ], |
0a3441ee |
61 | 'correct SQL on limited prefetch over search_related ordered by root', |
62 | ); |
63 | |
64 | # note: we only requested "get all cds of all artists with rank 13 then order |
65 | # by the artist name and give me the fourth, fifth and sixth", consequently the |
66 | # cds that belong to the same artist are unordered; fortunately we know that |
67 | # the first artist have 3 cds and the second and third artist both have only |
68 | # one, so the first 3 cds belong to the first artist and the fourth and fifth |
69 | # cds belong to the second and third artist, respectively, and there's no sixth |
70 | # row |
71 | is_deeply ( |
13fa2937 |
72 | $filtered_cd_rs->all_hri, |
0a3441ee |
73 | [ |
74 | { |
75 | 'artist' => '2', |
76 | 'cdid' => '4', |
77 | 'genreid' => undef, |
78 | 'single_track' => undef, |
79 | 'title' => 'Generic Manufactured Singles', |
80 | 'tracks' => [ |
81 | { |
82 | 'cd' => '4', |
83 | 'last_updated_at' => undef, |
84 | 'last_updated_on' => undef, |
908aa1bb |
85 | 'position' => '3', |
86 | 'title' => 'No More Ideas', |
87 | 'trackid' => '12' |
0a3441ee |
88 | }, |
89 | { |
90 | 'cd' => '4', |
91 | 'last_updated_at' => undef, |
92 | 'last_updated_on' => undef, |
93 | 'position' => '2', |
0a3441ee |
94 | 'title' => 'Boring Song', |
95 | 'trackid' => '11' |
96 | }, |
97 | { |
98 | 'cd' => '4', |
99 | 'last_updated_at' => undef, |
100 | 'last_updated_on' => undef, |
908aa1bb |
101 | 'position' => '1', |
102 | 'title' => 'Boring Name', |
103 | 'trackid' => '10' |
0a3441ee |
104 | } |
105 | ], |
106 | 'year' => '2001' |
107 | }, |
108 | { |
109 | 'artist' => '3', |
110 | 'cdid' => '5', |
111 | 'genreid' => undef, |
112 | 'single_track' => undef, |
113 | 'title' => 'Come Be Depressed With Us', |
114 | 'tracks' => [ |
115 | { |
116 | 'cd' => '5', |
117 | 'last_updated_at' => undef, |
118 | 'last_updated_on' => undef, |
0a3441ee |
119 | 'position' => '3', |
0a3441ee |
120 | 'title' => 'Suicidal', |
121 | 'trackid' => '15' |
122 | }, |
123 | { |
124 | 'cd' => '5', |
125 | 'last_updated_at' => undef, |
126 | 'last_updated_on' => undef, |
127 | 'position' => '2', |
0a3441ee |
128 | 'title' => 'Under The Weather', |
129 | 'trackid' => '14' |
908aa1bb |
130 | }, |
131 | { |
132 | 'cd' => '5', |
133 | 'last_updated_at' => undef, |
134 | 'last_updated_on' => undef, |
135 | 'position' => '1', |
136 | 'title' => 'Sad', |
137 | 'trackid' => '13' |
0a3441ee |
138 | } |
139 | ], |
140 | 'year' => '1998' |
141 | } |
142 | ], |
143 | 'Correctly ordered result', |
144 | ); |
145 | |
146 | done_testing; |