Refactoring queries with Doctrine


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

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.

 

 


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