Using models as criteria objects for querying the database

January 24, 2009 – 12:03 am Tags:

If you have written a model layer which separates database access from the rest of the code, you probably know that you may end up with lots of methods like findByName, findByTitle or such, for cases where you want to find records based on specific criterias.

You could also use arrays, for example findBy(array(‘title’=>’something’)). However, these are easy to type wrong, and you don’t get any validation that the value you’re passing can even exist to begin with.

You could also use your model objects as search “criterias” – A bit similar to how Propel handles criterias, but not as nearly as complex.

The concept

The basic idea is that you can use your model classes to define what results you want, for example consider this code:

$user = new User();
$user->setName('Peter');
 
$gw = new UserGateway();
 
$peters = $gw->findBy($user);

The above snippet would find all users whose name is Peter. It may seem that it’s simpler to just write a method like findByName, but using a model has another benefit too: The model can validate the name before it’s getting passed to the find method. You already wrote the validation logic inside your model, so why not make use of it?

In addition, you can define more than one values in the model. In addition to setting the name, you could set the age or some other property of the user model. This way, you can search by multiple criterias without having to define more methods like findByNameAndAge($name, $age).

Next, let’s look at how this could be implemented in practice

Implementing criteria based search

Let’s assume our user object can have an id, a name and a password. The UserGateway object is the class which handles all the database related logic.

class UserGateway {
  //Let's assume this is a PDO instance that gets populated by some other code
  private $_pdo;
 
  public function findBy(User $criteria) {
    $sql = 'SELECT id, name, password FROM users WHERE ';
 
    //Parse the criteria into sql
    $sql .= $this->_parseCriteria($criteria);
 
    return $this->_pdo->query($sql)->fetchAll();
  }
 
  private function _parseCriteria(User $criteria) {
    $id = $criteria->getId();
    $name = $criteria->getName();
    $password = $criteria->getPassword();
 
    //Only use values which are set as criterias
    $sqlParts = array();
    if($id != '')
      $sqlParts[] = 'id = ' . $this->_pdo->quote($id);
 
    if($name != '')
      $sqlParts[] = 'name = ' . $this->_pdo->quote($name);
 
    if($password != '')
      $sqlParts[] = 'password = ' . $this->_pdo->quote($password);
 
    //Implode by AND and we have a nice SQL snippet for ther WHERE clause
    return implode(' AND ', $sqlParts);
  }
}

So in the end, it’s relatively simple to do criterias like that. The above code does have certain parts that could be optimized a little. For example, the method which creates the criteria could instead have an array of columns in the table, and use that instead of multiple if’s. However, for sake of simplicity I left it out.

Also, instead of returning the array in findBy, you might want to actually convert the array into User objects. This is again quite simple by looping through the result and calling relevant setter methods.

Summary

Criteria objects are an easy way to define search parameters for SQL. They allow your model’s validation code to be re-used for validating search parameters, and you will not have to write so many methods in your classes. There is a small drawback, though: as you may have noticed, the code for parsing the criteria only does equals-comparisons, so you may have to find another way if you need, for example, greater than or LIKE comparisons.

It might be possible to improve this concept a bit more by using reflection – that could be useful for automating the generation of the SQL clauses.

Share this:
  1. 6 Responses to “Using models as criteria objects for querying the database”

  2. I think the above two methods violates the concept of Active Record as you are exposing native SQL in your model.
    I have seen the use of criteria in ORM like NHibernate [C#] and Hibernate [Java] with the support of generics. But since PHP is a dynamic language, it does not have generics feature. Using criteria based search violating the active record pattern as mentioned above seems a bit weird to me.

    By Prajwal Tuladhar on Jan 27, 2009

  3. It’s not ActiveRecord, and the SQL is just in the gateway class, not in the model.

    The model is just a “dumb” class – unaware of the database. The gateway-class is kind of like a data access object or a data mapper, which handles accessing the database. It handles all the SQL-related logic, and the model simply contains the data, and can contain things like validation logic etc.

    Oh, and I have no idea what generics have to do with this =)

    By Jani Hartikainen on Jan 27, 2009

  4. Totally agree with Prajwal,

    Also considere the next scenario :

    “since the object $user has not been setted with any property , the sql just fail right ?”

    $user = new User();
    $gw = new UserGateway();
    $peters = $gw->findBy($user);
    –> ‘SELECT id, name, password FROM users WHERE ‘

    By Instance on Jan 29, 2009

  5. Well spotted, Instance – Thanks for pointing it out. That is a problem in my example, and anyone who wants to implement this should take measures to confirm the SQL query will execute correctly, and handle erroneous conditions properly!

    By Jani Hartikainen on Jan 29, 2009

  1. 2 Trackback(s)

  2. Jan 30, 2009: The problems faced by a common model interface in frameworks | CodeUtopia
  3. May 7, 2009: Validating Zend_Forms using model objects | CodeUtopia

Post a Comment

You can use some HTML (a, em, strong, etc.). If you want to post code, use <pre lang="PHP">code here</pre> (you can replace PHP with the language you are posting)