On 8/30/06, Kurt Hansen <[EMAIL PROTECTED]> wrote:
> I need to do sum, max, avg, group by on columns in my database. However,
> I'm not sure the best way to do it in RDBO. The count() method in
> Manager makes me think that there may be some pre-existing methods to do
> these tasks.
>
> If not, is it best to create a method using QueryBuilder and Manager?
> I'm guessing it would be something analogous to count(). Where can I
> look to find an example of how the count method is built and integrated
> into Manager?
The get_objects_count() method exists because it's a well-known,
well-defined operation: tell me how many objects (from the primary
table) would have been fetched had I called get_objects().
The get_objects() method always returns collections of "row objects"
(objects derived from Rose::DB::Object), meaning that each thing
returned is an object of that Manager's object_class. You can take
each object returned by get_objects(), modify it, save(), re-load()
it, and so on.
Once you add an aggregate function like SUM() or AVG() to the query,
however, the data returned from the database does not correspond to
rows in a particular table. Instead, it's aggregate data derived from
many table rows. Obviously, the things returned cannot be "row
objects" and cannot be save()d and so on.
I eventually plan to add a Manager method for constructing queries
like this, but I'm still trying to decide exactly what it will return.
In the meantime, the way to do it is with "canned" queries wrapped in
custom Manager methods that you write yourself.
You can do this totally from scratch, using plain DBI, perhaps also
grabbing some metadata from row class (e.g., the table name below):
package My::Product::Manager;
...
sub get_average_prices_by_type
{
my($class) = shift;
my $meta = My::Product->meta;
my $sql = 'SELECT type, AVG(price) FROM ' . $meta->table .
'GROUP BY type';
my @rows;
eval
{
my $dbh = My::DB->new->retain_dbh;
local $dbh->{'RaiseError'} = 1;
my $sth = $dbh->prepare($sql);
$sth->execute;
while(my $row = $sth->fetchrow_hashref)
{
push(@rows, $row);
}
$dbh->disconnect;
};
die "Could not get average prices: $@" if($@);
return wantarray ? @rows : [EMAIL PROTECTED];
}
That's not very flexible, but it's totally under your control. (It
also tends to be fast, since you're using DBI directly.) If you want
to support customization of the query, you can use some undocumented
features of the Manager to help build the SQL:
package My::Product::Manager;
...
sub get_average_prices
{
my($class, %args) = @_;
# group_by is an undocumented Manager parameter that
# only works with get_objects_sql()
$args{'group_by'} ||= 'type';
$args{'select'} = "$args{'group_by'}, AVG(price)",
my($sql, $bind) = $class->get_products_sql(%args);
my @rows;
eval
{
my $dbh = My::DB->new->retain_dbh;
local $dbh->{'RaiseError'} = 1;
my $sth = $dbh->prepare($sql);
$sth->execute(@$bind);
while(my $row = $sth->fetchrow_hashref)
{
push(@rows, $row);
}
$dbh->disconnect;
};
die "Could not get average prices: $@" if($@);
return wantarray ? @rows : [EMAIL PROTECTED];
}
With this version of the method, you can take advantage of the
built-in query parsing abilities:
# Customize WHERE clause
$rows =
My::Product::Manager->get_average_prices(
query => [ name => { like => '%foo%' } ]);
You can also customize the grouping:
# Group by something different
$rows =
My::Product::Manager->get_average_prices(
query => [ name => { like => '%foo%' } ],
group_by => 'type, in_stock',
sort_by => 'type');
And so on. Again, this technique uses the undocumented Manager
features. Those features probably won't go away until some "official"
way to do the same thing appears, but no guarantees.
Both of these techniques are not ideal, but if you hide the
implementation in the Manager class, then your "end-user" code never
has to know how these methods are implemented. When better support
for this type of thing finally does arrive, you can swap out the
implementation (perhaps adding adapter code to make the APIs match up)
and you won't have to modify any code outside the Manager.
(IME, Manager classes usually grow to contain a whole range of
methods, from straight call-throughs to base class Manager methods,
all the way down to entirely custom, vendor-specific SQL and database
procedure calls. The point of having a separate Manager class is to
hide the details of how each method is implemented and centralize the
code for them.)
Speaking of better official support for aggregate queries, if anyone
has any ideas about what a generic API should return in lieu of row
objects, I'm all ears.
-John
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
