7 use DBIC::SqlMakerTest;
9 use DBIx::Class::SQLMaker::LimitDialects;
11 my ($ROWS, $OFFSET) = (
12 DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
13 DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
16 my $schema = DBICTest->init_schema();
18 my $artist_rs = $schema->resultset('Artist');
19 my $ar = $artist_rs->current_source_alias;
21 my $filtered_cd_rs = $artist_rs->search_related('cds_unordered',
24 prefetch => [ 'tracks' ],
25 order_by => [ 'tracks.position DESC', { -asc => "$ar.name" }, "$ar.artistid DESC" ],
32 $filtered_cd_rs->as_query,
34 SELECT cds_unordered.cdid, cds_unordered.artist, cds_unordered.title, cds_unordered.year, cds_unordered.genreid, cds_unordered.single_track,
35 tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at
38 SELECT cds_unordered.cdid, cds_unordered.artist, cds_unordered.title, cds_unordered.year, cds_unordered.genreid, cds_unordered.single_track
41 ON cds_unordered.artist = me.artistid
42 LEFT JOIN track tracks
43 ON tracks.cd = cds_unordered.cdid
45 ORDER BY tracks.position DESC, me.name ASC, me.artistid DESC
49 ON cds_unordered.artist = me.artistid
50 LEFT JOIN track tracks
51 ON tracks.cd = cds_unordered.cdid
53 ORDER BY tracks.position DESC, me.name ASC, me.artistid DESC
56 [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' } => 13 ],
59 [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' } => 13 ],
61 'correct SQL on limited prefetch over search_related ordered by root',
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
72 $filtered_cd_rs->all_hri,
78 'single_track' => undef,
79 'title' => 'Generic Manufactured Singles',
83 'last_updated_at' => undef,
84 'last_updated_on' => undef,
86 'title' => 'No More Ideas',
91 'last_updated_at' => undef,
92 'last_updated_on' => undef,
94 'title' => 'Boring Song',
99 'last_updated_at' => undef,
100 'last_updated_on' => undef,
102 'title' => 'Boring Name',
112 'single_track' => undef,
113 'title' => 'Come Be Depressed With Us',
117 'last_updated_at' => undef,
118 'last_updated_on' => undef,
120 'title' => 'Suicidal',
125 'last_updated_at' => undef,
126 'last_updated_on' => undef,
128 'title' => 'Under The Weather',
133 'last_updated_at' => undef,
134 'last_updated_on' => undef,
143 'Correctly ordered result',