Efficient querying for PHP objects with SQL database

vma

Hello readers,

I've been thinking and wondering for different practices how to design my object-oriented PHP script to use SQL queries efficiently. What I'd like to do is to minimize the amount of round trips to the database but still encapsulate as much business logic as possible inside the classes.

I have a handful of PHP business objects: Customer, Order and OrderItem, all of them populated from a database. In addition to them, I have some helper classes CustomerManager, OrderManager etc. that makes generic queries (for example CustomerManager::LookupCustomerByName() ) and returns the matching objects (Customer) in an array.

Currently, this has led into a well-known "n+1" situation. LookupCustomerByName() makes a call to the database fetching id's of each matching customer. After that, looping through the result set and building each Customer object makes (at least) one call to the database to fetch object properties. Not a problem yet - but will be in future.

This pattern is not very efficient as soon as I'd like to make queries involving multiple objects, let say, looking up all order items that have ordered by a customer from a specific postal code area. This would currently need
1) Lookup for customers from the postal code area (1 query & let's say 20 results)
2) Building each customer object (20 queries)
3) Looking up orders for each customer (20 queries = let's say 3 results per customer = 60 results)
4) Looking up order items for each order (60 queries)

Now I've done 101 queries (1 + 20 + 20 + 60) to the database - for a simple operation that would've been able to done with a very simple relational database SQL query!

OK - you may now ask that why don't I do the simple relational database query... Doing that would couple the business logic pretty thight with the database structure and that's something I'd like to avoid.

I've been thinking for different solutions and best practices, for example
- storing the objects in a memcached server (this would lower the database queries significantly),- making special manager objects, for example OrderItemOfCustomerLookupManager that takes care of database relations

Do you have some resources, thoughts or any other ideas that could help me to clarify these things?

Thanks,
Ville

laserlight

vma wrote:
OK - you may now ask that why don't I do the simple relational database query... Doing that would couple the business logic pretty thight with the database structure and that's something I'd like to avoid.

But if you abstract away this query with a function, like how you intend to use CustomerManager::LookupCustomerByName(), wouldn't this coupling no longer be a significant problem?

vma

laserlight;10928903 wrote:
But if you abstract away this query with a function, like how you intend to use CustomerManager::LookupCustomerByName(), wouldn't this coupling no longer be a significant problem?

Thanks for your reply. Abstracting the query into a function is so far the best practice. However, there are still some cons with this approach: for example fetching the result objects still needs a new query for each object. Also if I wanted to abstract also the Name property of the Customer, making changes to the database structure would be easy - but it should be updated in all manager/l9ookup objects too.

One pattern I've been thinking is to provide a constructor that gets a database row as a parameter. The Manager class could make a database query (

SELECT customers.* FROM customers, orders WHERE customers.id = orders.customer_id AND orders.orderdate = TODAY();

, feed the whole result row to the constructor

$customer = new Customer($result_row);

that fills up the properties of the newly created object. This approach would create a new object immediately with the latest data.

One step further: maybe using a factory function

Customer::Factory($id, $result_row)

is also efficient way to accomplish this. If we find the customer object already cached from the memory by it's ID, we can use it, else we can construct a new object immediately without making a new query to the database.

And the discussion continues... 😉

Ville