in reply to Class::DBI and sql order by

I think what perlcgi was trying to say is "use the retrieve_from_sql() method". Anyways ... let's say that you have set up your classes like so (and i am using Class::DBI::mysql for this, by the way) 
package Order::customer;

use base 'Order::DBI';
__PACKAGE__->set_up_table('customer');
__PACKAGE__->has_a(region => 'Order::region');

1;

package Order::region;

use base 'Order::DBI';
__PACKAGE__->set_up_table('region');

1;

[download]
Then you can refer directly to the region name from a customer, and not just by the id. One way to achieve sorting by the region is name is brute force: 
my @customer = Order::customer->retrieve_all;

# region descending
for (sort {$a->region->name cmp $b->region->name } @customer) {
   print $_->region->name, ' => ', $_->name, "\n";
}

# region ascending
for (sort {$b->region->name cmp $a->region->name } @customer) {
   print $_->region->name, ' => ', $_->name, "\n";
}

[download]
But another way is to add two add_constructor() methods to Order::customer like so: 
__PACKAGE__->add_constructor(
   region_asc  => 'region like ? ORDER BY region ASC'
);
__PACKAGE__->add_constructor(
   region_desc => 'region like ? ORDER BY region DESC'
);

[download]
And then you can access those methods like so: 
 
for (Order::customer->region_desc('%')) {
   print $_->region->name, ' => ', $_->name, "\n";
}

for (Order::customer->region_asc('%')) {
   print $_->region->name, ' => ', $_->name, "\n";
}

[download]
The only thing i don't like about the second method is that you have to specify a LIKE clause ... i really wish you didn't have to, as this is wasteful for the database engine. But the first style i presented is wasteful as well, as the database engine should have sorted the data before Perl even got a hold of the data.

This is why i think the best way to handle this is to set up a retrieve_from_sql() method with perlcgi's SQL, although i prefer a more "refined" query myself: 
SELECT customer.id, customer.name, region.name
FROM customer INNER JOIN region
ON customer.region = region.id
ORDER BY region.name DESC
I hope that someone more knowledgeable in Class::DBI matters will prove me wrong. ;)

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

Replies are listed 'Best First'.
Re: Re: Class::DBI and sql order by
by Anarion (Hermit) on Nov 16, 2003 at 11:25 UTC
    I would do something like this:
    __PACKAGE__->set_sql(regions_sorted => qq{
    SELECT customer.id, customer.name, region.name
    FROM customer INNER JOIN region
    ON customer.region = region.id
    ORDER BY region.name DESC
  });
  
sub retrieve_sorted_by_region 
{
    my $class = shift;
    my $sth = $class->sql_regions_sorted;
    $sth->execute
        or carp "Error: $DBI::errstr";
    return $class->sth_to_objects($sth);
}

    [download]

    $anarion=\$anarion;

    s==q^QBY_^=,$_^=$[x7,print
[reply]
[d/l]

In Section Seekers of Perl Wisdom