Re: [CDBI] order_by with joined tables?

On 11/2/06, Alan <[email protected]> wrote:
> Hey all.  I'm wondering if there's a way to order by a field in a joined
> table.  For example, I have:
>
> table company
>         id
>         name
> table people
>         id
>         fullname
>         company_id
>
> package Company;
> ...
> package Person;
> ...
> Person->has_a( company_id => 'Company' );
>
> I can of course get results sorted by any field in the Person class:
>
> @array = Company->search_where( { fullname => 'alan' }, { order_by =>
> 'fullname' } );
>
> However what I want to do is order by the name of the company in the
> join because the order by company_id doesn't work.
>


Try using Class::DBI::Sweet, then I believe you can do something like:

Company->search( {'company_id.fullname' => 'alan'}, {order_by => 'fullname'})


If you have to resort to using set_sql, I have a little snippet of
code that might be helpful and make the SQL a bit more reusable.

package MyApp::DBI;

...

# Add to CDBI base class
sub set_search_where_sql {
    my $proto = shift;
    my $class = ref $proto || $proto;
    my ( $name, $sql ) = @_;

    my $method = "search_where_" . $name;
    $class->set_sql( $name, $sql );
    my $sql_method = "sql_" . $name;

    no strict 'refs';
    *{"$class\::$method"} = sub {
        my $proto = shift;
        my $class = ref $proto || $proto;
        my $where = ( ref $_[0] ) ? $_[0] : {@_};
        my $attr  = ( ref $_[0] ) ? $_[1] : undef;
        my $order = ($attr) ? delete( $attr->{order_by} ) : undef;
        my $sql   = SQL::Abstract->new(%$attr);
        my ( $phrase, @bind ) = $sql->where( $where, $order );
        $phrase =~ s/^\s*WHERE\s*/AND /i;

        my $sth = $class->$sql_method($phrase);
        $class->sth_to_objects( $sth, \@bind );
    };
}

package MyApp::Article;
use base 'MyApp::DBI';

...

# setup a
__PACKAGE__->set_search_where_sql( 'big_search' => qq~
   SELECT distinct FROM article a, author_article aa, author a1
   WHERE aa.article_id = a.article_id
   AND a1.author_id = aa.author_id
   %s
~);


package main;

MyApp::Article->search_where_big_search( { author => { '!=' => 'Oliver'  } } );