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