Commit | Line | Data |
7fe322c8 |
1 | BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) } |
2 | |
3 | use strict; |
4 | use warnings; |
5 | |
6 | use Test::More; |
7 | |
8 | use DBICTest ':DiffSQL'; |
9 | |
10 | my $schema = DBICTest->init_schema( |
11 | no_deploy => 1, |
12 | quote_names => 1, |
13 | storage_type => 'DBIx::Class::Storage::DBI::Pg' |
14 | ); |
15 | |
16 | my $rs = $schema->resultset('Artist')->search_related('cds_unordered', |
17 | { "me.rank" => 13 }, |
18 | { |
19 | prefetch => 'tracks', |
20 | join => 'genre', |
21 | order_by => [ 'genre.name', { -desc => \ 'tracks.title' }, { -asc => "me.name" }, { -desc => [qw(year cds_unordered.title)] } ], # me. is the artist, *NOT* the cd |
22 | rows => 1, |
23 | }, |
24 | ); |
25 | |
26 | { |
27 | # THIS IS AN OFFLINE TEST |
28 | # We only need this so that the thing can be verified to work without PG_DSN |
29 | # Executing it while "lying" this way won't work |
30 | local $rs->result_source->related_source('tracks')->column_info('title')->{data_type} = 'bool'; |
31 | local $rs->result_source->related_source('genre')->column_info('name')->{data_type} = 'BOOLEAN'; |
32 | |
33 | is_same_sql_bind( |
34 | $rs->as_query, |
35 | q{( |
36 | SELECT "cds_unordered"."cdid", "cds_unordered"."artist", "cds_unordered"."title", "cds_unordered"."year", "cds_unordered"."genreid", "cds_unordered"."single_track", |
37 | "tracks"."trackid", "tracks"."cd", "tracks"."position", "tracks"."title", "tracks"."last_updated_on", "tracks"."last_updated_at" |
38 | FROM "artist" "me" |
39 | JOIN ( |
40 | SELECT "cds_unordered"."cdid", "cds_unordered"."artist", "cds_unordered"."title", "cds_unordered"."year", "cds_unordered"."genreid", "cds_unordered"."single_track" |
41 | FROM "artist" "me" |
42 | JOIN cd "cds_unordered" |
43 | ON "cds_unordered"."artist" = "me"."artistid" |
44 | LEFT JOIN "genre" "genre" |
45 | ON "genre"."genreid" = "cds_unordered"."genreid" |
46 | LEFT JOIN "track" "tracks" |
47 | ON "tracks"."cd" = "cds_unordered"."cdid" |
48 | WHERE "me"."rank" = ? |
49 | GROUP BY "cds_unordered"."cdid", "cds_unordered"."artist", "cds_unordered"."title", "cds_unordered"."year", "cds_unordered"."genreid", "cds_unordered"."single_track", "me"."name" |
50 | ORDER BY BOOL_AND("genre"."name"), |
51 | BOOL_OR( tracks.title ) DESC, |
52 | "me"."name" ASC, |
53 | "year" DESC, |
54 | "cds_unordered"."title" DESC |
55 | LIMIT ? |
56 | ) "cds_unordered" |
57 | ON "cds_unordered"."artist" = "me"."artistid" |
58 | LEFT JOIN "genre" "genre" |
59 | ON "genre"."genreid" = "cds_unordered"."genreid" |
60 | LEFT JOIN "track" "tracks" |
61 | ON "tracks"."cd" = "cds_unordered"."cdid" |
62 | WHERE "me"."rank" = ? |
63 | ORDER BY "genre"."name", |
64 | tracks.title DESC, |
65 | "me"."name" ASC, |
66 | "year" DESC, |
67 | "cds_unordered"."title" DESC |
68 | )}, |
69 | [ |
70 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' } => 13 ], |
71 | [ DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype => 1 ], |
72 | [ { sqlt_datatype => 'integer', dbic_colname => 'me.rank' } => 13 ], |
73 | ], |
74 | 'correct SQL with aggregate boolean order on Pg', |
75 | ); |
76 | } |
77 | |
78 | done_testing; |