Everyone who is using a relational database and has a good concept, meaning a loose coupling and a low redundancy of the data, needs from time to time complex/multipart queries in (My)SQL.
Zend Framework is supporting relationships within the Zend_Db class, which are quite nice implemented. But there is one big restriction: You need a concrete row to access the dependent rows declared in the model. For larger queries, e.g. for an overview or a list containing multiple rows, the relationships can be built with different solutions. All in all, I have found three different ways of establishing the relationship for one concrete row:
- Build a View Helper (or a function) an run it on every row.
- Use the Join-Command within Zend_Db_Table respectively Zend_Db_Select.
- Run complex queries constructed in SQL-Language over Zend_Db.
Previous to an example of the last method, I want to discuss some Pros and Cons of the presented solutions.
View Helpers
The View Helpers are a nice solution. The function can be designed for re-usability and the Helper itself can be registered with the bootstrap for the usage within of all modules.
The clear disadvantage of this solution are the many queries needed for the multiple entries. Theoretically do you need for every entry one query. Which is not a big problem as long as you do not have a lot of queries at the same time. But if the application grows and your server gets some traffic you will get problems.
Join-Command
The Join-Command is a stronger method, since you need only one query to find the results for all rows. This reduces the load on your server and makes it easier to handle the request, since you have only one variable to deal with. Additionally this methods allows you to stay within the methods of the framework and thus having a consistent solution.
But there are some technical difficulties and problems with this method. You can only use it, if you want to use exactly 2 different tables. More than two are not allowed or at least have not worked for me (Version 1.7.2) and the query returned an error message, that the requested columns could not be found.
SQL-Statements
This method offers you the biggest flexibility, because you have no restraints from the framework and can work as usual known.
But you need some knowhow and may be not consistent with the framework. Additionally the queries can become very large and thus make your system slow as well.
Now, after this short reflexion about the different methods, I want to give a brief example of an actual implementation of the third solution, which I have used today in a project. The simple function of the code below is, to find all relevant information from the starting point of the 'Reviews'. It is used to build a list of the entries.
public function latestReviews($count)
{
$db = Zend_Registry::get('database');
$sql = 'SELECT * FROM reviews, albums, artists, labels WHERE albums.id =
reviews.r_album AND artists.a_id = reviews.r_artist AND labels.l_id = albums.label
ORDER BY reviews.review_created ASC ';
$query = $db->query($sql);
$result = $query->fetchAll($count);
return $result;
}
This code is placed within my model, so I can use it directly when I instantiate the required class and have directly all fields available. The database is registered within a Zend_Registry in the bootstrap file. You will find a solution for this (Zend_Registry and Zend_Db) on the blog of Pádraic Brady.
Leave a comment