Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 24449
Status: rejected
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: masaki [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Wishlist
Broken in: (no value)
Fixed in: (no value)



Subject: [PATCH] _count() generates incorrect SQL when use GROUP BY and HAVING clause
Download (untitled) / with headers
text/plain 2.1k
ResultSet.pm's _count() replaces GROUP BY clause with COUNT(DISTINCT). However, HAVING clause is always deleted and generates SQL is: SELECT COUNT(DISTINCT(...)) FROM ...; so, can't count following SQL's rows: SELECT ... FROM ... (WHERE ...) GROUP BY ... HAVING ...; in this case, I think _count() should generate following SQL to get correct number of counts: SELECT COUNT(*) FROM ( "__THIS_IS_SUBQUERY__ (__IS_ORIGINAL_SQL__)" ); I want to count with GROUP BY and HAVING clauses, write a following patch. -- masaki === --- ResultSet.pm~ 2007-01-11 17:41:10.000000000 +0900 +++ ResultSet.pm 2007-01-18 22:40:13.000000000 +0900 @@ -896,21 +896,32 @@ my $attrs = { %{$self->_resolved_attrs} }; if (my $group_by = delete $attrs->{group_by}) { - delete $attrs->{having}; - my @distinct = (ref $group_by ? @$group_by : ($group_by)); - # todo: try CONCAT for multi-column pk - my @pk = $self->result_source->primary_columns; - if (@pk == 1) { - my $alias = $attrs->{alias}; - foreach my $column (@distinct) { - if ($column =~ qr/^(?:\Q${alias}.\E)?$pk[0]$/) { - @distinct = ($column); - last; + # with GROUP BY and HAVING clause + if (my $having = delete $attrs->{having}) { + delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/; + @$attrs{ qw/group_by having/ } = ($group_by, $having); + my ($sql, @bind) = $self->result_source->storage->sql_maker->select( + @$attrs{ qw/from select where/ }, $attrs + ); + 1 while $sql =~ s/\?/sprintf "'%s'", shift(@bind)/e; + $attrs = { from => "($sql)" }; + } + else { + my @distinct = (ref $group_by ? @$group_by : ($group_by)); + # todo: try CONCAT for multi-column pk + my @pk = $self->result_source->primary_columns; + if (@pk == 1) { + my $alias = $attrs->{alias}; + foreach my $column (@distinct) { + if ($column =~ qr/^(?:\Q${alias}.\E)?$pk[0]$/) { + @distinct = ($column); + last; + } } } - } - $select = { count => { distinct => \@distinct } }; + $select = { count => { distinct => \@distinct } }; + } } $attrs->{select} = $select;
Subject: dbic-rs-countable.patch
--- ResultSet.pm~ 2007-01-11 17:41:10.000000000 +0900 +++ ResultSet.pm 2007-01-18 22:40:13.000000000 +0900 @@ -896,21 +896,32 @@ my $attrs = { %{$self->_resolved_attrs} }; if (my $group_by = delete $attrs->{group_by}) { - delete $attrs->{having}; - my @distinct = (ref $group_by ? @$group_by : ($group_by)); - # todo: try CONCAT for multi-column pk - my @pk = $self->result_source->primary_columns; - if (@pk == 1) { - my $alias = $attrs->{alias}; - foreach my $column (@distinct) { - if ($column =~ qr/^(?:\Q${alias}.\E)?$pk[0]$/) { - @distinct = ($column); - last; + # with GROUP BY and HAVING clause + if (my $having = delete $attrs->{having}) { + delete $attrs->{$_} for qw/rows offset order_by page pager record_filter/; + @$attrs{ qw/group_by having/ } = ($group_by, $having); + my ($sql, @bind) = $self->result_source->storage->sql_maker->select( + @$attrs{ qw/from select where/ }, $attrs + ); + 1 while $sql =~ s/\?/sprintf "'%s'", shift(@bind)/e; + $attrs = { from => "($sql)" }; + } + else { + my @distinct = (ref $group_by ? @$group_by : ($group_by)); + # todo: try CONCAT for multi-column pk + my @pk = $self->result_source->primary_columns; + if (@pk == 1) { + my $alias = $attrs->{alias}; + foreach my $column (@distinct) { + if ($column =~ qr/^(?:\Q${alias}.\E)?$pk[0]$/) { + @distinct = ($column); + last; + } } } - } - $select = { count => { distinct => \@distinct } }; + $select = { count => { distinct => \@distinct } }; + } } $attrs->{select} = $select;
Download (untitled) / with headers
text/plain 117b
The patch is broken in several ways; we'll implement your proposal correctly once true subselect support is in place.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.