1 BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
9 use DBICTest ':DiffSQL';
10 use DBIx::Class::SQLMaker::LimitDialects;
12 my ($ROWS, $OFFSET) = (
13 DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
14 DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
17 my $schema = DBICTest->init_schema(quote_names => 1);
19 my $artist_rs = $schema->resultset('Artist');
21 my $filtered_cd_rs = $artist_rs->search_related('cds_unordered',
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
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 },
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 },
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 },
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 },
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},
69 $filtered_cd_rs->all_hri,
71 'Expected ordered unlimited contents',
81 my ($limit, $offset) = @$_;
83 my $rs = $filtered_cd_rs->search({}, { $limit ? (rows => $limit) : (), offset => $offset });
85 my $used_limit = $limit || DBIx::Class::SQLMaker->__max_int;
86 my $offset_str = $offset ? 'OFFSET ?' : '';
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"
95 SELECT "cds_unordered"."cdid", "cds_unordered"."artist", "cds_unordered"."title", "cds_unordered"."year", "cds_unordered"."genreid", "cds_unordered"."single_track"
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" = ?
104 GROUP BY "cds_unordered"."cdid", "cds_unordered"."artist", "cds_unordered"."title", "cds_unordered"."year", "cds_unordered"."genreid", "cds_unordered"."single_track", "me"."name"
105 ORDER BY MAX("genre"."name") DESC,
106 MAX( tracks.title ) DESC,
109 "cds_unordered"."title" DESC
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,
123 "cds_unordered"."title" DESC
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 ],
131 "correct SQL on prefetch over search_related ordered by external joins with limit '$limit', offset '$offset'",
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'",