Type and boolean columns with Doctrine and Symfony


Symfony | Technical | Development | Doctrine | January 28, 2011

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.

 

 


Comments




Hey Stranger, leave a comment:

Last Posts

Autoloading: Symfony vs Yii

Symfony2, PhpBB4 and Drupal8

Type and boolean columns with Doctrine and Symfony

Refactoring queries with Doctrine

Extending your Doctrine Model: Template Classes

Integrating Doctrine: Symfony vs Yii

Passing parameters from the action to the view: Symfony vs Yii

Yii framework

Adding custom information to your Doctrine schema

PHP Advent Calendar 2010


My Tweets

10 days ago
I'm sooo not looking forward to getting up for work tomorrowwww - cant wait for this thing to work! https://tinyurl.com/67rg4lo
12 days ago
finally this class #semester is finished, new #projects in mind
20 days ago
Mark Zuckerberg Explains His Law Of Social Sharing [Video]https://t.co/sqq10Ehh via@TechCrunchh
about 1 month ago
retomando mi #twitter #notime
about 1 month ago
Inspiring for ANYONE who hates their job and their STUPIDD GD BOSS https://tinyurl.com/3cz3pan
about 1 month ago
How Facebook Can Put Google Out of Businesshttps://t.co/HqDfQGoo via@TechCrunchh
2 months ago
So why not just cut out the middle man? Microsoft.https://techcrunch.com/2011/05/15/samsung-series-5-chromebook/
2 months ago
really like to #design class hierarchies with #compositepattern
3 months ago
Similarly, Microsoft.com started to use jQuery instead of their own ASP.NET Ajax framework. They are still using Windows, for whatever XD
3 months ago
Estudiantes de la PUCP le “voltean” campaña a esposo de Keiko | yoperiodhttps://t.co/ruI9UnmI9Unm@lamulaamula
3 months ago
Reading: Apress.-.Pro.html"https://www.twitter.com/search?q=#php">#php #performance #read
3 months ago
debug_backtrace() is very important on certain situations. #php #debug
3 months ago
@alvarezrodrich felicitaciones!
4 months ago
time to do some #uml diagrams, #classdiagram
4 months ago
hoy es el día,#votaa conciente#peruu
4 months ago
@skoop @funkatron I think someone had a bad day!, #Frameworks are there but you don't have to use them.
4 months ago
making #wireframes for a new #functionality
4 months ago
so much #spam on my #blog =(
4 months ago
learning new topics and tools that I did not use before #rcp
4 months ago
aprendiendo muchos temas y herramientas que no utilizaba antes #rcp
4 months ago
#tweaking httpd.conf #virtualhost
4 months ago
thanks #symfony 1.4, even when i'm not using the entire #framework, yours classes save my life!
4 months ago
integrating with #SOA using #soap
4 months ago
It seems my most #productive working hours are on #sunday #afternoon #evening! XD
4 months ago
#ASOT 500 =)
4 months ago
installing SCA_SDO on #Centos #php
4 months ago
My web service using #soap worked!!!!! #php #SCA #SDO
4 months ago
Working in a new place since last week!, #RCP: Red Científica Peruana, the one which sells the .pe domains in#Perúú ->#happypy
5 months ago
Finally with a new #laptop: #Toshiba =)
5 months ago
@pasku1 Thanks, I will try Pivotaltracker.
5 months ago
@doolphy thanks for your answer doolphy! I'll try you!
5 months ago
@jmasson thanks for your answer! Jira + Confluence is a good combination.
5 months ago
Which is the best project management and collaboration tool right now? #projectmanagement #tool #collaboration
5 months ago
What a #voice! Sied Van Riel feat Nicole McKenna - Stealing Time (Aly & Fila Remix) + #ASOT 497 #trancefamily
5 months ago
working on a situation where #php #traits would be very useful
5 months ago
It is #awesome when you finish doing a lot of changes and nothing is broken =) #TDD #testing
5 months ago
oh, happy birthday! @mtabini o mejor dicho feliz cumpleaños!
5 months ago
why do #IE8 not accept #javascript "const" keyword?
5 months ago
Discovering there is much #more to do with #javascript ... a lot.
5 months ago
Awesome #song!: Cerf, Mitiska & Jaren - Another World (Original Vocal Mix) #ASOT 495 #trancefamily
6 months ago
My legal woman is #PHP, but I have an affair with #Javascript, overall when she wears #jQuery.
6 months ago
This presentation is one of the best I've seen about #unit #testing https://www.slideshare.net/avalanche123/clean-code-5609451
6 months ago
where to do a master on #IT: US or Spain? #survey #php #master plz RT
6 months ago
Bobina feat. Betsie Larkin - You Belong To Me: What a #beautiful voice -> #ASOT 494 #arminvanbuuren
6 months ago
OH NO, IT'S MONDAY -- 2011-02-07 https://t.co/pgaIxe5 via @gojkoadzic
6 months ago
it was not a + b, it was parserInt(a) + parseInt(b) =(, #javascript #fail
6 months ago
The models are complete representations of the system, whereas an #architectural #view focuses only on what is architecturally #significant.
6 months ago
@jmasson that would be great and finally #wikimedia, #drupal, #wordpress and maybe #joomla would push towards the same side, the #php side.
6 months ago
@jmasson Thanks!, #PHP has a bright #future ahead.
6 months ago
A new #blog post about not reinventing the #wheel: /article/symfony2_phpbb4_and_drupal8 #symfony #phpbb #drupal
6 months ago
@giorgiosironi #indeed, that's a very good #question. I think an average of 4 but also depends on how much that #developer work.
6 months ago
It seems the new platform for deploying, managing and scaling PHP apps is https://orchestra.io/ #cool
6 months ago
#Phase project planning vs #iteration project #planning - #project #management
6 months ago
#jeditable with #jquery save my life =)
6 months ago
Amazing #song -> Sied van Riel feat. Nicole McKenna - Stealing Time #ASOT 493 #trancefamily
6 months ago
I simply love "offset" #jquery function =)
6 months ago
I really don't understand why projects like #drupal does not base their components in projects like #doctrine and #symfony
6 months ago
OH NO, IT'S MONDAY -- 2011-01-17 https://t.co/37pr4Bd via @gojkoadzic
6 months ago
@alvarezrodrich me alegra ver q ya borró su cuenta Sr. Rodrich,#twitterr es malo jajja, XD
6 months ago
Acabo de hacerle a mi #brother @diegonl89 un blog para que hable de #actualidad en general: https://www.elgatotechero.com #peru
6 months ago
I just made to my #brother a #blog to talk about current #events in #peru: https://www.elgatotechero.com
6 months ago
Amazing things can be done with #javascript and #css, and of course with the help of #jQuery =)
6 months ago
An architecturally significant element is an element that is important for #understanding the #system.
6 months ago
An architecturally element has a wide impact on the #structure, #performance, #robustness, #evolvability, and #scalability of a #system.
6 months ago
@giorgiosironi Definitely!
6 months ago
Playing with #table #inheritance in #Doctrine
6 months ago
Reading about #RUP, and how addresses the #major difficulties in a new #project.
6 months ago
A new blog #post about #refactoring your queries with #Doctrine: /article/refactoring_queries_with_doctrine #symfony
6 months ago
Yandex in 2010: 43 percent revenue growth https://t.co/cpjT5Jw via @cnet
6 months ago
Going forward!!!!! =) poco a poco llegan los resultados de tanto esfuerzo #fb
6 months ago
Perfect #system with respect to the #requirements but the #wrong system with respect to the #real #problem at the time of #delivery.
6 months ago
Going #forward! =) #fb
6 months ago
A new #blog #post about integrating #Doctrine with #Yii and #Symfony: /article/integrating_doctrine_symfony_vs_yii
6 months ago
@sam_dark Ok thanks!, but I don't understand why in #Yii documentantion use $_GET and $_POST instead of CHttpRequest https://bit.ly/i5emoL