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

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

I recently posted to the CDBI Wiki Wish List and was referred here.

I need to search for an Order object via a field of a related object, for example a Customer.surname. So if writing an SQL statement my SQL would be:

SELECT * FROM order, customer WHERE order.customer = customer.id AND customer.surname = ...

So, using CDBI, Order->has_a(customer => 'Customer').

At present, I can see 3 ways of doing the search using CDBI.

1) Do Order->retrieve_all() to return all the Order objects in an array. Then loop through the array and check the surname of each Customer. This is a memory intensive and slow approach.

2) Write custom sql to do a search using the Order and Customer tables. This is more efficient, but a pain to maintain because I need many similar relationships for my whole database.

3) Use views/stored procedures in the database. Unfortunately not supported by my rdbms (mySQL 4).

It would be very, very useful if Class::DBI subclasses allowed searching on fields in a related table as well as just the main table. So in this instance I would be allowed to do

Order->search_like(Customer.surname => '%foo%')

No part of the Customer object would be returned by the search - just an Order object as would normally be the case.

CDBI already knows about relationships between tables, so I would imagine this would be quite trivial to implement for someone with knowledge of CDBI internals. Essentially a case of changing the base SQL statement used by any CDBI class so that related tables are included in the FROM clause. And of course field names in search() and search_like() will need to be prefixed with the table name if a table has any related tables.

Any thoughts?

Regards

Will Hawes