X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2Fprefetch%2Fwith_limit.t;h=28b3b8a89ebadb470ff2720c1740184f749a59f8;hb=a5a7bb733a940db710b7408508374833683a2e79;hp=4acdcab392f4e1bd927e5735aaf3bb9746e6a445;hpb=14e26c5f3516e39d9191ca9b2a9d460f8f495654;p=dbsrgits%2FDBIx-Class.git diff --git a/t/prefetch/with_limit.t b/t/prefetch/with_limit.t index 4acdcab..28b3b8a 100644 --- a/t/prefetch/with_limit.t +++ b/t/prefetch/with_limit.t @@ -6,8 +6,10 @@ use warnings; use Test::More; use Test::Exception; use lib qw(t/lib); -use DBICTest; -use DBIC::SqlMakerTest; +use DBICTest ':DiffSQL'; +use DBIx::Class::SQLMaker::LimitDialects; + +my $ROWS = DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype; my $schema = DBICTest->init_schema(); @@ -31,6 +33,65 @@ my $use_prefetch = $no_prefetch->search( } ); +# add an extra +select to make sure it does not throw things off +# we also expect it to appear in both selectors, as we can not know +# for sure which part of the query it applies to (may be order_by, +# maybe something else) +# +# we use a reference to the same array in bind vals, because +# is_deeply picks up this difference too (not sure if bug or +# feature) +$use_prefetch = $use_prefetch->search({}, { + '+columns' => { monkeywrench => \[ 'me.artistid + ?', [ \ 'inTEger' => 1 ] ] }, +}); + +my $bind_int_resolved = sub { [ { sqlt_datatype => 'inTEger' } => 1 ] }; +my $bind_vc_resolved = sub { [ + { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'tracks.title' } + => 'blah-blah-1234568' +] }; +is_same_sql_bind ( + $use_prefetch->as_query, + '( + SELECT me.artistid + ?, + me.artistid, me.name, + cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track + FROM ( + SELECT me.artistid + ?, + me.artistid, me.name + FROM artist me + LEFT JOIN cd cds + ON cds.artist = me.artistid + LEFT JOIN cd_artwork artwork + ON artwork.cd_id = cds.cdid + LEFT JOIN track tracks + ON tracks.cd = cds.cdid + WHERE artwork.cd_id IS NULL + OR tracks.title != ? + GROUP BY me.artistid + ?, me.artistid, me.name + ORDER BY name DESC LIMIT ? + ) me + LEFT JOIN cd cds + ON cds.artist = me.artistid + LEFT JOIN cd_artwork artwork + ON artwork.cd_id = cds.cdid + LEFT JOIN track tracks + ON tracks.cd = cds.cdid + WHERE artwork.cd_id IS NULL + OR tracks.title != ? + ORDER BY name DESC + )', + [ + $bind_int_resolved->(), # outer select + $bind_int_resolved->(), # inner select + $bind_vc_resolved->(), # inner where + $bind_int_resolved->(), # inner group_by + [ $ROWS => 3 ], + $bind_vc_resolved->(), # outer where + ], + 'Expected SQL on complex limited prefetch' +); + is($no_prefetch->count, $use_prefetch->count, '$no_prefetch->count == $use_prefetch->count'); is( scalar ($no_prefetch->all), @@ -80,7 +141,7 @@ is ( throws_ok ( sub { $use_prefetch->single }, - qr/resultsets prefetching has_many/, + qr/\Qsingle() can not be used on resultsets collapsing a has_many/, 'single() with multiprefetch is illegal', ); @@ -90,7 +151,7 @@ throws_ok ( {'tracks.title' => { '!=' => 'foo' }}, { order_by => \ 'some oddball literal sql', join => { cds => 'tracks' } } )->next - }, qr/A required group_by clause could not be constructed automatically/, + }, qr/Unable to programatically derive a required group_by from the supplied order_by criteria/, ); my $artist = $use_prefetch->search({'cds.title' => $artist_many_cds->cds->first->title })->next; @@ -119,22 +180,109 @@ is_same_sql_bind ( FROM cd me JOIN artist artist ON artist.artistid = me.artist WHERE ( ( artist.name = ? AND me.year = ? ) ) - LIMIT 15 + LIMIT ? ) me LEFT JOIN track tracks ON tracks.cd = me.cdid JOIN artist artist ON artist.artistid = me.artist WHERE ( ( artist.name = ? AND me.year = ? ) ) - ORDER BY tracks.cd )', [ - [ 'artist.name' => 'foo' ], - [ 'me.year' => 2010 ], - [ 'artist.name' => 'foo' ], - [ 'me.year' => 2010 ], + [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' } => 'foo' ], + [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.year' } => 2010 ], + [ $ROWS => 15 ], + [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' } => 'foo' ], + [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.year' } => 2010 ], ], 'No grouping of non-multiplying resultsets', ); +my $many_one_many_rs = $schema->resultset('CD')->search({}, { + prefetch => { tracks => { lyrics => 'lyric_versions' } }, + rows => 2, + order_by => ['lyrics.track_id'], +}); + +is_same_sql_bind( + $many_one_many_rs->as_query, + '( + SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, + tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, + lyrics.lyric_id, lyrics.track_id, lyric_versions.id, lyric_versions.lyric_id, lyric_versions.text + FROM ( + SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track + FROM cd me + LEFT JOIN track tracks + ON tracks.cd = me.cdid + LEFT JOIN lyrics lyrics + ON lyrics.track_id = tracks.trackid + GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track + ORDER BY MIN(lyrics.track_id) + LIMIT ? + ) me + LEFT JOIN track tracks + ON tracks.cd = me.cdid + LEFT JOIN lyrics lyrics + ON lyrics.track_id = tracks.trackid + LEFT JOIN lyric_versions lyric_versions + ON lyric_versions.lyric_id = lyrics.lyric_id + ORDER BY lyrics.track_id + )', + [ + [ { sqlt_datatype => 'integer' } => 2 ] + ], + 'Correct SQL on indirectly multiplied orderer', +); + +my $cond_on_multi_ord_by_single = $schema->resultset('CD')->search( + { + 'tracks.position' => { '!=', 1 }, + }, + { + prefetch => [qw( tracks artist )], + order_by => 'artist.name', + rows => 1, + }, +); + +is_same_sql_bind( + $cond_on_multi_ord_by_single->as_query, + '( + SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, + tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, + artist.artistid, artist.name, artist.rank, artist.charfield + FROM ( + SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track + FROM cd me + LEFT JOIN track tracks + ON tracks.cd = me.cdid + JOIN artist artist + ON artist.artistid = me.artist + WHERE tracks.position != ? + GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, artist.name + ORDER BY artist.name + LIMIT ? + ) me + LEFT JOIN track tracks + ON tracks.cd = me.cdid + JOIN artist artist + ON artist.artistid = me.artist + WHERE tracks.position != ? + ORDER BY artist.name + )', + [ + [ { dbic_colname => "tracks.position", sqlt_datatype => "int" } + => 1 + ], + [ { sqlt_datatype => "integer" } + => 1 + ], + [ { dbic_colname => "tracks.position", sqlt_datatype => "int" } + => 1 + ], + ], + 'Correct SQl on prefetch with limit of restricting multi ordered by a single' +); + done_testing;