Clean OO-structure vs. SQL performance

Question

When programming in PHP I always try to create meaningful 'models' (classes) that correspond to tables in the database. I often encounter the following problem:

Assuming that I've created a database with two tables: authors and blogs, which both have a corresponding model in my application.

Let's say I want to print all the blogs along with information about the author, I'd have to do something like this:

<?php foreach ($app->getBlogs() as $blog) {   echo "<h1>" . $blog->title . "</h1>";   echo "Written by" . $blog->getAuthor()->name . "</p>";   // ... et cetera } ?> 

The problem is that the application will now fire 1 SQL query to get all the blog items, and [number of blog items] queries to get the information for every author. Having used straightforward SQL I could have retrieved this information using a simple query:

SELECT * FROM blogs JOIN authors ON authors.id = blogs.author 

What's the best way of dealing with such issues: developing an object-oriented application without executing too many useless SQL queries.

Answer 1

IMO, I think you should just write another class that will encapsulate what you have. Does it always make sense for a blog to have an author? Does every author have a blog? Can an author have multiple blogs? Think about these issues, then design a class that will encapsulate this. Remember, typical database schemas are not OO... they are relational. Yes, they are close, but there are subtle differences.

So if an author can have multiple blogs, you can have a key to multivalued class of some sort (with the key based on author id) and you can initialize or load this class with one SQL call. Just some things to think about.