Php – How do I use objects to represent data from my relational database without making an excessive number of database queries?

I’m trying to design my first complex object-oriented PHP application and I’m confused about how to use objects to represent data from my database. More specifically I’m confused about how to combine multiple objects without making a huge number of database queries.

A simple example: let’s say I have two database tables:

products: - product_id
          - manufacturer_id
          - name
          - price

manufacturers: - manufacturer_id
               - name
               - factory_address
               - factory_zipcode
               - factory_country
               - // [50 additional columns]

I represent these using two classes:

class Product {}
class Manufacturer {}

Now let’s say I want to create a page which lists 1000 products.

I create an array of 1000 Product objects using a ProductFactory class or similar, something like this:

class ProductFactory {
    function allProducts() {
        $results = db_query("SELECT * FROM products");
        foreach ($results as $result) {
            $product_objects[] = new Product($result);
        }
        return $product_objects;
    }
}

That’s fine. That’s only a single database query and now I have all the product data – great!

But what if I want to create a slightly different page which also displays 1000 products, but also includes data from the manufacturers table?

Sure, I could change my query to use a JOIN to return columns from the manufacturer table as part of my product query, but that doesn’t seem very flexible to me, because:

1. I might not always need manufacturer data, so loading it in the ProductFactory seems wasteful if I only use it on a single page and nowhere else.
2. The manufacturer data might be very extensive, and I don’t want 50 extra columns bloating my query result when all I need is the manufacturer’s name.
3. If I fetched product and manufacturer data in a single query I don’t see how I could elegantly create two separate objects (Product and Manufacturer) from the query results without it being super messy.

I guess I could add a second method to my ProductFactory class, something like:

function allProductsWithManufacturerData() {
    // SELECT * FROM products JOIN manufacturers...
}

But that seems hacky and inflexible. People using my code would not know that they need to specifically call this method if they want to include manufacturer data in the Product objects.

Alternatively I could load the manufacturer data only when it’s explicity requested:

echo $productObject->getManufacturer()->name;

That seems like a fairly flexible approach to me. But in my example above, that would result in 1000 extra queries (one for each product on the page) which would be horrible for performance.

So it seems like I’m being forced into a choice:

1. Choose to fetch all the data you could ever possibly need (using a single JOIN query)
2. Or… choose to fetch the bare minimum data and then use extra queries to fetch data as required.

Are these my only choices or am I missing something? Is there a design pattern I don’t know about that I can use to leverage more flexibility here?