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 User
s and related PhoneNumber
s. 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 Order
s and the OrderItem
s 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 OrderItem
s and push them to each $clientOrder
, either way I definitely know I'm not going to be querying the DB in an iteration.