[CDBI] How to sort "has_many" relation after I've declared it?

Edward J. Sabol Edward.J.Sabol at nasa.gov
Tue Aug 21 01:30:19 BST 2012


> are there any magical arguments (or something) one could pass to
> $cd->tracks() to sort the results? I want to do the sorting on the
> database side (which could well be a bad idea, but that's an empirical
> question).

The relevant method in the source code is _hm_run_search() in
Class::DBI::Relationship::HasMany.

http://cpansearch.perl.org/src/TMTM/Class-DBI-v3.0.17/lib/Class/DBI/Relationship/HasMany.pm

> In my particular case (which is basically the same as the Music::CD
> case, so I don't bother listing it), my naive attempt was to just ask:
>
> 	my (@s) = $d->schools( { order_by => 'name' } ) ;
>
> but got the message
>
> order_by is not a column of School at
> /usr/lib/perl5/site_perl/5.8.8/Class/DBI/Search/Basic.pm line 115.

Looking over my mailing list archives, I see at least two have asked this
question, but no good solution was offered other than sorting in Perl or
redefining the has_many relationship. Apparently, Class::DBI worked as you
naively guessed it should work somewhere in the v0.90-0.95 range, but then
the code changed....

Based on my reading of the current code, I think it would be worth trying the
following:

       my @s = $d->schools({ { order_by => 'name' } });

I.e., pass the order_by in a hash of a hash instead of a single-level hash.
But it looks like that would only work if you did *not* include an order_by
in the declaration(?) for the has_many() relationship. Otherwise,
@search_args would end up with two order_by hashes, as you can see in the
following code snippet from _hm_run_search()

	my ($self, @search_args) = @_;
	@search_args = %{ $search_args[0] } if ref $search_args[0] eq "HASH";
	[...]
	unshift @search_args, %{ $args->{constraint} }
	   if defined($args->{constraint}) && ref $args->{constraint} eq 'HASH';
	unshift @search_args, ($f_key => $self->id);
	push @search_args, { order_by => $args->{order_by} }
	   if defined $args->{order_by};

If that doesn't work and you really need the sorting to be done in the
database query rather than doing it in Perl, I think your best bet is to
modify the code, by either redefining
Class::DBI::Relationship::HasMany::_hm_run_search() or by copying HasMany.pm
to a new name and defining a new relationship that works the way you want it.

Hope this helps,
Ed



More information about the ClassDBI mailing list