How to get related objects with pure PHP and PDO without n+1 inefficiency?

Question

Summary

How do you get objects and their related objects using pure PHP and PDO without running into N+1 hell?

---

I'm trying to understand the fundamental way to instantiate objects and their relations without using a framework or ORM. Typically when I ask and provide examples, I get responses like "you would never use results that way", etc and it blows my mind considering the examples are so common in the real world I use these types of queries everyday in procedural code...

So, let's say you have a User class, and a PhoneNumber class. A User can have many PhoneNumbers. Well, that's a perfectly normal real-world use-case. You want to get all Users and related PhoneNumbers. How would you retrieve results from PDO, instantiate the User and populate each User's private $phoneNumbers = []; property? My greenhorn OOP mind would say foreach over an associated result set, sorting it by user_id and then looping over that result set instantiating a new User(), setting its properties and looping over each user, instantiating a new PhoneNumber() and pushing it to the property, but that most definitely seems "wrong".

When people say those types of examples are not common-place (still shocked), I always go back to the Amazon/e-commerce example. You have an Order History page. That would have an Order class and an OrderItem class. You can view all of your Orders and the OrderItems purchase on all orders on a single page.

Since I don't want to get pounced on for not having any code, this is definitely incorrect, but this is what I'm trying to understand the "how" of how to instantiate results of object's, and related results for original object's.

In procedural I could do this in a one-query solution. I'd expect at maximum two queries in an OOP solution. One that gets all Orders for a user and one that gets all OrderItems. In the below snippet. $clientOrders contains all order's from a client. Great, I now have all order_id's I would need to get OrderItems from but how to get those results and populate them against each Order?

$orderRepo = new OrderRepository(); // This is a must.
$orderItemsRepo = new OrderItemRepository(); // Unsure if this would be needed/used.

$clientOrders = $orderRepo->byClientId($clientId);

foreach ($clientOrders as $clientOrder) {

$orderItemsRepo->byOrderId($clientOrder->getId()) // definitely wrong

}

So, I'm fairly confident the foreach is not proper, I thought it'd be used to instantiate OrderItems and push them to each $clientOrder, either way I definitely know I'm not going to be querying the DB in an iteration.

Answer 1

What you're trying to avoid is the looping query for each order's order items.

You want to have a constant number of queries, not N+1 where the 1 is the original query for the list of orders and the N are subqueries required to get each orders order items as you iterate over the orders.

I'd suggest instead of one query per $clientOrder:

• Batch all the $clientOrder id's together in an array, then combine to make comma separated list
• Create a select in that allows you to pass in your list of id's
• Assuming the order items have a client order id, then loop through the results of order items and add them to the appropriate $clientOrder manually

Whether it's orders to items or users to phone numbers I'm assuming you've got 2 tables where the it's a one-to-many type relationship based on the id of the outer object (user or order).

In clauses aren't easy last time I checked PDO, but here's a link to show an example: https://stackoverflow.com/a/14767651/722263

If you do this, you've now go 1 query to pull the client orders and 1 query to pull all the order items (or users to phone numbers as earlier in your post).

Here's some psuedo PHP:

$orders = [];

for(getAllOrders() as $order) {
   $orders[$order->getId()] = $order;
}

for(getAllOrderItemsByOrderIds(array_keys($orders)) as $orderItem) {
   $order[$orderItem->getOrderId()].addOrderItem($orderItem);   
}

Where getAllOrders() calls a single PDO query to get all the orders using fetch class and getAllOrderItemsByOrderIds() creates a single PDO select to return all the order items also with fetch class.

You could even combine the two queries into a single one (as you note), return a massive result set, and manually build each class instead of using PDO FETCH_CLASS, but I find this sort of thing a micro optimization, especially when you get more and more relationships to deal with. I try to keep queries tied to the object they're constructing, not return multiple different entities in giant flat result sets unless there is a major performance need involve. Makes it easier to deal with caching as well.