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

[prev] [thread] [next] [Date index for 2005/02/17]

From: Matt S Trout
Subject: Re: Feature request - Allow searching columns of related tables in search() and search_like()
Date: 23:12 on 17 Feb 2005
On Thu, Feb 17, 2005 at 09:21:05PM +0100, Hartmaier Alexander wrote:
> Any news on this?
> I'd need searching and ordering by fields of foreign tables all the time!
> 
> I didn't look at the CDBI internals but wouldn't it be the easiest way to create an sql query which just returns all matching primary id's of the searched object and then retrieve all ESSENTIAL columns?
> 
> The 'order by' can be done the same way...

It can, but it's not an amazingly efficient one. One alternative is to do
something like

__PACKAGE__->set_sql(Retrieve => <<EOSQL);
SELECT __ESSENTIAL__
FROM __TABLE__, __TABLE__(Join::To::This), __TABLE(Join::To::That)
WHERE __JOIN__(table this that)
AND %s
EOSQL

and then you should be able to do

Class::Name->search( 'table.foo' => 1, 'this.bar' => 'Spoo',
                        { order_by => 'that.baz' });

and it'll all work fine.

I've been thinking about packaging this up as a general case, but am unsure
whether it should be a plugin and what syntax to use if I do. Suggestions?

        -- 
            Matt S Trout            Brag sheet:    http://trout.me.uk/services.html
LAMP, Infrastructure        Contact:       services@xxxxx.xx.xx
   and Automation
     specialist                                       Do it once. Do it right.

(message missing)

Re: Feature request - Allow searching columns of related tables in search() and search_like()
Matt S Trout 23:12 on 17 Feb 2005

Generated at 12:48 on 22 Feb 2005 by mariachi v0.52