7 use DBIC::SqlMakerTest;
10 my $schema = DBICTest->init_schema();
12 my $artist_rs = $schema->resultset('Artist');
13 my $ar = $artist_rs->current_source_alias;
15 my $filtered_cd_rs = $artist_rs->search_related('cds_unordered',
18 prefetch => [ 'tracks' ],
19 order_by => [ { -asc => "$ar.name" }, "$ar.artistid DESC" ],
26 $filtered_cd_rs->as_query,
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
32 SELECT cds_unordered.cdid, cds_unordered.artist, cds_unordered.title, cds_unordered.year, cds_unordered.genreid, cds_unordered.single_track
35 ON cds_unordered.artist = me.artistid
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
42 ON cds_unordered.artist = me.artistid
43 LEFT JOIN track tracks
44 ON tracks.cd = cds_unordered.cdid
46 ORDER BY me.name ASC, me.artistid DESC, tracks.cd
48 [ [ 'me.rank' => 13 ], [ 'me.rank' => 13 ] ],
49 'correct SQL on limited prefetch over search_related ordered by root',
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
60 [ $filtered_cd_rs->hri_dump ],
66 'single_track' => undef,
67 'title' => 'Generic Manufactured Singles',
71 'last_updated_at' => undef,
72 'last_updated_on' => undef,
75 'title' => 'Boring Name',
80 'last_updated_at' => undef,
81 'last_updated_on' => undef,
84 'title' => 'Boring Song',
89 'last_updated_at' => undef,
90 'last_updated_on' => undef,
93 'title' => 'No More Ideas',
103 'single_track' => undef,
104 'title' => 'Come Be Depressed With Us',
108 'last_updated_at' => undef,
109 'last_updated_on' => undef,
117 'last_updated_at' => undef,
118 'last_updated_on' => undef,
121 'title' => 'Suicidal',
126 'last_updated_at' => undef,
127 'last_updated_on' => undef,
130 'title' => 'Under The Weather',
137 'Correctly ordered result',