Let's say I have two tables: Book
and Category
. Book
has a foreign key to Category
. Book can only have on category. Let's say I want to display this table:
Book Title | Category ID | Category Name
-------------------------------------------------------------------------
Lord of the Rings | 1 | Fiction
Ender's Game | 2 | Science Fiction
Purpose Driven Life | 3 | Religious
With domain model and data mapper (Fowler's definition), we have Book
and Category
objects, each with their own mappers. The problem here is since both are separated entities, I end up with lots of queries run to display the above simple table:
$booksArray = $bookMapper->getTopTenBooks();
$viewData = array();
foreach ($booksArray as $book)
{
$categoryID = $book->getCategoryID();
$category = $categoryMapper->getByID($categoryID);
$viewData[] = array(
'book' => $book,
'category' => $category
);
}
return $view->load($viewData);
That's one query to get the list of books to display, along with many queries to get the appropriate category objects related to each books.
The thing is, with SQL, I can slice the data any way I want. I can actually get all the data I need with one query:
SELECT
book.name, category.name
FROM
book LEFT JOIN category ON (book.cat_id = category.id)
Can I use this SQL while still maintaining separation between presentation, data source and domain logic layer? If I can, how? How can we effectively slice data we wanted to display?