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