How to reduce the number of MySQL queries using object oriented code

Question

Take a simple example: I have products. Products can be in 1 or many categories, as well as in 1 or more channels.

So, I have an object for the product, category and channel, as well as their display and factory classes.

My approach number 1 should be 100% untied; ProductFactory simply creates an instance of the Product. And inside the product class, I have a lazy instanciation method that calls the category and factory channel and saves the object as needed.

This is great for the product details page, but when it comes to the product list, my query counter becomes very high, since I have to make 2 additional requests for each product and let it look: when I show the list of products in category No. 2, this completely insane to have a 25x match of the same category for each product.

My approach # 2 is to put things together a little; ProductFactory makes several JOINs and gets all the information about the category and channel, and also creates them. IMO, which is just as nasty as the first solution, because the number of requests becomes low, I just run a larger request, so in the background it does not change anything.

My No. 3 approach is to keep things loose, but not have chain automation; First, I would have to iterate over the entire product and create an array of the entire category and the entire channel, manage the duplicate identifier, and then initiate all of them. This seems nice, but the code is no longer chained. (That is: I cannot do $ product-> getCategory () to get a list of the category in which the product is stored.)

What approach do you suggest?

Answer 1

What if you save the array in format

CATEGORY_ID => CATEGORY_OBJECT 

Then, when you need to assign a category, you can check this array first. For instance:

 $page_categories = array(); foreach( $products as $product ) { if ( isset( $page_categories[$product->category_id] ) ) { $product->category = $page_categories[$product->category_id]; } else { $product->category = $page_categories[$product->category_id] = new Category( $product->category_id ); } }