Optimizing Zend_Db_Table SQL Query Count for Multiple Reference Tables


Recently I have started to use Zend Framework for a new project in the company. I have designed my database tables and then created some models for my MVC with Zend_Db_Table. When I wanted to show a detail page for a row in a table which had 4 foreign keys, I needed to make some joins in the sql which I was unable to do and used findParentRow() method which was a huge miskate. That’s where the story begins.

So an example is the best when you want to explain something. Let’s create a simple ecommerce database. We have products, categories, depots and etc. these three is just enough for now. Every product must have a category and every product must be in a depot. Every product has also a name, a description and a quantity (yes I know there are some other things too but let’s keep it short and simple). A simple image on that.

simple_db

I know this is very simple, but I suppose you get the picture. I have two foreign keys in the products table. So when we create the models using Zend_Db_Table for tables, we obtain something like this:

  1. class Category extends Zend_Db_Table
  2. {
  3. protected $_name = “categories”;
  4. protected $_primary = “id”;
  5. }
  6. class Depot extends Zend_Db_Table
  7. {
  8. protected $_name = “depots”;
  9. protected $_primary = “id”;
  10. }
  11. class Products extends Zend_Db_Table
  12. {
  13. protected $_name = “products”;
  14. protected $_primary = “id”;
  15. }
class Category extends Zend_Db_Table
{
    protected $_name = "categories";
    protected $_primary = "id";
}

class Depot extends Zend_Db_Table
{
    protected $_name = "depots";
    protected $_primary = "id";
}

class Products extends Zend_Db_Table
{
    protected $_name = "products";
    protected $_primary = "id";
}

Of course you have to define these classes to the appropriate files. So let’s get to the point where I want to show the product detail page. In my controller I have a detail action, which will fetch the data using the model and assign it to a view attribute and then show it. The important part is to fetch the data:

  1. //Some code here about the controller….
  2. public function detailAction()
  3. {
  4. //get the id from somewhere, don’t forget to escape it…
  5. $prdDao = new Products();
  6. $singlePrd = $prdDao->find($id); //Find will return a rowset so I should use the first element…
  7. $this->view->prd = $singlePrd[0];
  8. }
//Some code here about the controller....
    public function detailAction()
    {
        //get the id from somewhere, don't forget to escape it...
        $prdDao = new Products();
        $singlePrd = $prdDao->find($id); //Find will return a rowset so I should use the first element...
        $this->view->prd = $singlePrd[0];
    }

And in my view code I will do something like this:

  1. Category: prd->category_id?>
  2. Depot: prd->depot_id?>
  3. Quantity: prd->quantity?>
  4. Title: prd->title?>
  5. Description: prd->desc?>
Category: prd->category_id?>
Depot: prd->depot_id?>
Quantity: prd->quantity?>
Title: prd->title?>
Description: prd->desc?>

Simple, Eh? Yet when I execute this part of the code, I encounter an output containing integers instead of category names, when I enable sql query profiling and look for the executed sql queries, I see that data is only fetched from products. As I had not said otherwise, this is the expected outcome anyway. So what should I do to get the data from other contents?

A way might be to use the find method with the other model classes and get the data like that way. So my controller must evolve to something like this:

  1. public function detailAction()
  2. {
  3. //get the id from somewhere, don’t forget to escape it…
  4. $prdDao = new Products();
  5. $singlePrd = $prdDao->find($id);
  6. $catDao = new Category();
  7. $prdCatName = $catDao->find($singlePrd[0]->category_id);
  8. $depDao = new Depot();
  9. $prdDepName = $depDao->find($singlePrd[0]->depot_id);
  10. $this->view->prd = $singlePrd[0];
  11. $this->view->cat = $prdCatName;
  12. $this->view->dep = $prdDepName;
  13. }
    public function detailAction()
    {
        //get the id from somewhere, don't forget to escape it...
        $prdDao = new Products();
        $singlePrd = $prdDao->find($id);
        $catDao = new Category();
        $prdCatName = $catDao->find($singlePrd[0]->category_id);
        $depDao = new Depot();
        $prdDepName = $depDao->find($singlePrd[0]->depot_id);
        $this->view->prd = $singlePrd[0];
        $this->view->cat = $prdCatName;
        $this->view->dep = $prdDepName;
    }

and my view should change accordingly:

  1. Category: cat->name?>
  2. Depot: dep->name?>
  3. Quantity: prd->quantity?>
  4. Title: prd->title?>
  5. Description: prd->desc?>
Category: cat->name?>
Depot: dep->name?>
Quantity: prd->quantity?>
Title: prd->title?>
Description: prd->desc?>

So that’s ok but this causes a problem. Every time I want to get the detailed product I should write these lines, so let’s make a method in the model class which will return an array containing my three different values:

  1. public function getDetail($id)
  2. {
  3. $singlePrd = $this->find($id);
  4. $catDao = new Category();
  5. $prdCatName = $catDao->find($singlePrd[0]->category_id);
  6. $depDao = new Depot();
  7. $prdDepName = $depDao->find($singlePrd[0]->depot_id);
  8. return array($singlePrd[0], $prdCatName, $prdDepName);
  9. }
    public function getDetail($id)
    {
        $singlePrd = $this->find($id);
        $catDao = new Category();
        $prdCatName = $catDao->find($singlePrd[0]->category_id);
        $depDao = new Depot();
        $prdDepName = $depDao->find($singlePrd[0]->depot_id);
        return array($singlePrd[0], $prdCatName, $prdDepName);
    }

My controller should evolve too but I’m passing that part. Let’s get to the point now. Isn’t there a way to define the relation between the tables. Answer is yes! Add this attribute to your Products model class:

  1. protected $_referenceMap = array (
  2. ‘cats’ => array(
  3. ‘columns’           => ‘category_id’,
  4. ‘refTableClass’     => ‘Category’,
  5. ‘refColumns’        => ‘id’,
  6. ),
  7. ‘depots’ => array(
  8. ‘columns’           => ‘depot_id’,
  9. ‘refTableClass’     => ‘Depot’,
  10. ‘refColumns’        => ‘id’,
  11. ));
    protected $_referenceMap = array (
    'cats' => array(
        'columns'           => 'category_id',
        'refTableClass'     => 'Category',
        'refColumns'        => 'id',
    ),
    'depots' => array(
        'columns'           => 'depot_id',
        'refTableClass'     => 'Depot',
        'refColumns'        => 'id',
    ));

This is the relationship definition for Zend Framework this will allow us to use a method named findParentRow() in the model like this:

  1. public function getDetail($id)
  2. {
  3. $singlePrd = $this->find($id);
  4. $prdCatName = $singlePrd[0]->findParentRow(“cats”);
  5. $prdDepName = $singlePrd[0]->findParentRow(“depots”);
  6. return array($singlePrd[0], $prdCatName, $prdDepName);
  7. }
    public function getDetail($id)
    {
        $singlePrd = $this->find($id);
        $prdCatName = $singlePrd[0]->findParentRow("cats");
        $prdDepName = $singlePrd[0]->findParentRow("depots");
        return array($singlePrd[0], $prdCatName, $prdDepName);
    }

These lines will make what I was doing previously. When I will check the sql queries list I will notice the same queries without optimization. Well honestly that’s cool but the problem occurs when you have thousands of users and your database may not hold this much of needless queries and you will require an optimization anyway. So what can we do? We should convert these queries to a single one but how?

Let’s implement a magical join creator method which will create a joined query automaticaly using the referenceMap attribute:

  1. //Let’s change my attribute’s values a little and add a table name and columns to fetch data
  2. //keys starting with ref indicates the reference table values…
  3. protected $_referenceMap = array (
  4. ‘cats’ => array(
  5. ‘columns’           => ‘category_id’,
  6. ‘refTableClass’     => ‘Category’,
  7. ‘refTableName’      => “categories”,
  8. ‘refColumns’        => ‘id’,
  9. ‘refColumnsToFetch’ => array(“name”),
  10. ),
  11. ‘depots’ => array(
  12. ‘columns’           => ‘depot_id’,
  13. ‘refTableClass’     => ‘Depot’,
  14. ‘refTableName’      => “depots”,
  15. ‘refColumns’        => ‘id’,
  16. ‘refColumnsToFetch’ => array(“name”),
  17. ));
  18. public function getDetail($id)
  19. {
  20. //I will create a custom select query instead of using find method directly…
  21. $sel = $this->select()
  22. ->from(array(“current” => $this->_name));
  23. $sel->where(“current.{$this->_primary} = ?”, $id);
  24. $this->_createJoinedSelect($sel); //This function will do the magical join
  25. return $this->fetchRow($sel); //Fetch one row (which must be unique anyway) and return Zend_Db_Table_Row
  26. }
  27. private function _createJoinedSelect(Zend_Db_Table_Select &$sel) //Notice the & sign, indicating reference…
  28. {
  29. $sel->setIntegrityCheck(false); //I should set this to false, if I want to make joins (or to add values other than the current table’s column names)
  30. foreach ($this->_referenceMap as $key => $rf) { //Loop for all the entries in the referenceMap
  31. $colsToFetch = array();
  32. foreach($rf[“refColumnsToFetch”] as $cKey => $cVal) {
  33. if (!is_numeric($cKey)) {
  34. $colsToFetch[$cKey] = $cVal; //You might set a key
  35. } else {
  36. $colsToFetch[$key . “_” . $cVal] = $cVal; //To use the data in the view, you will use something like $this->prd->cats_name
  37. }
  38. }
  39. $sel->join(array($key => $rf[“refTableName”]),
  40. “{$key}.{$rf[“refColumns”]} = current.{$rf[“columns”]}”,
  41. $colsToFetch);
  42. }
  43. }
    //Let's change my attribute's values a little and add a table name and columns to fetch data
    //keys starting with ref indicates the reference table values...
    protected $_referenceMap = array (
    'cats' => array(
        'columns'           => 'category_id',
        'refTableClass'     => 'Category',
        'refTableName'      => "categories",
        'refColumns'        => 'id',
        'refColumnsToFetch' => array("name"),
    ),
    'depots' => array(
        'columns'           => 'depot_id',
        'refTableClass'     => 'Depot',
        'refTableName'      => "depots",
        'refColumns'        => 'id',
        'refColumnsToFetch' => array("name"),
    ));

    public function getDetail($id)
    {
        //I will create a custom select query instead of using find method directly...
        $sel = $this->select()
                    ->from(array("current" => $this->_name));
        $sel->where("current.{$this->_primary} = ?", $id);
        $this->_createJoinedSelect($sel); //This function will do the magical join
        return $this->fetchRow($sel); //Fetch one row (which must be unique anyway) and return Zend_Db_Table_Row
    }

    private function _createJoinedSelect(Zend_Db_Table_Select &$sel) //Notice the & sign, indicating reference...
    {
        $sel->setIntegrityCheck(false); //I should set this to false, if I want to make joins (or to add values other than the current table's column names)
        foreach ($this->_referenceMap as $key => $rf) { //Loop for all the entries in the referenceMap
            $colsToFetch = array();
            foreach($rf["refColumnsToFetch"] as $cKey => $cVal) {
                if (!is_numeric($cKey)) {
                    $colsToFetch[$cKey] = $cVal; //You might set a key
                } else {
                    $colsToFetch[$key . "_" . $cVal] = $cVal; //To use the data in the view, you will use something like $this->prd->cats_name
                }
            }
            $sel->join(array($key => $rf["refTableName"]),
                             "{$key}.{$rf["refColumns"]} = current.{$rf["columns"]}",
                             $colsToFetch);
        }
    }

Code explains itself I suppose and everthing I have done was pretty simple. Now you should see, that what I had done was to write my own select query instead of using the default find() method. When I check the sql queries profiler, I will notice that there is only, 2 queries instead of 6. Imagine when you have to list the products, you have to make 6 queries for each row (actually (rowcount-1) * 3 + 6 queries if you use singleton on your models to prevent querying describe for each instance of your model.)

You could also want to remove the first describe query too which is not that easy. A simple singleton implementation will help you increase your performance (query will not be called for each instance of the class because there is only one instance) but will still not remove the first execution of describe query. To disable this one too, you should set a meta data cache, to the class. This cache is an instance of Zend_Cache_Core. You can use memcache or something like this for caching. You can find a good article about Zend_Cache here.

This was my approach to this problem, I don’t really know if there is a way in Zend Framework itself without doing things that I have done, let me know because I was not able to find it.

Advertisements

One comment on “Optimizing Zend_Db_Table SQL Query Count for Multiple Reference Tables

  1. Pingback: Reference tables | TrueConvictionDesigns

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s