Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Problem with table joins with Class::DBI

by thunders (Priest)
on Dec 24, 2003 at 15:15 UTC ( #316860=perlquestion: print w/ replies, xml ) Need Help??
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;

Comment on Problem with table joins with Class::DBI
Download Code
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();
    HTH.

    The PerlMonk tr/// Advocate
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.

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__
    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); }
    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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://316860]
Approved by jdtoronto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (17)
As of 2014-07-28 18:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (205 votes), past polls