Problem with table joins with Class::DBI

[thunders]

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;

[Roy Johnson]

According to perldoc Class::DBI:

Of course, any query can be added via set_sql, including joins. So, to add a query that returns the 10 Artists with the most CDs, you could write (with MySQL):

        Music::Artist->set_sql(most_cds => qq{
                SELECT artist.id, COUNT(cd.id) AS cds
                  FROM artist, cd
                 WHERE artist.id = cd.artist
                 GROUP BY artist.id
                 ORDER BY cds DESC
                 LIMIT 10
        });

        my @artists = Music::Artist->search_most_cds();

HTH.

[perrin]

Class::DBI doesn't directly handle this situation, partly because it isn't clear how what you are doing should translate into objects. You have a reporting query which brings back a mix of fields from three tables. What class of object should you end up with?

One way to do it is to use Class::DBI::View. That lets you use arbitrary SQL queries and get back a list of objects that correspond to rows, although they are not editable.

Another approach would be to break this down into objects and relationships. If you select a task, you should be able to call $task->employee() or something similar to get an employee object, and $task->status() to get a status object. This is more how Class::DBI is intended to be used.

As for the ordering, you can do that in perl, e.g. sort by $task->employee()->lname(). If you need to do it in the database, you can create multiple constructors in your Task class, using the set_sql() method to allow them to join other tables. Set up one of these for each possible sort and then call the appropriate one.

There are other approaches, but hopefully that will get you started.

[Anarion]