X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2Fsqlmaker%2Flimit_dialects%2Ftoplimit.t;h=88c99a63f1635b29790347a823ca3fcaab3e980b;hb=97e130fa48aca5a1255b7014b4cbcb0c30c95328;hp=630f32d7cb7a9d4b323f9b8052dfc873a8e4367b;hpb=0e773352a9c6c034dfb2526b8d68bf6ac1e2323b;p=dbsrgits%2FDBIx-Class.git diff --git a/t/sqlmaker/limit_dialects/toplimit.t b/t/sqlmaker/limit_dialects/toplimit.t index 630f32d..88c99a6 100644 --- a/t/sqlmaker/limit_dialects/toplimit.t +++ b/t/sqlmaker/limit_dialects/toplimit.t @@ -14,7 +14,10 @@ my $schema = DBICTest->init_schema; delete $schema->storage->_sql_maker->{_cached_syntax}; $schema->storage->_sql_maker->limit_dialect ('Top'); -my $books_45_and_owners = $schema->resultset ('BooksInLibrary')->search ({}, { prefetch => 'owner', rows => 2, offset => 3 }); +my $books_45_and_owners = $schema->resultset ('BooksInLibrary')->search ({}, { + prefetch => 'owner', rows => 2, offset => 3, + columns => [ grep { $_ ne 'title' } $schema->source('BooksInLibrary')->columns ], +}); for my $null_order ( undef, @@ -27,10 +30,10 @@ for my $null_order ( is_same_sql_bind( $rs->as_query, '(SELECT TOP 2 - id, source, owner, title, price, owner__id, owner__name + me.id, me.source, me.owner, me.price, owner__id, owner__name FROM ( SELECT TOP 5 - me.id, me.source, me.owner, me.title, me.price, owner.id AS owner__id, owner.name AS owner__name + me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name FROM books me JOIN owners owner ON owner.id = me.owner WHERE ( source = ? ) @@ -43,75 +46,113 @@ for my $null_order ( ); } +{ +my $subq = $schema->resultset('Owners')->search({ + 'count.id' => { -ident => 'owner.id' }, +}, { alias => 'owner' })->count_rs; + +my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search ({}, { + columns => [ + { owner_name => 'owner.name' }, + { owner_books => $subq->as_query }, + ], + join => 'owner', + rows => 2, + offset => 3, +}); + +is_same_sql_bind( + $rs_selectas_rel->as_query, + '( + SELECT TOP 2 owner_name, owner_books + FROM ( + SELECT TOP 5 owner.name AS owner_name, + ( SELECT COUNT( * ) + FROM owners owner + WHERE ( count.id = owner.id ) + ) AS owner_books + FROM books me + JOIN owners owner ON owner.id = me.owner + WHERE ( source = ? ) + ORDER BY me.id + ) me + ORDER BY me.id DESC + )', + [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } + => 'Library' ] ], + 'pagination with subqueries works' +); + +} for my $ord_set ( { - order_by => \'foo DESC', - order_inner => 'foo DESC', - order_outer => 'ORDER__BY__1 ASC', - order_req => 'ORDER__BY__1 DESC', - exselect_outer => 'ORDER__BY__1', - exselect_inner => 'foo AS ORDER__BY__1', + order_by => \'title DESC', + order_inner => 'title DESC', + order_outer => 'ORDER__BY__001 ASC', + order_req => 'ORDER__BY__001 DESC', + exselect_outer => 'ORDER__BY__001', + exselect_inner => 'title AS ORDER__BY__001', }, { - order_by => { -asc => 'foo' }, - order_inner => 'foo ASC', - order_outer => 'ORDER__BY__1 DESC', - order_req => 'ORDER__BY__1 ASC', - exselect_outer => 'ORDER__BY__1', - exselect_inner => 'foo AS ORDER__BY__1', + order_by => { -asc => 'title' }, + order_inner => 'title ASC', + order_outer => 'ORDER__BY__001 DESC', + order_req => 'ORDER__BY__001 ASC', + exselect_outer => 'ORDER__BY__001', + exselect_inner => 'title AS ORDER__BY__001', }, { - order_by => { -desc => 'foo' }, - order_inner => 'foo DESC', - order_outer => 'ORDER__BY__1 ASC', - order_req => 'ORDER__BY__1 DESC', - exselect_outer => 'ORDER__BY__1', - exselect_inner => 'foo AS ORDER__BY__1', + order_by => { -desc => 'title' }, + order_inner => 'title DESC', + order_outer => 'ORDER__BY__001 ASC', + order_req => 'ORDER__BY__001 DESC', + exselect_outer => 'ORDER__BY__001', + exselect_inner => 'title AS ORDER__BY__001', }, { - order_by => 'foo', - order_inner => 'foo', - order_outer => 'ORDER__BY__1 DESC', - order_req => 'ORDER__BY__1', - exselect_outer => 'ORDER__BY__1', - exselect_inner => 'foo AS ORDER__BY__1', + order_by => 'title', + order_inner => 'title', + order_outer => 'ORDER__BY__001 DESC', + order_req => 'ORDER__BY__001', + exselect_outer => 'ORDER__BY__001', + exselect_inner => 'title AS ORDER__BY__001', }, { - order_by => [ qw{ foo me.owner} ], - order_inner => 'foo, me.owner', - order_outer => 'ORDER__BY__1 DESC, me.owner DESC', - order_req => 'ORDER__BY__1, me.owner', - exselect_outer => 'ORDER__BY__1', - exselect_inner => 'foo AS ORDER__BY__1', + order_by => [ qw{ title me.owner} ], + order_inner => 'title, me.owner', + order_outer => 'ORDER__BY__001 DESC, me.owner DESC', + order_req => 'ORDER__BY__001, me.owner', + exselect_outer => 'ORDER__BY__001', + exselect_inner => 'title AS ORDER__BY__001', }, { - order_by => ['foo', { -desc => 'bar' } ], - order_inner => 'foo, bar DESC', - order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 ASC', - order_req => 'ORDER__BY__1, ORDER__BY__2 DESC', - exselect_outer => 'ORDER__BY__1, ORDER__BY__2', - exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2', + order_by => ['title', { -desc => 'bar' } ], + order_inner => 'title, bar DESC', + order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC', + order_req => 'ORDER__BY__001, ORDER__BY__002 DESC', + exselect_outer => 'ORDER__BY__001, ORDER__BY__002', + exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002', }, { - order_by => { -asc => [qw{ foo bar }] }, - order_inner => 'foo ASC, bar ASC', - order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 DESC', - order_req => 'ORDER__BY__1 ASC, ORDER__BY__2 ASC', - exselect_outer => 'ORDER__BY__1, ORDER__BY__2', - exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2', + order_by => { -asc => [qw{ title bar }] }, + order_inner => 'title ASC, bar ASC', + order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 DESC', + order_req => 'ORDER__BY__001 ASC, ORDER__BY__002 ASC', + exselect_outer => 'ORDER__BY__001, ORDER__BY__002', + exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002', }, { order_by => [ - 'foo', + 'title', { -desc => [qw{bar}] }, { -asc => [qw{me.owner sensors}]}, ], - order_inner => 'foo, bar DESC, me.owner ASC, sensors ASC', - order_outer => 'ORDER__BY__1 DESC, ORDER__BY__2 ASC, me.owner DESC, ORDER__BY__3 DESC', - order_req => 'ORDER__BY__1, ORDER__BY__2 DESC, me.owner ASC, ORDER__BY__3 ASC', - exselect_outer => 'ORDER__BY__1, ORDER__BY__2, ORDER__BY__3', - exselect_inner => 'foo AS ORDER__BY__1, bar AS ORDER__BY__2, sensors AS ORDER__BY__3', + order_inner => 'title, bar DESC, me.owner ASC, sensors ASC', + order_outer => 'ORDER__BY__001 DESC, ORDER__BY__002 ASC, me.owner DESC, ORDER__BY__003 DESC', + order_req => 'ORDER__BY__001, ORDER__BY__002 DESC, me.owner ASC, ORDER__BY__003 ASC', + exselect_outer => 'ORDER__BY__001, ORDER__BY__002, ORDER__BY__003', + exselect_inner => 'title AS ORDER__BY__001, bar AS ORDER__BY__002, sensors AS ORDER__BY__003', }, ) { my $o_sel = $ord_set->{exselect_outer} @@ -125,14 +166,13 @@ for my $ord_set ( is_same_sql_bind( $books_45_and_owners->search ({}, {order_by => $ord_set->{order_by}})->as_query, - "(SELECT TOP 2 - id, source, owner, title, price, owner__id, owner__name + "(SELECT me.id, me.source, me.owner, me.price, owner__id, owner__name FROM ( SELECT TOP 2 - id, source, owner, title, price, owner__id, owner__name$o_sel + me.id, me.source, me.owner, me.price, owner__id, owner__name$o_sel FROM ( SELECT TOP 5 - me.id, me.source, me.owner, me.title, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel + me.id, me.source, me.owner, me.price, owner.id AS owner__id, owner.name AS owner__name$i_sel FROM books me JOIN owners owner ON owner.id = me.owner WHERE ( source = ? ) @@ -150,15 +190,15 @@ for my $ord_set ( # with groupby is_same_sql_bind ( $books_45_and_owners->search ({}, { group_by => 'title', order_by => 'title' })->as_query, - '(SELECT me.id, me.source, me.owner, me.title, me.price, owner.id, owner.name + '(SELECT me.id, me.source, me.owner, me.price, owner.id, owner.name FROM ( - SELECT TOP 2 id, source, owner, title, price + SELECT me.id, me.source, me.owner, me.price, me.title FROM ( SELECT TOP 2 - id, source, owner, title, price + me.id, me.source, me.owner, me.price, me.title FROM ( SELECT TOP 5 - me.id, me.source, me.owner, me.title, me.price + me.id, me.source, me.owner, me.price, me.title FROM books me JOIN owners owner ON owner.id = me.owner WHERE ( source = ? ) @@ -184,17 +224,16 @@ my $rs_selectas_top = $schema->resultset ('BooksInLibrary')->search ({}, { '+select' => ['owner.name'], '+as' => ['owner_name'], join => 'owner', - rows => 1 + rows => 1 }); is_same_sql_bind( $rs_selectas_top->search({})->as_query, '(SELECT TOP 1 me.id, me.source, me.owner, me.title, me.price, - owner.name AS owner_name + owner.name FROM books me JOIN owners owner ON owner.id = me.owner WHERE ( source = ? ) - ORDER BY me.id )', [ [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ] ], @@ -202,9 +241,9 @@ is_same_sql_bind( $rs_selectas_top->search({})->as_query, { my $rs = $schema->resultset('Artist')->search({}, { - columns => 'name', + columns => 'artistid', offset => 1, - order_by => 'name', + order_by => 'artistid', }); local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table"; @@ -215,4 +254,22 @@ is_same_sql_bind( $rs_selectas_top->search({})->as_query, ); } +{ +my $subq = $schema->resultset('Owners')->search({ + 'books.owner' => { -ident => 'owner.id' }, +}, { alias => 'owner', select => ['id'] } )->count_rs; + +my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search( { -exists => $subq->as_query }, { select => ['id','owner'], rows => 1 } ); + +is_same_sql_bind( + $rs_selectas_rel->as_query, + '(SELECT TOP 1 me.id, me.owner FROM books me WHERE ( ( (EXISTS (SELECT COUNT( * ) FROM owners owner WHERE ( books.owner = owner.id ))) AND source = ? ) ) )', + [ + [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], + ], + 'Pagination with sub-query in WHERE works' +); + +} + done_testing;