http://www.perlmonks.org?node_id=316860

thunders has asked for the wisdom of the Perl Monks concerning the following question:

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]

Replies are listed 'Best First'.
Re: Problem with table joins with Class::DBI
by perrin (Chancellor) on Dec 24, 2003 at 16:30 UTC
    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.

[reply]
[d/l]
[select]
Re: Problem with table joins with Class::DBI
by Anarion (Hermit) on Dec 24, 2003 at 16:38 UTC
    You can do various things. According to documentation of Class::DBI you can use something like:
    __PACKAGE__->set_sql('update', <<"");
                UPDATE __TABLE__
                SET    %s
                WHERE  __IDENTIFIER__

    [download]
    and then construct the statement on the fly.

    You can use the set_sql method on the fly to create it using a function on the base class of your Class::DBI classes to construct the statements too, something like: 
    sub my_sqlset
{
    my ($self,$name,$statement) = @_;
    # escape your statement here
    $self->set_sql($name,$statement);
}

    [download]
    You can use placeholders to use it latter in a similar search.

    And perhaps use UNIVERSAL to check if you already created that function to not waste time.

    $anarion=\$anarion;

    s==q^QBY_^=,$_^=$[x7,print
[reply]
[d/l]
[select]
Re: Problem with table joins with Class::DBI
by Roy Johnson (Monsignor) on Dec 24, 2003 at 16:19 UTC
    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();

    [download]
    HTH.
    The PerlMonk tr/// Advocate
[reply]
[d/l]

Back to Seekers of Perl Wisdom