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)

In reply to Re: Class::DBI and sql order by by jeffa
in thread Class::DBI and sql order by by arc_of_descent

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.