Re: FYI: fully-qualified column names for set_sql JOIN queries

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

On Sun, Mar 13, 2005 at 09:04:19PM +0100, Struan Bartlett wrote:
> 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:

Or, alteratively,

SELECT __ESSENTIAL(me)__ 
  FROM __TABLE(=me)__
...

Tony