[Dbix-class] Ordering materialized path searches

[Ovid]

----- Original Message ----

> From: Rob Kinyon <rob.kinyon at gmail.com>

> > I have the following DBIx::Class code.
> >
> >    sub ancestors_rs {
> >        my $self = shift;
> >
> >        my @ids = split /\./, $self->materialized_path;
> >        pop @ids;   # remove self
> >        if (!@ids) {
> >            @ids = ('NOSUCHID'); # XXX :(
> >        }
> >
> >        return $self->_default_resultset('PCE')
> >          ->search( { 'me.id' => { -in => \@ids } } );
> >    }
> >
> >
> > The problem is that I need to walk up the tree from bottom to top but I can't 
> figure out how to order the results correctly (they get returned in an 
> effectively random order).  Thoughts?
> 
> You need to add an orderby on a depth column that you add in based on
> the index of the id in @ids. If you're in mysql, that'll be some huge
> CASE statement. So, something like:

Actually, after walking around and talking to many colleagues, one of them pointed out that I can just order by the length of the materialized path.  It's guaranteed to work because that's an inherent property of how materialized paths are created.  It's very counter-intuitive, but it works :)

Cheers,
Ovid
--
Buy the book         - http://www.oreilly.com/catalog/perlhks/
Tech blog            - http://use.perl.org/~Ovid/journal/
Twitter              - http://twitter.com/OvidPerl
Official Perl 6 Wiki - http://www.perlfoundation.org/perl6