Commit | Line | Data |
c0329273 |
1 | BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) } |
2 | |
0a3441ee |
3 | use strict; |
4 | use warnings; |
5 | |
6 | use Test::More; |
7 | |
c0329273 |
8 | |
a5a7bb73 |
9 | use DBICTest ':DiffSQL'; |
fcb7fcbb |
10 | use DBIx::Class::SQLMaker::LimitDialects; |
11 | |
12 | my ($ROWS, $OFFSET) = ( |
13 | DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype, |
14 | DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype, |
15 | ); |
0a3441ee |
16 | |
e6977bbb |
17 | my $schema = DBICTest->init_schema(quote_names => 1); |
0a3441ee |
18 | |
19 | my $artist_rs = $schema->resultset('Artist'); |
0a3441ee |
20 | |
21 | my $filtered_cd_rs = $artist_rs->search_related('cds_unordered', |
1e4f9fb3 |
22 | { "me.rank" => 13 }, |
0a3441ee |
23 | { |
1e4f9fb3 |
24 | prefetch => 'tracks', |
25 | join => 'genre', |
e6977bbb |
26 | order_by => [ { -desc => 'genre.name' }, { -desc => \ 'tracks.title' }, { -asc => "me.name" }, { -desc => [qw(year cds_unordered.title)] } ], # me. is the artist, *NOT* the cd |
0a3441ee |
27 | }, |
28 | ); |
29 | |
1e4f9fb3 |
30 | my $hri_contents = [ |
31 | { |
32 | artist => 1, cdid => 1, genreid => 1, single_track => undef, title => "Spoonful of bees", year => 1999, tracks => [ |
33 | { cd => 1, last_updated_at => undef, last_updated_on => undef, position => 1, title => "The Bees Knees", trackid => 16 }, |
34 | { cd => 1, last_updated_at => undef, last_updated_on => undef, position => 3, title => "Beehind You", trackid => 18 }, |
35 | { cd => 1, last_updated_at => undef, last_updated_on => undef, position => 2, title => "Apiary", trackid => 17 }, |
36 | ], |
37 | }, |
38 | { |
39 | artist => 1, cdid => 3, genreid => undef, single_track => undef, title => "Caterwaulin' Blues", year => 1997, tracks => [ |
40 | { cd => 3, last_updated_at => undef, last_updated_on => undef, position => 1, title => "Yowlin", trackid => 7 }, |
41 | { cd => 3, last_updated_at => undef, last_updated_on => undef, position => 2, title => "Howlin", trackid => 8 }, |
42 | { cd => 3, last_updated_at => undef, last_updated_on => undef, position => 3, title => "Fowlin", trackid => 9 }, |
43 | ], |
44 | }, |
45 | { |
46 | artist => 3, cdid => 5, genreid => undef, single_track => undef, title => "Come Be Depressed With Us", year => 1998, tracks => [ |
47 | { cd => 5, last_updated_at => undef, last_updated_on => undef, position => 2, title => "Under The Weather", trackid => 14 }, |
48 | { cd => 5, last_updated_at => undef, last_updated_on => undef, position => 3, title => "Suicidal", trackid => 15 }, |
49 | { cd => 5, last_updated_at => undef, last_updated_on => undef, position => 1, title => "Sad", trackid => 13 }, |
50 | ], |
51 | }, |
52 | { |
53 | artist => 1, cdid => 2, genreid => undef, single_track => undef, title => "Forkful of bees", year => 2001, tracks => [ |
54 | { cd => 2, last_updated_at => undef, last_updated_on => undef, position => 1, title => "Stung with Success", trackid => 4 }, |
55 | { cd => 2, last_updated_at => undef, last_updated_on => undef, position => 2, title => "Stripy", trackid => 5 }, |
56 | { cd => 2, last_updated_at => undef, last_updated_on => undef, position => 3, title => "Sticky Honey", trackid => 6 }, |
57 | ], |
58 | }, |
59 | { |
60 | artist => 2, cdid => 4, genreid => undef, single_track => undef, title => "Generic Manufactured Singles", year => 2001, tracks => [ |
61 | { cd => 4, last_updated_at => undef, last_updated_on => undef, position => 3, title => "No More Ideas", trackid => 12 }, |
62 | { cd => 4, last_updated_at => undef, last_updated_on => undef, position => 2, title => "Boring Song", trackid => 11 }, |
63 | { cd => 4, last_updated_at => undef, last_updated_on => undef, position => 1, title => "Boring Name", trackid => 10}, |
64 | ], |
65 | }, |
66 | ]; |
0a3441ee |
67 | |
1e4f9fb3 |
68 | is_deeply( |
13fa2937 |
69 | $filtered_cd_rs->all_hri, |
1e4f9fb3 |
70 | $hri_contents, |
71 | 'Expected ordered unlimited contents', |
0a3441ee |
72 | ); |
73 | |
1e4f9fb3 |
74 | for ( |
75 | [ 0, 1 ], |
76 | [ 2, 0 ], |
77 | [ 20, 2 ], |
78 | [ 1, 3 ], |
79 | [ 2, 4 ], |
80 | ) { |
81 | my ($limit, $offset) = @$_; |
82 | |
83 | my $rs = $filtered_cd_rs->search({}, { $limit ? (rows => $limit) : (), offset => $offset }); |
84 | |
85 | my $used_limit = $limit || DBIx::Class::SQLMaker->__max_int; |
86 | my $offset_str = $offset ? 'OFFSET ?' : ''; |
87 | |
88 | is_same_sql_bind( |
89 | $rs->as_query, |
e6977bbb |
90 | qq{( |
91 | SELECT "cds_unordered"."cdid", "cds_unordered"."artist", "cds_unordered"."title", "cds_unordered"."year", "cds_unordered"."genreid", "cds_unordered"."single_track", |
92 | "tracks"."trackid", "tracks"."cd", "tracks"."position", "tracks"."title", "tracks"."last_updated_on", "tracks"."last_updated_at" |
93 | FROM "artist" "me" |
1e4f9fb3 |
94 | JOIN ( |
e6977bbb |
95 | SELECT "cds_unordered"."cdid", "cds_unordered"."artist", "cds_unordered"."title", "cds_unordered"."year", "cds_unordered"."genreid", "cds_unordered"."single_track" |
96 | FROM "artist" "me" |
97 | JOIN cd "cds_unordered" |
98 | ON "cds_unordered"."artist" = "me"."artistid" |
99 | LEFT JOIN "genre" "genre" |
100 | ON "genre"."genreid" = "cds_unordered"."genreid" |
101 | LEFT JOIN "track" "tracks" |
102 | ON "tracks"."cd" = "cds_unordered"."cdid" |
103 | WHERE "me"."rank" = ? |
eb58c082 |
104 | GROUP BY "cds_unordered"."cdid", "cds_unordered"."artist", "cds_unordered"."title", "cds_unordered"."year", "cds_unordered"."genreid", "cds_unordered"."single_track", "me"."name" |
e6977bbb |
105 | ORDER BY MAX("genre"."name") DESC, |
106 | MAX( tracks.title ) DESC, |
eb58c082 |
107 | "me"."name" ASC, |
e6977bbb |
108 | "year" DESC, |
109 | "cds_unordered"."title" DESC |
1e4f9fb3 |
110 | LIMIT ? |
111 | $offset_str |
e6977bbb |
112 | ) "cds_unordered" |
113 | ON "cds_unordered"."artist" = "me"."artistid" |
114 | LEFT JOIN "genre" "genre" |
115 | ON "genre"."genreid" = "cds_unordered"."genreid" |
116 | LEFT JOIN "track" "tracks" |
117 | ON "tracks"."cd" = "cds_unordered"."cdid" |
118 | WHERE "me"."rank" = ? |
119 | ORDER BY "genre"."name" DESC, |
120 | tracks.title DESC, |
121 | "me"."name" ASC, |
122 | "year" DESC, |
123 | "cds_unordered"."title" DESC |
124 | )}, |
1e4f9fb3 |
125 | [ |
126 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' } => 13 ], |
127 | [ $ROWS => $used_limit ], |
128 | $offset ? [ $OFFSET => $offset ] : (), |
129 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' } => 13 ], |
130 | ], |
131 | "correct SQL on prefetch over search_related ordered by external joins with limit '$limit', offset '$offset'", |
132 | ); |
133 | |
134 | is_deeply( |
135 | $rs->all_hri, |
136 | [ @{$hri_contents}[$offset .. List::Util::min( $used_limit+$offset-1, $#$hri_contents)] ], |
137 | "Correct slice of the resultset returned with limit '$limit', offset '$offset'", |
138 | ); |
139 | } |
140 | |
0a3441ee |
141 | done_testing; |