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=1d2aa84ff5dc2aeb905269374bb1861b70c992a3;hpb=946f626022c63fd269d9d985c2abeabb52871027;p=dbsrgits%2FDBIx-Class.git

diff --git a/t/prefetch/with_limit.t b/t/prefetch/with_limit.t
index 1d2aa84..28b3b8a 100644
--- a/t/prefetch/with_limit.t
+++ b/t/prefetch/with_limit.t
@@ -6,8 +6,7 @@ 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;
@@ -80,7 +79,6 @@ is_same_sql_bind (
         ON tracks.cd = cds.cdid
     WHERE artwork.cd_id IS NULL
        OR tracks.title != ?
-    GROUP BY me.artistid + ?, me.artistid, me.name, cds.cdid, cds.artist, cds.title, cds.year, cds.genreid, cds.single_track
     ORDER BY name DESC
   )',
   [
@@ -90,7 +88,6 @@ is_same_sql_bind (
     $bind_int_resolved->(),  # inner group_by
     [ $ROWS => 3 ],
     $bind_vc_resolved->(), # outer where
-    $bind_int_resolved->(),  # outer group_by
   ],
   'Expected SQL on complex limited prefetch'
 );
@@ -144,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',
 );
 
@@ -154,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;
@@ -190,7 +187,6 @@ is_same_sql_bind (
       JOIN artist artist
         ON artist.artistid = me.artist
     WHERE ( ( artist.name = ? AND me.year = ? ) )
-    ORDER BY me.cdid
   )',
   [
     [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'artist.name' } => 'foo' ],
@@ -202,4 +198,91 @@ is_same_sql_bind (
   '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;