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

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

Hi Tony

Thanks for your reply.

Those solutions are OK for single relationships, but I am really 
thinking of multiple relationships, e.g. using an Order object as the 
starting point you could have a Customer, an InvoiceAddress, a 
DeliveryAddress, and multiple OrderProducts.

For example, if I want to find all the orders for a given date, address 
and containing a certain product, I now need to search 3 tables (or 
possibly 4 if orderproducts is a lookup table for a products table). 
This sort of search is quite a common reporting requirement.

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'

Using the previous solution I would have to find all the orders for the 
date in question, then check the address for each, then check whether 
the order contained a certain product. That would work, but would 
require several lines of code to traverse the various relationships. You 
would also need to add further lines of code for each new relationship, 
for example if a SalesPerson was introduced for each Order.

Being able to do this instead:

my @orders = Order->search(
	Order.date = '2005-01-10',
	Addresses.town = 'London',
	OrderProducts.productname = 'foo'
);

Would be more concise and easier to implement, since new relationships 
would only require the addition of paramaters to the search() method, 
rather than special traversal code. The search should then be quite a 
bit more efficient, since the related tables would be added to the FROM 
clause of a single SQL statement generated by the Order class, rather 
than having to be SELECTed individually. And you would still only be 
returning an Order object, so no less efficient in terms of what is 
SELECTed.

I welcome your comments.

Thanks

Will


        -- 
        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