Re: Feature request - Allow searching columns of related tables in search() and search_like()

[prev] [thread] [next] [Date index for 2005/01/10]

On Mon, Jan 10, 2005 at 05:48:29PM +0000, Tony Bowden wrote:
> On Mon, Jan 10, 2005 at 08:59:38AM -0500, Lance A. Brown wrote:
> > Sounds like a perfect opportunity for 'set_sql':
> > OrdersPackage->set_sql(report => qq{
> >    SELECT orders.* FROM orders, addresses, orderproducts WHERE
> > orders.address = addresses.id AND orderproducts.order = orders.id AND
> > orders.date = ? AND addresses.town = ? AND orderproducts.productname = ?
> >   });
> 
> Except you don't need to do it like that.
> 
> As Will has pointed out, CDBI already knows lots of this.
> 
> You could do:
> 
> Order->set_sql(oap => q{
> 	SELECT __ESSENTIAL(o)__
>     FROM __TABLE(=o)__, __TABLE(Address=a)__, __TABLE(Product=p)__
>    WHERE JOIN(o a p)
>      AND %s
> })
> 
> and then do your substitution in the same was as search() normally
> works, except you can prefix your columns with whatever abbreviations
> you've used for the tables.

I've had a play with this, and it's lovely; one thing it lacks though is
the ability to pull e.g. has_a rels at the same time and auto-populate the
relevant objects.

It'd also be really nice if ->search allowed you to specify constraints on
multiple tables and automatically did the appropriate joins to allow this.

I'd be willing to work on implementations of the above but I'm uncertain
how best to do so (and whether I should aim for a patch to Class::DBI or
an add-on of some sort).

        -- 
        Bring me my etherkiller; Oh clouds unfold! / Bring me the magic smoke of desire
I shall not cease from mental fight / Nor shall my LART rest in my hand
Till we have buried the bodies / Of all the lusers in all this land
  -- rpg, ASR                        [ My homepage is http://www.trout.me.uk/ ]