Commit | Line | Data |
d955e938 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
d955e938 |
5 | |
6 | use lib qw(t/lib); |
a5a7bb73 |
7 | use DBICTest ':DiffSQL'; |
d955e938 |
8 | |
9 | my $schema = DBICTest->init_schema(); |
10 | |
11 | # make sure order + distinct do not double-inject group criteria |
eb58c082 |
12 | my $rs = $schema->resultset ('CD')->search ({}, { |
d955e938 |
13 | distinct => 1, |
eb58c082 |
14 | columns => 'title', |
d955e938 |
15 | }); |
16 | |
eb58c082 |
17 | # title + cdid == unique constraint |
18 | my $unique_rs = $rs->search ({}, { |
19 | '+columns' => 'cdid', |
20 | }); |
21 | |
22 | is_same_sql_bind ( |
23 | $rs->search({}, { order_by => 'title' })->as_query, |
24 | '( |
25 | SELECT me.title |
26 | FROM cd me |
27 | GROUP BY me.title |
28 | ORDER BY title |
29 | )', |
30 | [], |
31 | 'Correct GROUP BY on selection+order_by on same column', |
32 | ); |
33 | |
34 | is_same_sql_bind ( |
35 | $rs->search({}, { order_by => 'year' })->as_query, |
36 | '( |
37 | SELECT me.title |
38 | FROM cd me |
39 | GROUP BY me.title |
40 | ORDER BY MIN(year) |
41 | )', |
42 | [], |
43 | 'Correct GROUP BY on non-unique selection and order by a different column', |
44 | ); |
45 | |
d955e938 |
46 | is_same_sql_bind ( |
eb58c082 |
47 | $unique_rs->search({}, { order_by => 'year' })->as_query, |
d955e938 |
48 | '( |
eb58c082 |
49 | SELECT me.title, me.cdid |
d955e938 |
50 | FROM cd me |
eb58c082 |
51 | GROUP BY me.title, me.cdid, me.year |
d955e938 |
52 | ORDER BY year |
53 | )', |
54 | [], |
eb58c082 |
55 | 'Correct GROUP BY on unique selection and order by a different column', |
56 | ); |
57 | |
58 | is_same_sql_bind ( |
59 | $rs->search({}, { order_by => 'artist.name', join => 'artist' })->as_query, |
60 | '( |
61 | SELECT me.title |
62 | FROM cd me |
63 | JOIN artist artist |
64 | ON artist.artistid = me.artist |
65 | GROUP BY me.title |
66 | ORDER BY MIN(artist.name) |
67 | )', |
68 | [], |
69 | 'Correct GROUP BY on non-unique selection and external single order_by', |
70 | ); |
71 | |
72 | is_same_sql_bind ( |
73 | $unique_rs->search({}, { order_by => 'artist.name', join => 'artist' })->as_query, |
74 | '( |
75 | SELECT me.title, me.cdid |
76 | FROM cd me |
77 | JOIN artist artist |
78 | ON artist.artistid = me.artist |
79 | GROUP BY me.title, me.cdid, artist.name |
80 | ORDER BY artist.name |
81 | )', |
82 | [], |
83 | 'Correct GROUP BY on unique selection and external single order_by', |
84 | ); |
85 | |
86 | is_same_sql_bind ( |
87 | $rs->search({}, { order_by => 'tracks.title', join => 'tracks' })->as_query, |
88 | '( |
89 | SELECT me.title |
90 | FROM cd me |
91 | LEFT JOIN track tracks |
92 | ON tracks.cd = me.cdid |
93 | GROUP BY me.title |
94 | ORDER BY MIN(tracks.title) |
95 | )', |
96 | [], |
97 | 'Correct GROUP BY on non-unique selection and external multi order_by', |
98 | ); |
99 | |
100 | is_same_sql_bind ( |
101 | $unique_rs->search({}, { order_by => 'tracks.title', join => 'tracks' })->as_query, |
102 | '( |
103 | SELECT me.title, me.cdid |
104 | FROM cd me |
105 | LEFT JOIN track tracks |
106 | ON tracks.cd = me.cdid |
107 | GROUP BY me.title, me.cdid |
108 | ORDER BY MIN(tracks.title) |
109 | )', |
110 | [], |
111 | 'Correct GROUP BY on unique selection and external multi order_by', |
d955e938 |
112 | ); |
113 | |
114 | done_testing; |