carp ("Useless use of distinct on a grouped resultset ('distinct' is ignored when a 'group_by' is present)");
}
else {
- my $storage = $self->result_source->schema->storage;
- my $rs_column_list = $storage->_resolve_column_info ($attrs->{from});
-
- my $group_spec = $attrs->{group_by} = [];
- my %group_index;
-
- for (@{$attrs->{select}}) {
- if (! ref($_) or ref ($_) ne 'HASH' ) {
- push @$group_spec, $_;
- $group_index{$_}++;
- if ($rs_column_list->{$_} and $_ !~ /\./ ) {
- # add a fully qualified version as well
- $group_index{"$rs_column_list->{$_}{-source_alias}.$_"}++;
- }
- }
- }
- # add any order_by parts that are not already present in the group_by
- # we need to be careful not to add any named functions/aggregates
- # i.e. select => [ ... { count => 'foo', -as 'foocount' } ... ]
- for my $chunk ($storage->_extract_order_columns($attrs->{order_by})) {
-
- # only consider real columns (for functions the user got to do an explicit group_by)
- my $colinfo = $rs_column_list->{$chunk}
- or next;
-
- $chunk = "$colinfo->{-source_alias}.$chunk" if $chunk !~ /\./;
- push @$group_spec, $chunk unless $group_index{$chunk}++;
- }
+ $attrs->{group_by} = $source->storage->_group_over_selection (
+ @{$attrs}{qw/from select order_by/}
+ );
}
}
# if a multi-type join was needed in the subquery - add a group_by to simulate the
# collapse in the subq
- $inner_attrs->{group_by} ||= $inner_select
- if first { ! $_->[0]{-is_single} } (@{$inner_from}[1 .. $#$inner_from]);
+ if (
+ ! $inner_attrs->{group_by}
+ and
+ first { ! $_->[0]{-is_single} } (@{$inner_from}[1 .. $#$inner_from])
+ ) {
+ $inner_attrs->{group_by} = $self->_group_over_selection (
+ $inner_from, $inner_select, $inner_attrs->{order_by}
+ );
+ }
# we already optimized $inner_from above
local $self->{_use_join_optimizer} = 0;
return $aliases_by_type;
}
+sub _group_over_selection {
+ my ($self, $from, $select, $order_by) = @_;
+
+ my $rs_column_list = $self->_resolve_column_info ($from);
+
+ my (@group_by, %group_index);
+
+ for (@$select) {
+ if (! ref($_) or ref ($_) ne 'HASH' ) {
+ push @group_by, $_;
+ $group_index{$_}++;
+ if ($rs_column_list->{$_} and $_ !~ /\./ ) {
+ # add a fully qualified version as well
+ $group_index{"$rs_column_list->{$_}{-source_alias}.$_"}++;
+ }
+ }
+ }
+
+ # add any order_by parts that are not already present in the group_by
+ # we need to be careful not to add any named functions/aggregates
+ # i.e. select => [ ... { count => 'foo', -as 'foocount' } ... ]
+ for my $chunk ($self->_extract_order_columns($order_by)) {
+ # only consider real columns (for functions the user got to do an explicit group_by)
+ my $colinfo = $rs_column_list->{$chunk}
+ or next;
+
+ $chunk = "$colinfo->{-source_alias}.$chunk" if $chunk !~ /\./;
+ push @group_by, $chunk unless $group_index{$chunk}++;
+ }
+
+ return \@group_by;
+}
+
sub _resolve_ident_sources {
my ($self, $ident) = @_;
--- /dev/null
+use strict;
+use warnings;
+
+use Test::More;
+
+use lib qw(t/lib);
+use DBIC::SqlMakerTest;
+use DBICTest;
+
+my $schema = DBICTest->init_schema();
+
+my $artist_rs = $schema->resultset('Artist');
+my $ar = $artist_rs->current_source_alias;
+
+my $filtered_cd_rs = $artist_rs->search_related('cds_unordered',
+ { "$ar.rank" => 13 },
+ {
+ prefetch => [ 'tracks' ],
+ order_by => [ { -asc => "$ar.name" }, "$ar.artistid DESC" ],
+ offset => 3,
+ rows => 3,
+ },
+);
+
+is_same_sql_bind(
+ $filtered_cd_rs->as_query,
+ q{(
+ SELECT cds_unordered.cdid, cds_unordered.artist, cds_unordered.title, cds_unordered.year, cds_unordered.genreid, cds_unordered.single_track,
+ tracks.trackid, tracks.cd, tracks.position, tracks.title, tracks.last_updated_on, tracks.last_updated_at, tracks.small_dt
+ FROM artist me
+ JOIN (
+ SELECT cds_unordered.cdid, cds_unordered.artist, cds_unordered.title, cds_unordered.year, cds_unordered.genreid, cds_unordered.single_track
+ FROM artist me
+ JOIN cd cds_unordered
+ ON cds_unordered.artist = me.artistid
+ WHERE ( me.rank = ? )
+ GROUP BY cds_unordered.cdid, cds_unordered.artist, cds_unordered.title, cds_unordered.year, cds_unordered.genreid, cds_unordered.single_track, me.name, me.artistid
+ ORDER BY me.name ASC, me.artistid DESC
+ LIMIT 3
+ OFFSET 3
+ ) cds_unordered
+ ON cds_unordered.artist = me.artistid
+ LEFT JOIN track tracks
+ ON tracks.cd = cds_unordered.cdid
+ WHERE ( me.rank = ? )
+ ORDER BY me.name ASC, me.artistid DESC, tracks.cd
+ )},
+ [ [ 'me.rank' => 13 ], [ 'me.rank' => 13 ] ],
+ 'correct SQL on limited prefetch over search_related ordered by root',
+);
+
+# note: we only requested "get all cds of all artists with rank 13 then order
+# by the artist name and give me the fourth, fifth and sixth", consequently the
+# cds that belong to the same artist are unordered; fortunately we know that
+# the first artist have 3 cds and the second and third artist both have only
+# one, so the first 3 cds belong to the first artist and the fourth and fifth
+# cds belong to the second and third artist, respectively, and there's no sixth
+# row
+is_deeply (
+ [ $filtered_cd_rs->hri_dump ],
+ [
+ {
+ 'artist' => '2',
+ 'cdid' => '4',
+ 'genreid' => undef,
+ 'single_track' => undef,
+ 'title' => 'Generic Manufactured Singles',
+ 'tracks' => [
+ {
+ 'cd' => '4',
+ 'last_updated_at' => undef,
+ 'last_updated_on' => undef,
+ 'position' => '1',
+ 'small_dt' => undef,
+ 'title' => 'Boring Name',
+ 'trackid' => '10'
+ },
+ {
+ 'cd' => '4',
+ 'last_updated_at' => undef,
+ 'last_updated_on' => undef,
+ 'position' => '2',
+ 'small_dt' => undef,
+ 'title' => 'Boring Song',
+ 'trackid' => '11'
+ },
+ {
+ 'cd' => '4',
+ 'last_updated_at' => undef,
+ 'last_updated_on' => undef,
+ 'position' => '3',
+ 'small_dt' => undef,
+ 'title' => 'No More Ideas',
+ 'trackid' => '12'
+ }
+ ],
+ 'year' => '2001'
+ },
+ {
+ 'artist' => '3',
+ 'cdid' => '5',
+ 'genreid' => undef,
+ 'single_track' => undef,
+ 'title' => 'Come Be Depressed With Us',
+ 'tracks' => [
+ {
+ 'cd' => '5',
+ 'last_updated_at' => undef,
+ 'last_updated_on' => undef,
+ 'position' => '1',
+ 'small_dt' => undef,
+ 'title' => 'Sad',
+ 'trackid' => '13'
+ },
+ {
+ 'cd' => '5',
+ 'last_updated_at' => undef,
+ 'last_updated_on' => undef,
+ 'position' => '3',
+ 'small_dt' => undef,
+ 'title' => 'Suicidal',
+ 'trackid' => '15'
+ },
+ {
+ 'cd' => '5',
+ 'last_updated_at' => undef,
+ 'last_updated_on' => undef,
+ 'position' => '2',
+ 'small_dt' => undef,
+ 'title' => 'Under The Weather',
+ 'trackid' => '14'
+ }
+ ],
+ 'year' => '1998'
+ }
+ ],
+ 'Correctly ordered result',
+);
+
+done_testing;
projects / dbsrgits/DBIx-Class.git / commitdiff