PHP: refactoring to OO -- How to model Joins

Question

Let's say I have two models:

class Book
{
    public $book_id;
    public $book_author_id;
    public $title;
}

class Author
{
    public $author_id;
    public $author_name;
}

I'm used to writing something like this:

$DB->query("SELECT book_id, title, author_name 
            FROM book 
            LEFT JOIN author 
                ON book_author_id = author_id
           ");

Let's assume, that I'm not interested in having separate queries for this association. How do I proceed? Here are some things I've heard:

• create a MySQL VIEW of the JOIN
• create a model class of the VIEW

The application I'm working on involves dozens of tables and was highly optimized in procedural code (almost no SELECT * anywhere, for instance). I'm refactoring to make it more maintainable (I'm the original creator, too), but I would like to have the flexibility of using joins when I need to without compromising the structure of my files and DB calls.

A possibly related question I have is related to including other models:

class Author
{
    public $author_id;
    public $author_name;

    /* @var Book */ //<--don't really fully understand this but I've seen something like it somewhere
    public (array) $authors_books;
}

I'm still searching for answers, but if you could send a link my way, that would be appreciated.

Answer 1

What you are calling "models" are actually domain objects. They should be responsible for dealing with domain business logic, and have nothing to do with storage.

The storage-related logic and interaction should be handled by separate group of objects. One of most sensible solution is using data mappers. Each mapper can deal with multiple tables and complicated SQL.

As for your query, the result of such query would contain information, appropriate for passing to a collection of domain objects.

BTW, that query is quite useless. You forget that each book can have multiple authors. Take for an example this book - it has 4 different authors. To make this query useful, you should have to do GROUP_CONCAT() based on either author_id or book_id.

When implementing such JOIN statements, the database response most likely will be a collection:

$mapper = $factory->buildMapper('BookCollection');
$collection = $factory->buildCollection('Book');

$collection->setSomeCondition('foobar');
$mapper->fetch( $collection );
foreach ( $collection as $item )
{
    $item->setSomething('marker');
}
$mapper->store( $collection );

P.S. Your code example seem to be leaking abstraction. It is a bad practice to let other structures to access object's variables directly.

P.P.S. It seems that your understanding of model part of MVC is quite different from how i see it.

Answer 2

Database joins are an artefact of relational databases, you don't need to model them. You need to model what your data is and how it behaves, e.g. you might have a getBooks() method in your Author instances, or a static getByAuthor() method in your Book class (generally, $author->getBooks() should be implemented as Book::getByAuthor($this), so your Author class shouldn't be concerned with Book's implementation details). It's not always a good idea to automatically instantiate all related data (e.g. instantiate Book instances for all books by a given Author instance, as you seem to be considering with your $author_books property), since this might easily degrade into a "load the entire database to memory for each request" scenario.

Answer 3

If you're trying to model your DB in classes, this is a problem that's already been solved.

I suggest you try out the Doctrine framework, which is a full ORM framework for PHP.

Hope that helps.