| Subject: | [PATCH] _count() generates incorrect SQL when use GROUP BY and HAVING clause |
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;