Type and boolean columns with Doctrine and Symfony

Type and boolean columns are very common when designing a database, boolean columns are those which can have just two values: true or false. And type columns are those which can have a set of values.

The Problem

Let's see an Doctrine 1.2 schema as example:

 
Category:
  tableName:          t_category
  columns:
    id:               { type: integer, length: 20 , primary: true, autoincrement: true        }
    name:             { type: string , length: 50                , notnull: true              }
    type:             { type: string , length: 1  , fixed: true  , notnull: true , default: N }
    visible:          { type: string , length: 1  , fixed: true  , notnull: true , default: 1 }
    active:           { type: string , length: 1  , fixed: true  , notnull: true , default: 1 }
  options:
    symfony:          { filter: false, form: true }
 

In the previous schema there is a category table with 5 columns:

id: The autoincrement primary key.
name: The name of the category
type: The kind of category, it could be: normal, hierarchical or dependent. (N: Normal, H: Hierarchical, D: Dependent) 
visible: Whether or not the category is visible. (1 is true, 0 false)
active: Whether or not the category is active. (1 is true, 0 false)

As you can see there is one type column (type) and two boolean columns (visible and active). Now there is a need for methods to manage effectively these fields:

 
class Category extends BaseCategory
{
  public function isNormalType()
  {
    return $this->getType() == 'N';
  }
 
  public function isHierarchicalType()
  {
    return $this->getType() == 'H';
  }
 
  public function isDependentType()
  {
    return $this->getType() == 'D';
  }}
 
  public function isVisible()
  {
    return $this->getVisible() == '1';
  }
 
  public function isActive()
  {
    return $this->getActive() == '1';
  }
 

Ok, that was easy, mmm but it seems like a lot of operational code. And now what if you need a method like getTypeName that depending of the type value returns a string as Normal or Hierarchical to show directly to the user. Yeah, I think the same, it could be implemented, but what if you have a lot of tables like this one, with a lot of boolean and type fields. Ok you know the answer, I already passed through this and tried to find  a way to automate this behavior.

The Solution

Sometime ago I wrote a post about "Adding custom information to your Doctrine schema" and now is the time to show you why I needed to add info on a table basis.

Update your schema to:

 
Category:
  ...
  options:
    symfony:          { filter: false, form: true }
    type_columns:     [ type ]
    boolean_columns:  [ visible, active ]
 

As you can see, now the table knows which columns are type columns and which ones are boolean.

Then, add the following code to your DoctrineRecord class, the one in the hierarchy:

Doctrine_Record > sfDoctrineRecord > DoctrineRecord > BaseCategory > Category

 
abstract class DoctrineRecord extends sfDoctrineRecord
{
  public function __call($method, $arguments)
  {
    try
    {
      $verb = substr($method, 0, 3);
      $name = substr($method, 3);
      if (substr($method, 0, 2) == 'is')
      {
        $name = sfInflector::underscore(substr($method, 2));
        if (in_array($name, (array) $this->getTable()->getOption('boolean_columns')))
        {
          return $this->$name == constant(get_class($this->getTable()).'::YES');
        }
        elseif ($column = $this->getTypeColumn($name))
        {
          return $column[1];
        }
 
        return parent::__call($method, $arguments);
      }
      elseif ($verb == 'get')
      {
        if (!strpos(sfInflector::underscore($name), '_'))
        {
          return parent::__call($method, $arguments);
        }
 
        foreach (array_keys($this->getTable()->getSingleRelations()) as $relation)
        {
          $relation_name = substr($name, 0, strlen($relation));
          if ($relation_name == $relation)
          {
            $field = sfInflector::underscore(substr($name, strlen($relation)));
            if ($field && $field != 'id' && $field != 'Id') // warning when doing something like getAssociationDetailId
            {
              return $this->{'has'.$relation}() ? $this->$relation->$field : '';
            }
          }
        }
 
        if (substr($method, -4) == 'Name')
        {
          $name = substr($method, 3, strlen($method) - 7);
          if (!$name) // getName
          {
            return parent::__call($method, $arguments);
          }
 
          $name = sfInflector::underscore($name);
          if (in_array($name, (array) $this->getTable()->getOption('boolean_columns')))
          {
            $assertions = $this->getTable()->getAssertions();
            return $assertions[$this->$name];
          }
          elseif (in_array($name, (array) $this->getTable()->getOption('type_columns')))
          {
            $types = $this->getTable()->{'get'.sfInflector::camelize($name).'s'}();
            return $types[$this->$name];
          }
        }
      }
      elseif ($verb == 'has')
      {
        return $this->relatedExists($name);
      }
      elseif ($verb == 'add')
      {
        $collectionName = $name.'s';
 
        return $this->$collectionName->add($arguments[0]);
      }
 
      return parent::__call($method, $arguments);
    }
    catch (Exception $e)
    {
      return parent::__call($method, $arguments);
    }
  }
}
 

I apologize with you, my dear reader, for have a method with 74 lines of code. Although I have the properly test for this method, I do not find the time to refactor it.

You can see that we overload the already overloaded __call magic method to achieve the desired functionality with the methods starting with is and get.

After that, to properly reused the boolean functionality, add to the DoctrineTable class the following code:

 
class DoctrineTable extends Doctrine_Table
{
  const
    YES = '1',
    NO  = '0';
 
  protected static
    $assertions = array
    (
      self::YES => 'Yes',
      self::NO  => 'No',
    );
 
  public function getAssertions()
  {
    return self::$assertions;
  }
}
 

This code is for the boolean functionality. There are a yes and no values matched with 1 and 0.

Finally, add to the CategoryTable class the following code:

 
class CategoryTable extends DoctrineTable
{
  const
    TYPE_NORMAL       = 'N',
    TYPE_HIERARCHICAL = 'H',
    TYPE_DEPENDENT    = 'D';
 
  protected static
    $types = array
    (
      self::TYPE_NORMAL        => 'Normal',
      self::TYPE_HIERARCHICAL  => 'Hierarchical',
      self::TYPE_DEPENDENT     => 'Dependent',
    );
 
  public function getTypes()
  {
    return self::$types;
  }
 

As you can see the three types of categories are created like constants and then there is a static array with the constant values as keys and with representative strings as values. And at the bottom fo the class a method that returns this protected static array.

The Reward

Wow, it is a lot of code so far, but don't worry all of this has the purpose to make your life easier when treating with type and boolean columns. Now you can use the following methods without adding a single line of code to your model:

 
  public function executeIndex(sfWebRequest $request)
  {
    /*
    My Category: id  :    1
                 name:    My Category
                 type:    N
                 visible: 1
                 active:  0
    */
 
    $category = Doctrine::getTable('Category')->findOneByName('My Category');
    $category->isNormal();       // will return true
    $category->isHierarchical(); // will return false
    $category->isDependent();    // will return false
    $category->getTypeName();    // will return "Normal"
    $category->isVisible();      // will return true
    $category->isActive();       // will return false
    $category->getVisibleName(); // will return "Yes"
    $category->getActiveName();  // will return "No"
  }
 

In order to add a new type column, its corresponding table info is needed to allow this functionality. As for the boolean columns there is no special info, there is nothing to add.

Now, there is no need to clutter our precious model with repetitive and operational methods. Yes!, more space for business logic methods.

This and other functionalities from Doctrine and Symfony I described to you in this blog are part of the Symfext Plugin. A symfony plugin I created in all this years of delivering stable and rock solid systems with the help of Doctrine 1.X and Symfony 1.X. In a future post I'll present you formally this plugin and publish it in the symfony plugin system. I should have done that so many time ago because now Symfony 2.0 is so near, and this plugin is based on symfony 1.4, however the time is short and is better late than ever. Somehow I try to show you all of these functionalities in these posts and I hope they are very helpful to you.