use strict;
use warnings;
+
use Test::More;
+use Test::Exception;
use lib qw(t/lib);
use DBICTest;
use DBIC::SqlMakerTest;
-#plan tests => 6;
-plan 'no_plan';
-
my $schema = DBICTest->init_schema();
my $sdebug = $schema->storage->debug;
my $track_rs = $schema->resultset ('Track')->search (
{ 'me.cd' => { -in => [ $cd_rs->get_column ('cdid')->all ] } },
{
- # the select/as is deliberately silly to test both funcs and refs below
select => [
'me.cd',
{ count => 'me.trackid' },
# Test sql by hand, as the sqlite db will simply paper over
# improper group/select combinations
#
- # the exploded IN needs fixing below, coming in another branch
- #
is_same_sql_bind (
$track_rs->count_rs->as_query,
'(
SELECT me.cd
FROM track me
JOIN cd cd ON cd.cdid = me.cd
- WHERE ( me.cd IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) )
+ WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
GROUP BY me.cd
)
count_subq
SELECT me.cd, COUNT (me.trackid) AS track_count,
FROM track me
JOIN cd cd ON cd.cdid = me.cd
- WHERE ( me.cd IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) )
+ WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
GROUP BY me.cd
) as me
JOIN cd cd ON cd.cdid = me.cd
- WHERE ( me.cd IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) )
+ WHERE ( me.cd IN ( ?, ?, ?, ?, ? ) )
)',
[ map { [ 'me.cd' => $_] } ( ($cd_rs->get_column ('cdid')->all) x 2 ) ],
'next() query generated expected SQL',
# test a has_many/might_have prefetch at the same level
# Note that one of the CDs now has 4 tracks instead of 3
{
- my $most_tracks_rs = $cd_rs->search ({}, {
- prefetch => 'liner_notes', # tracks are alredy prefetched
- select => ['me.cdid', { count => 'tracks.trackid' } ],
- as => [qw/cdid track_count/],
- group_by => 'me.cdid',
- order_by => { -desc => 'track_count' },
- rows => 2,
- });
+ my $most_tracks_rs = $schema->resultset ('CD')->search (
+ {
+ 'me.cdid' => { '!=' => undef }, # duh - this is just to test WHERE
+ },
+ {
+ prefetch => [qw/tracks liner_notes/],
+ select => ['me.cdid', { count => 'tracks.trackid' } ],
+ as => [qw/cdid track_count/],
+ group_by => 'me.cdid',
+ order_by => { -desc => 'track_count' },
+ rows => 2,
+ }
+ );
is_same_sql_bind (
$most_tracks_rs->count_rs->as_query,
FROM cd me
LEFT JOIN track tracks ON tracks.cd = me.cdid
LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid
- WHERE ( tracks.cd IS NOT NULL )
+ WHERE ( me.cdid IS NOT NULL )
GROUP BY me.cdid
LIMIT 2
) count_subq
SELECT me.cdid, COUNT( tracks.trackid ) AS track_count
FROM cd me
LEFT JOIN track tracks ON tracks.cd = me.cdid
- WHERE ( tracks.cd IS NOT NULL )
+ WHERE ( me.cdid IS NOT NULL )
GROUP BY me.cdid
ORDER BY track_count DESC
LIMIT 2
) me
LEFT JOIN track tracks ON tracks.cd = me.cdid
LEFT JOIN liner_notes liner_notes ON liner_notes.liner_id = me.cdid
- WHERE ( tracks.cd IS NOT NULL )
+ WHERE ( me.cdid IS NOT NULL )
ORDER BY track_count DESC, tracks.cd
)',
[],
$schema->storage->debugcb (undef);
$schema->storage->debug ($sdebug);
}
+
+# make sure that distinct still works
+{
+ my $rs = $schema->resultset("CD")->search({}, {
+ prefetch => 'tags',
+ order_by => 'cdid',
+ distinct => 1,
+ });
+
+ is_same_sql_bind (
+ $rs->as_query,
+ '(
+ SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track,
+ tags.tagid, tags.cd, tags.tag
+ FROM (
+ SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
+ FROM cd me
+ GROUP BY me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
+ ORDER BY cdid
+ ) me
+ LEFT JOIN tags tags ON tags.cd = me.cdid
+ ORDER BY cdid, tags.cd, tags.tag
+ )',
+ [],
+ 'Prefetch + distinct resulted in correct group_by',
+ );
+
+ is ($rs->all, 5, 'Correct number of CD objects');
+ is ($rs->count, 5, 'Correct count of CDs');
+}
+
+lives_ok (sub {
+ my $rs = $schema->resultset("Artwork")->search(undef, {distinct => 1})
+ ->search_related('artwork_to_artist')->search_related('artist',
+ undef,
+ { prefetch => q(cds) },
+ );
+ is($rs->all, 0, 'prefetch without WHERE');
+
+ $rs = $schema->resultset("Artwork")->search(undef, {distinct => 1})
+ ->search_related('artwork_to_artist')->search_related('artist',
+ { 'cds.title' => 'foo' },
+ { prefetch => q(cds) },
+ );
+ is($rs->all, 0, 'prefetch with WHERE');
+
+
+ # different case
+ $rs = $schema->resultset("Artist")->search(undef)
+ ->search_related('cds')->search_related('genre',
+ { 'genre.name' => 'foo' },
+ { prefetch => q(cds) },
+ );
+ is($rs->all, 0, 'prefetch without distinct');
+
+ $rs = $schema->resultset("Artist")->search(undef, {distinct => 1})
+ ->search_related('cds')->search_related('genre',
+ { 'genre.name' => 'foo' },
+ );
+ is($rs->all, 0, 'distinct without prefetch');
+
+ $rs = $schema->resultset("Artist")->search({artistid => '11'}, {distinct => 1})
+ ->search_related('cds')->search_related('genre',
+ { 'genre.name' => 'foo' },
+ { prefetch => q(cds) },
+ );
+ is($rs->all, 0, 'prefetch with distinct');
+}, 'distinct generally works with prefetch');
+
+done_testing;