7 use DBICTest ':DiffSQL';
9 my $schema = DBICTest->init_schema();
11 # make sure order + distinct do not double-inject group criteria
12 my $rs = $schema->resultset ('CD')->search ({}, {
17 # title + cdid == unique constraint
18 my $unique_rs = $rs->search ({}, {
23 $rs->search({}, { order_by => 'title' })->as_query,
31 'Correct GROUP BY on selection+order_by on same column',
35 $rs->search({}, { order_by => 'year' })->as_query,
43 'Correct GROUP BY on non-unique selection and order by a different column',
47 $unique_rs->search({}, { order_by => 'year' })->as_query,
49 SELECT me.title, me.cdid
51 GROUP BY me.title, me.cdid, me.year
55 'Correct GROUP BY on unique selection and order by a different column',
59 $rs->search({}, { order_by => 'artist.name', join => 'artist' })->as_query,
64 ON artist.artistid = me.artist
66 ORDER BY MIN(artist.name)
69 'Correct GROUP BY on non-unique selection and external single order_by',
73 $unique_rs->search({}, { order_by => 'artist.name', join => 'artist' })->as_query,
75 SELECT me.title, me.cdid
78 ON artist.artistid = me.artist
79 GROUP BY me.title, me.cdid, artist.name
83 'Correct GROUP BY on unique selection and external single order_by',
87 $rs->search({}, { order_by => 'tracks.title', join => 'tracks' })->as_query,
91 LEFT JOIN track tracks
92 ON tracks.cd = me.cdid
94 ORDER BY MIN(tracks.title)
97 'Correct GROUP BY on non-unique selection and external multi order_by',
101 $unique_rs->search({}, { order_by => 'tracks.title', join => 'tracks' })->as_query,
103 SELECT me.title, me.cdid
105 LEFT JOIN track tracks
106 ON tracks.cd = me.cdid
107 GROUP BY me.title, me.cdid
108 ORDER BY MIN(tracks.title)
111 'Correct GROUP BY on unique selection and external multi order_by',