Refactoring queries with Doctrine

Sometimes when programming, you need to do a series of queries to gather data to present to the user. A lot of times these queries are very similar, but because of time, the correct refactoring is avoided, falling as a promise in the future that will never come.

In this post will be presented a simple example of refactoring queries that were already done but in the usual fast way.

First let's present the schema that the case will be based upon.

Schema

 
Employee:
  tableName:       t_employee
  columns:
    id:            { type: integer  , length: 20 , primary: true, autoincrement: true        }
    code:          { type: string   , length: 20                , notnull: true              }
    first_name:    { type: string   , length: 100               , notnull: true              }
    last_name:     { type: string   , length: 100               , notnull: true              }
    salary:        { type: decimal  , length: 10 , scale: 2     , notnull: true              }
    date_of_birth: { type: date                                                              }
    status:        { type: string   , length: 1  , fixed: true  , notnull: true , default: O }
  indexes:
    u_code:        { fields: [ code ]                           , type: unique               }
    i_first_name:  { fields: [ first_name]                                                   }
    i_last_name:   { fields: [ last_name ]                                                   }
    i_status:      { fields: [ status ]                                                      }
 

The schema resembles an Employee table which have some simple fields to hold the employee data. 

Queries

1.- Let's imagine there is an autocompleter widget and it needs to filter the active employees by its first_name and last_name. Then you could built a method like:

 
class EmployeeTable extends Doctrine_Table
{
  public function findActiveByNameLike($name)
  {
    $name = '%'.$name.'%';
    $q = $this->createQuery('e');
    $q->where('LOWER(e.first_name) LIKE ? OR LOWER(e.last_name) LIKE ?', array($name, $name))
    $q->andWhere('e.status = ?', '1'); // 1 means active =)
 
    return $q->execute();
  }
}
 

2.- Now, you need to retrieve all the active employees whose salary is less than a variable quantity:

 
  public function findActiveBySalaryLessThan($salary)
  {
    $q = $this->createQuery('e');
    $q->where('e.salary < ?', $salary)
    $q->andWhere('e.status = ?', '1'); // 1 means active =)
 
    return $q->execute();
  }
 

3.- And finally you need to get all the active employees whose birthday is today.

 
  public function findActiveByDateOfBirth($date)
  {
    $q = $this->createQuery('e');
    $q->where('e.date_of_birth = ?', $date)
    $q->andWhere('e.status = ?', '1'); // 1 means active =)
 
    return $q->execute();
  }
 

So far so good, our queries pretty much do what we need. But ... what if a nice stakeholder comes and says to you: "I want another autocompleter that automatically filters employees with salary less than 1000 ... and send an email to congratulate the employees whose birthday is the current day and whose salary is less than 500,000.00. Mmmm and all the previous queries but with inactive employees". Before thinking about murdering your nice stakeholder, you can try to refactor the queries above.

Refactoring Queries

 Now, let's see how could be refactored our previous queries:

 
class EmployeeTable extends Doctrine_Table
{
  public function updateQueryForNameLike(Doctrine_Query $q, $name)
  {
    $name = '%'.$name.'%';
    $q->andWhere('LOWER(e.first_name) LIKE ? OR LOWER(e.last_name) LIKE ?', array($name, $name))
  }
 
  public function updateQueryForSalaryLessThan(Doctrine_Query $q, $salary)
  {
    $q->andWhere('e.salary < ?', $salary);
  }
 
  public function updateQueryForDateOfBirth(Doctrine_Query $q, $date)
  {
    $q->andWhere('e.date_of_birth = ?', $date);
  }
 
  public function updateQueryForStatus(Doctrine_Query $q, $status)
  {
    $q->andWhere('e.status < ?', $status);
  }
 
  public function findActiveByNameLike($name)
  {
    $q = $this->createQuery('e');
    $this->updateQueryForNameLike($q, $name);
    $this->updateQueryForStatus($q, '1'); // this 1 should be a constant like ACTIVE
 
    return $q->execute();
  }
 
  public function findActiveBySalaryLessThan($salary)
  {
    $q = $this->createQuery('e');
    $this->updateQueryForSalaryLessThan($q, $salary);
    $this->updateQueryForStatus($q, '1'); // this 1 should be a constant like ACTIVE
 
    return $q->execute();
  }
 
  public function findActiveByDateOfBirth($date)
  {
    $q = $this->createQuery('e');
    $this->updateQueryForDateOfBirth($q, $date);
    $this->updateQueryForStatus($q, '1'); // this 1 should be a constant like ACTIVE
 
    return $q->execute();
  }
}
 

You can see how every query was rebuilt based on the updateQueryFor methods. This way you can easily build your new queries for the nice stakeholder without hassle, because the core functionality is already done. Remember these queries are simple just to show the purpose of the refactoring idea. In a real life project you must separate complex queries to be able to reuse them. For example if we make the employee table acts as NestedSet behavior then you can manage Supervisors and Subordinates easily. Thus, it would be necessary to create an updateQueryToFindSupervisorsByEmployeeId and an updateQueryToFindSubordinatesbyEmployeeId  methods. 

With this kind of refactoring you can tackle and reuse the most complex queries you may need in your daily programming. Just remember to refactor continuously to have an efficient an reusable code base.