FYI: fully-qualified column names for set_sql JOIN queries

[prev] [thread] [next] [Date index for 2005/03/13]

Hi,

I thought I'd share with you a solution to a problem you might come across.

You have a class representing a company and you want to search for a 
particular type of company. You want to set up this search using 
set_sql. You don't want to have to duplicate a list of all the table 
columns in the query - after all, you've already declared them at the 
top of the package and duplication would be a ready source of integrity 
problems down the line if you renamed the columns or the table or added 
or removed any columns.

If you're familiar with Class::DBI I suspect you might be thinking you 
need something like this:

package MyProg::Company;

MyProg::Company->table('Company');
MyProg::Company->columns(Primary => qw/ID/);
MyProg::Company->columns(Essential => qw/Stamp Name Address Status_FK 
Contact_FK/);
MyProg::Company->has_a( 'Status_FK' => 'MyProg::CompanyStatus' );
MyProg::Company->has_a( 'Contact_FK' => 'MyProg::Person' );

__PACKAGE__->set_sql( 'qualified' => qq{
                                           SELECT __ESSENTIAL__
                                                from __TABLE__
                                           where Status_FK in (10,20,30)
                                       }
);

The problem is, what if you need to search according to a joined 
criteria? You can't say this:

__PACKAGE__->set_sql( 'qualified' => qq{
                                           SELECT __ESSENTIAL__
                                                from __TABLE__
                                           left join CompanyStatuses on 
Company.Status_FK = CompanyStatuses.ID
                                           where CompanyStatuses.Type >= 500
                                       }
);

...because __ESSENTIAL__ is substituted for "Stamp, Name, Address, 
Status_FK, Contact_FK" yet the database needs fully-qualified column 
names to distinguish them from those in the CompanyStatuses table, 
should there be any overlap (and in my case there was).

This is what you can do instead:

package MyProg::DBI;

sub FQcolumns {
    return join(', ', map { __TABLE__ . ".$_" } $_[0]->columns($_[1]));
}

package MyProg::Company;

[as above]

__PACKAGE__->set_sql( 'qualified' => qq{
                                           SELECT 
@{[__PACKAGE__->FQcolumns('Essential')]}
                                                from __TABLE__
                                           left join CompanyStatuses on 
Company.Status_FK = CompanyStatuses.ID
                                           where CompanyStatuses.Type >= 500
                                       }
);

See also: 'man perlref' for the @{[]} trick.

Hope you find this useful (and not completely the wrong way of 
addressing this problem).

Incidentally, I can't think of a reason why __ESSENTIAL__ shouldn't be 
substituted for "Company.Stamp, Company.Name, Company.Address, 
Company.Status_FK, Company.Contact_FK" - can anyone suggest why this 
shouldn't be changed in the Class::DBI code?

Struan