I'm considering using Class::DBI for a large project. I've already started the project, but I've been successful in retrofitting Class::DBI into most of my pages, however I run into a snag in stuations where I attempt to order my select statement by a column on a another table (i.e. my Tasks table will have a reference to the owner of a project, and I want to sort by that person's last name.

I've tried the standard Class::DBI->search as well Class::DBI::AbstractSearch, but neither seems to deal with searches on joined tables. I know i could create custom SQL code wth Class::DBI->set_sql, but not without repeating program logic in two places.

The code below shows what I did in the original DBI code, building up a select statement depending on the arguments I receive, can this kind of code be abstracted with Class:DBI or a related module?

my $statement =q{
                        SELECT
                                tasks.id as tid,
                                long_desc,
                                employees.fname as first,
                                employees.lname as last,
                                DATE_FORMAT(start_date,"%b %d %y") as 
+fdate,
                                status.descr as status_d
                        FROM
                                employees,tasks,status
                        WHERE
                                        owner = employees.id
                                        AND status = status.id
                };



if (exists $ARGS{status} && $ARGS{status} >0){
        $statement .= " AND status = $ARGS{status}";
}elsif (exists $ARGS{status} && $ARGS{status} eq "active"){
        $statement .= " AND (status=1 OR status=2)";
}

if (exists $ARGS{owner} && $ARGS{owner} > 0){
        $statement .= " AND owner = $ARGS{owner}";
}

if ($ARGS{order} eq "osd"){
        $statement .=   q{
                        ORDER BY
                                employees.lname, status.descr,start_da
+te
        };
}elsif ($ARGS{order} eq "ods"){
        $statement .=   q{
                        ORDER BY
                                employees.lname, start_date, status.de
+scr
        };
}elsif ($ARGS{order} eq "sdo"){
        $statement .=   q{
                        ORDER BY
                                status.descr, start_date,employees.lna
+me
        };
}else{
        $statement .=q{
                        ORDER BY
                                start_date,employees.lname,status.desc
+r
        };
}

 my $sth = $dbh->prepare($statement) or die $dbh->errstr;
 $sth->execute;
[download]