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

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

On Mon, 14 Mar 2005, Tony Bowden wrote:

> 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)__
> ...

Thanks Tony.

Or, alternatively, even,

SELECT __ESSENTIAL(__TABLE__)__

I can't find in any of the Class::DBI manpages. Pray tell, where is this
documented?

Struan