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();
}
);
-# add a floating +select to make sure it does nto throw things off
+# 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)
-my $bind_one = [ __add => 1 ];
$use_prefetch = $use_prefetch->search({}, {
- '+select' => \[ 'me.artistid + ?', $bind_one ],
+ '+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.name,
- cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track,
- me.artistid + ?
+ 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.name,
- me.artistid + ?
+ SELECT me.artistid + ?,
+ me.artistid, me.name
FROM artist me
LEFT JOIN cd cds
ON cds.artist = me.artistid
ON tracks.cd = cds.cdid
WHERE artwork.cd_id IS NULL
OR tracks.title != ?
- GROUP BY me.artistid, me.name, me.artistid + ?
- ORDER BY name DESC LIMIT 3
+ GROUP BY me.artistid + ?, me.artistid, me.name
+ ORDER BY name DESC LIMIT ?
) me
LEFT JOIN cd cds
ON cds.artist = me.artistid
ON tracks.cd = cds.cdid
WHERE artwork.cd_id IS NULL
OR tracks.title != ?
- GROUP BY me.artistid, me.name, cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track, me.artistid + ?
- ORDER BY name DESC, cds.artist, cds.year ASC
+ ORDER BY name DESC
)',
[
- $bind_one, # outer select
- $bind_one, # inner select
- [ 'tracks.title' => 'blah-blah-1234568' ], # inner where
- $bind_one, # inner group_by
- [ 'tracks.title' => 'blah-blah-1234568' ], # outer where
- $bind_one, # outer group_by
+ $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'
);
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',
);
{'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;
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;
projects / dbsrgits/DBIx-Class.git / blobdiff