Commit | Line | Data |
8b31f62e |
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 | use DBIx::Class::SQLMaker::LimitDialects; |
9 | |
10 | my ($LIMIT, $OFFSET) = ( |
11 | DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype, |
12 | DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype, |
13 | ); |
14 | |
15 | my $schema = DBICTest->init_schema; |
16 | |
17 | $schema->storage->_sql_maker->limit_dialect ('SkipFirst'); |
18 | |
19 | my $rs_selectas_col = $schema->resultset ('BooksInLibrary')->search ({}, { |
20 | '+select' => ['owner.name'], |
21 | '+as' => ['owner.name'], |
22 | join => 'owner', |
23 | rows => 1, |
24 | offset => 2, |
25 | }); |
26 | |
27 | is_same_sql_bind( |
28 | $rs_selectas_col->as_query, |
29 | '( |
30 | SELECT SKIP ? FIRST ? me.id, me.source, me.owner, me.title, me.price, owner.name |
31 | FROM books me |
32 | JOIN owners owner ON owner.id = me.owner |
33 | WHERE ( source = ? ) |
34 | )', |
35 | [ |
36 | [ $OFFSET => 2 ], |
37 | [ $LIMIT => 1 ], |
38 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
39 | ], |
40 | ); |
41 | |
42 | $schema->storage->_sql_maker->quote_char ([qw/ [ ] /]); |
43 | $schema->storage->_sql_maker->name_sep ('.'); |
44 | |
45 | my $rs_selectas_rel = $schema->resultset ('BooksInLibrary')->search ({}, { |
46 | '+select' => ['owner.name'], |
47 | '+as' => ['owner_name'], |
48 | join => 'owner', |
49 | rows => 1, |
50 | offset => 2, |
51 | }); |
52 | |
53 | is_same_sql_bind( |
54 | $rs_selectas_rel->as_query, |
55 | '( |
56 | SELECT SKIP ? FIRST ? [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], [owner].[name] |
57 | FROM [books] [me] |
58 | JOIN [owners] [owner] ON [owner].[id] = [me].[owner] |
59 | WHERE ( [source] = ? ) |
60 | )', |
61 | [ |
62 | [ $OFFSET => 2 ], |
63 | [ $LIMIT => 1 ], |
64 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
65 | ], |
66 | ); |
67 | |
68 | { |
69 | my $subq = $schema->resultset('Owners')->search({ |
70 | 'count.id' => { -ident => 'owner.id' }, |
71 | 'count.name' => 'fail', # no one would do this in real life, the rows makes even less sense |
72 | }, { alias => 'owner', rows => 1 })->count_rs; |
73 | |
74 | my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search ({}, { |
75 | columns => [ |
76 | { owner_name => 'owner.name' }, |
77 | { owner_books => $subq->as_query }, |
78 | ], |
79 | join => 'owner', |
80 | rows => 1, |
81 | offset => 2, |
82 | }); |
83 | |
84 | is_same_sql_bind( |
85 | $rs_selectas_rel->as_query, |
86 | '( |
87 | SELECT SKIP ? FIRST ? |
88 | [owner].[name], |
89 | ( SELECT COUNT(*) FROM |
90 | ( SELECT FIRST ? [owner].[id] FROM [owners] [owner] |
91 | WHERE [count].[id] = [owner].[id] and [count].[name] = ? |
92 | ) [owner] |
93 | ) |
94 | FROM [books] [me] |
95 | JOIN [owners] [owner] ON [owner].[id] = [me].[owner] |
96 | WHERE ( [source] = ? ) |
97 | )', |
98 | [ |
99 | [ $OFFSET => 2 ], # outer |
100 | [ $LIMIT => 1 ], # outer |
101 | [ {%$LIMIT} => 1 ], # inner |
102 | [ { dbic_colname => 'count.name' } => 'fail' ], |
103 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
104 | ], |
105 | ) |
106 | }; |
107 | |
108 | { |
109 | my $rs = $schema->resultset('Artist')->search({}, { |
110 | columns => 'name', |
111 | offset => 1, |
112 | order_by => 'name', |
113 | }); |
114 | local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table"; |
115 | |
116 | like ( |
117 | ${$rs->as_query}->[0], |
118 | qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x, |
119 | 'Newlines/spaces preserved in final sql', |
120 | ); |
121 | } |
122 | |
123 | { |
124 | my $subq = $schema->resultset('Owners')->search({ |
125 | 'books.owner' => { -ident => 'owner.id' }, |
126 | }, { alias => 'owner', select => ['id'], offset => 3, rows => 4 }); |
127 | |
128 | my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search( { -exists => $subq->as_query }, { select => ['id','owner'], rows => 1, offset => 2 } ); |
129 | |
130 | is_same_sql_bind( |
131 | $rs_selectas_rel->as_query, |
132 | '( |
133 | SELECT SKIP ? FIRST ? [me].[id], [me].[owner] |
134 | FROM [books] [me] |
135 | WHERE ( ( (EXISTS ( |
136 | SELECT SKIP ? FIRST ? [owner].[id] FROM [owners] [owner] WHERE ( [books].[owner] = [owner].[id] ) |
137 | )) AND [source] = ? ) ) |
138 | )', |
139 | [ |
140 | [ $OFFSET => 2 ], #outer |
141 | [ $LIMIT => 1 ], #outer |
142 | [ {%$OFFSET} => 3 ], #inner |
143 | [ {%$LIMIT} => 4 ], #inner |
144 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
145 | ], |
146 | 'Pagination with sub-query in WHERE works' |
147 | ); |
148 | |
149 | } |
150 | |
151 | |
152 | done_testing; |