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