Commit | Line | Data |
f895c500 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | use lib qw(t/lib); |
6 | use DBICTest; |
7 | use DBIC::SqlMakerTest; |
8 | |
9 | my $schema = DBICTest->init_schema; |
10 | |
11 | my ($ROWS, $OFFSET) = ( |
12 | DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype, |
13 | DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype, |
14 | ); |
15 | |
16 | # based on toplimit.t |
17 | delete $schema->storage->_sql_maker->{_cached_syntax}; |
18 | $schema->storage->_sql_maker->limit_dialect ('OffsetFetchNext'); |
19 | |
20 | my $books_45_and_owners = $schema->resultset ('BooksInLibrary')->search ({}, { |
21 | prefetch => 'owner', rows => 2, |
22 | columns => [ grep { $_ ne 'title' } $schema->source('BooksInLibrary')->columns ], |
23 | }); |
24 | |
25 | # no offset |
26 | is_same_sql_bind( |
27 | $books_45_and_owners->as_query, |
28 | '( |
29 | SELECT me.id, me.source, me.owner, me.price, owner.id, owner.name |
30 | FROM books me |
31 | JOIN owners owner ON owner.id = me.owner |
32 | WHERE ( source = ? ) |
33 | ORDER BY 1 |
34 | OFFSET ? ROWS FETCH NEXT ? ROWS ONLY |
35 | )', |
36 | [ |
37 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
38 | => 'Library' ], |
39 | [ $OFFSET => 0 ], |
40 | [ $ROWS => 2 ], |
41 | ], |
42 | ); |
43 | |
44 | $books_45_and_owners = $books_45_and_owners->search({}, { offset => 3 }); |
45 | for my $null_order ( |
46 | undef, |
47 | '', |
48 | {}, |
49 | [], |
50 | [{}], |
51 | ) { |
52 | my $rs = $books_45_and_owners->search ({}, {order_by => $null_order }); |
53 | is_same_sql_bind( |
54 | $rs->as_query, |
55 | '(SELECT me.id, me.source, me.owner, me.price, owner.id, owner.name |
56 | FROM books me |
57 | JOIN owners owner ON owner.id = me.owner |
58 | WHERE ( source = ? ) |
59 | ORDER BY 1 |
60 | OFFSET ? ROWS |
61 | FETCH NEXT ? ROWS ONLY |
62 | )', |
63 | [ |
64 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
65 | => 'Library' ], |
66 | [ $OFFSET => 3 ], |
67 | [ $ROWS => 2 ], |
68 | ], |
69 | ); |
70 | } |
71 | |
72 | { |
73 | my $rs = $schema->resultset('Artist')->search({}, { |
74 | columns => 'artistid', |
75 | offset => 1, |
76 | order_by => 'artistid', |
77 | }); |
78 | local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table"; |
79 | |
80 | like ( |
81 | ${$rs->as_query}->[0], |
82 | qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x, |
83 | 'Newlines/spaces preserved in final sql', |
84 | ); |
85 | } |
86 | |
87 | my $attr = {}; |
88 | my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search(undef, { |
89 | columns => 'me.id', |
90 | offset => 3, |
91 | rows => 4, |
92 | '+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] }, |
93 | order_by => [ \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ], |
94 | having => \[ '?', [ $attr => 21 ] ], |
95 | }); |
96 | |
97 | is_same_sql_bind( |
98 | $rs_selectas_rel->as_query, |
99 | '( |
100 | SELECT me.id, ? * ?, ? |
101 | FROM books me |
102 | WHERE ( source = ? ) |
103 | HAVING ? |
104 | ORDER BY ? / ?, ? |
105 | OFFSET ? ROWS |
106 | FETCH NEXT ? ROWS ONLY |
107 | )', |
108 | [ |
109 | [ $attr => 11 ], [ $attr => 12 ], [ $attr => 13 ], |
110 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
111 | [ $attr => 21 ], |
112 | [ $attr => 1 ], [ $attr => 2 ], [ $attr => 3 ], |
113 | [ $OFFSET => 3 ], |
114 | [ $ROWS => 4 ], |
115 | ], |
116 | 'Pagination with sub-query in ORDER BY works' |
117 | ); |
118 | |
119 | |
120 | done_testing; |