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

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

Lance

Thanks for your reply.

set_sql() is the approach I currently use, I believe I mentioned it in 
the first post of this thread (albeit not explicitly). Sorry if that was 
not clear.

For future reference please allow me to state that I am using the Orders 
/Customers/Addresses problem for illustrative purposes only. I am trying 
to describe a way in which CDBI could be improved rather than solving a 
specific problem.

While set_sql() works, it is a horrible approach. If you are having to 
write SQL statements, you are not doing database abstraction any more. 
And that is contrary to what CDBI is supposed to be about.

CDBI already knows about relationships between tables from the has_a and 
has_many features, so it could quite feasibly search from multiple 
tables and cut out any need to write SQL for that purpose.

For those who have failed to do so (not you Lance), please reply to list 
for the benefit of others.

Thanks

Will


Lance A. Brown wrote:
> Will Hawes wrote:
> 
>>SELECT * FROM orders, addresses, orderproducts WHERE orders.address =
>>addresses.id AND orderproducts.order = orders.id AND orders.date =
>>'2005-01-10' AND addresses.town = 'London' AND orderproducts.productname
>>= 'foo'
> 
> 
> 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 = ?
>   });
> 
> my $orders = OrdersPackage->search_report('2005-01-10', 'London', 'foo');
> 
> will give you an OrdersPackage iterator containing each object that
> matches your select query.  I wrote this off the top of my head so it my
> have typos, but the idea is there.
> 
> --[Lance]
> 


        -- 
        No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.6.9 - Release Date: 06/01/2005