arc_of_descent has asked for the wisdom of the Perl Monks concerning the following question:


Hi Monks,

This is not really a perlish question, although I am developing a Web App using Perl, Class::DBI and the Template Toolkit. Its more a SQL question, and so excuse me if I've run the wrong way.

First off - Is there any way to specify a "order by" phrase in the retrieve_all method of the Class::DBI module? Is there any way, I can specify the 'ASC' or 'DESC' words, apart from using retrieve_from_sql?

Now for the SQL!
I have the following 2 tables:

Table customer:
codenameregion
1Me1
2Myself2
3You3

Table region:
idname
1Mumbai
2Delhi
3Chennai

Now I would like to sort and retrieve the customer table, based on region name and not on region id, which the following SQL statement does:
SELECT * FROM customer ORDER BY region
I tried the following:
SELECT customer.* FROM customer, region ORDER BY region.name
but i'm getting horribly stuck!

Any help would be most welcome.
Thankz
--
arc_of_descent

Replies are listed 'Best First'.
Re: Class::DBI and sql order by
by jeffa (Bishop) on Nov 15, 2003 at 16:12 UTC
    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)
[reply]
[d/l]
[select]
      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]
Re: Class::DBI and sql order by
by perlcgi (Hermit) on Nov 15, 2003 at 15:02 UTC 
    SELECT * from customer,region 
   WHERE 
      customer.region=region.id 
   ORDER BY 
      region.name

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom