Re: [RDBO] One-to-many and aggregates

[John Siracusa]

On 10/25/07, Arshavir Grigorian <[EMAIL PROTECTED]> wrote:
> I found an old post on the subject
> (http://osdir.com/ml/lang.perl.modules.dbi.rose-db-object/2006-08/msg00077.html)
> but am not sure how to implement this in a one-to-many context.

The approach is the same, just the details are different.  A better
way to do this kind of thing is sorely needed, and is planned
(eventually...)  In the meantime, below is a fairly robust way to
implement it for any relationship (though the details for a 1-to-many
"prices" relationship are hard-coded in the method).  You can call it
with filters too:

    $foo_avg = $o->average_price([ name => { like => 'Foo%' } ]);

-John

---

sub average_price
{
  my($self) = shift;

  my %args;

  if(my $ref = ref $_[0])
  {
    if($ref eq 'HASH')
    {
      %args = (query => [ %{shift(@_)} ], @_);
    }
    elsif(ref $_[0] eq 'ARRAY')
    {
      %args = (query => shift, @_);
    }
  }
  else { %args = @_ }

  my $meta         = $self->meta;
  my $relationship = $self->meta->relationship('prices');
  my $ft_columns   = $relationship->key_columns;
  my $query_args   = $relationship->query_args || [];
  my $mgr_args     = $relationship->manager_args || {};

  my $average;

  # Get query key
  my %key;

  while(my($local_column, $foreign_column) = each(%$ft_columns))
  {
    my $local_method = $meta->column_accessor_method_name($local_column);

    $key{$foreign_column} = $self->$local_method();

    # Comment this out to allow null keys
    unless(defined $key{$foreign_column})
    {
      keys(%$ft_columns); # reset iterator
      $self->error("Could not get average via average_price() - the " .
                   "$local_method attribute is undefined");
      return;
    }
  }

  # Merge query args
  my @query = (%key, @$query_args, @{delete $args{'query'} || []});

  # Merge the rest of the arguments
  foreach my $param (keys %args)
  {
    if(exists $mgr_args->{$param})
    {
      my $ref = ref $args{$param};

      if($ref eq 'ARRAY')
      {
        unshift(@{$args{$param}}, ref $mgr_args->{$param} ?
                @{$mgr_args->{$param}} :  $mgr_args->{$param});
      }
      elsif($ref eq 'HASH')
      {
        while(my($k, $v) = each(%{$mgr_args->{$param}}))
        {
          $args{$param}{$k} = $v  unless(exists $args{$param}{$k});
        }
      }
    }
  }

  while(my($k, $v) = each(%$mgr_args))
  {
    $args{$k} = $v  unless(exists $args{$k});
  }

  $args{'object_class'} = $relationship->class;

  my $debug = $Rose::DB::Object::Manager::Debug || $args{'debug'};

  # Make query for average
  eval
  {
    my($sql, $bind) =
      Rose::DB::Object::Manager->get_objects_sql(
        select => [ \q(AVG(price)) ],
        query => [EMAIL PROTECTED], db => $self->db, %args);

    $debug && warn "$sql (", join(', ', @$bind), ")\n";

    my $sth = $self->db->dbh->prepare($sql);
    $sth->execute(@$bind);

    $average = $sth->fetchrow_array;
  };

  if($@)
  {
    $self->error("Could not average $args{'object_class'} objects - " .
                 Rose::DB::Object::Manager->error);
    $meta->handle_error($self);
    return wantarray ? () : $average;
  }

  return $average;
}

-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems?  Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object