Using models as criteria objects for querying the database

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.