Tying entity classes to RDBMS queries

Most commercially viable RDBMS systems evolved at a time when procedural programming was at the fore. Imagine the RDBMS world as two dimensional, square, and procedurally oriented. In contrast, entities could be thought of as round, three dimensional, and object oriented. This gives you a picture of what we want to accomplish by tying the results of an RDBMS query into an iteration of entity instances.

Note

The relational model, upon which modern RDBMS systems are based, was first described by the mathematician Edgar F. Codd in 1969. The first commercially viable systems evolved in the mid-to-late 1970s. So, in other words, RDBMS technology is over 40 years old!

How to do it...

First of all, we need to design a class which will house our query logic. If you are following the Domain Model, this class might be called a repository. Alternatively, to keep things simple and generic, we could simply call the new class Application\Database\CustomerService. The class will accept an Application\Database\Connection instance as an argument:
```
namespace Application\Database;

use Application\Entity\Customer;

class CustomerService
{
    
    protected $connection;
    
    public function __construct(Connection $connection)
    {
      $this->connection = $connection;
    }

}
```
Now we will define a fetchById() method, which takes a customer ID as an argument, and returns a single Application\Entity\Customer instance or boolean FALSE on failure. At first glance, it would seem a no-brainer to simply use PDOStatement::fetchObject() and specify the entity class as an argument:
```
public function fetchById($id)
{
  $stmt = $this->connection->pdo
               ->prepare(Finder::select('customer')
               ->where('id = :id')::getSql());
  $stmt->execute(['id' => (int) $id]);
  return $stmt->fetchObject('Application\Entity\Customer');
}
```
Note
The danger here, however, is that fetchObject() actually populates the properties (even if they are protected) before the constructor is called! Accordingly, there is a danger that the constructor could accidentally overwrite values. If you don't define a constructor, or if you can live with this danger, we're done. Otherwise, it starts to get tougher to properly implement the tie between RDBMS query and OOP results.

Another approach for the fetchById() method is to create the object instance first, thereby running its constructor, and setting the fetch mode to PDO::FETCH_INTO, as shown in the following example:

public function fetchById($id)
{
  $stmt = $this->connection->pdo
               ->prepare(Finder::select('customer')
               ->where('id = :id')::getSql());
  $stmt->execute(['id' => (int) $id]);
  $stmt->setFetchMode(PDO::FETCH_INTO, new Customer());
  return $stmt->fetch();
}

Here again, however, we encounter a problem: fetch(), unlike fetchObject(), is not able to overwrite protected properties; the following error message is generated if it tries. This means we will either have to define all properties as public, or consider another approach.
The last approach we will consider will be to fetch the results in the form of an array, and manually hydrate the entity. Even though this approach is slightly more costly in terms of performance, it allows any potential entity constructor to run properly, and keeps properties safely defined as private or protected:
```
public function fetchById($id)
{
  $stmt = $this->connection->pdo
               ->prepare(Finder::select('customer')
               ->where('id = :id')::getSql());
  $stmt->execute(['id' => (int) $id]);
  return Customer::arrayToEntity(
    $stmt->fetch(PDO::FETCH_ASSOC));
}
```

To process a query that produces multiple results, all we need to do is to produce an iteration of populated entity objects. In this example, we implement a fetchByLevel() method that returns all customers for a given level, in the form of Application\Entity\Customer instances:

public function fetchByLevel($level)
{
  $stmt = $this->connection->pdo->prepare(
            Finder::select('customer')
            ->where('level = :level')::getSql());
  $stmt->execute(['level' => $level]);
  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    yield Customer::arrayToEntity($row, new Customer());
  }
}

The next method we wish to implement is save(). Before we can proceed, however, some thought must be given to what value will be returned if an INSERT takes place.
Normally, we would return the newly completed entity class after an INSERT. There is a convenient PDO::lastInsertId() method which, at first glance, would seem to do the trick. Further reading of the documentation reveals, however, that not all database extensions support this feature, and the ones that do are not consistent in their implementation. Accordingly, it would be a good idea to have a unique column other than $id that can be used to uniquely identify the new customer.

In this example we have chosen the email column, and thus need to implement a fetchByEmail() service method:

public function fetchByEmail($email)
{
  $stmt = $this->connection->pdo->prepare(
    Finder::select('customer')
    ->where('email = :email')::getSql());
  $stmt->execute(['email' => $email]);
  return Customer::arrayToEntity(
    $stmt->fetch(PDO::FETCH_ASSOC), new Customer());
}

Now we are ready to define the save() method. Rather than distinguish between INSERT and UPDATE, we will architect this method to update if the ID already exists, and otherwise do an insert.
First, we define a basic save() method, which accepts a Customer entity as an argument, and uses fetchById() to determine if this entry already exists. If it exists, we call an doUpdate() update method; otherwise, we call a doInsert() insert method:
```
public function save(Customer $cust)
{
  // check to see if customer ID > 0 and exists
  if ($cust->getId() && $this->fetchById($cust->getId())) {
    return $this->doUpdate($cust);
  } else {
    return $this->doInsert($cust);
  }
}
```

Next, we define doUpdate(), which pulls Customer entity object properties into an array, builds an initial SQL statement, and calls a flush() method, which pushes data to the database. We do not want the ID field updated, as it's the primary key. Also we need to specify which row to update, which means appending a WHERE clause:

protected function doUpdate($cust)
{
  // get properties in the form of an array
  $values = $cust->entityToArray();
  // build the SQL statement
  $update = 'UPDATE ' . $cust::TABLE_NAME;
  $where = ' WHERE id = ' . $cust->getId();
  // unset ID as we want do not want this to be updated
  unset($values['id']);
  return $this->flush($update, $values, $where);
}

The doInsert() method is similar, except that the initial SQL needs to start with INSERT INTO ... and the id array element needs to be unset. The reason for the latter is that we want this property to be auto-generated by the database. If this is successful, we use our newly defined fetchByEmail() method to look up the new customer and return a completed instance:
```
protected function doInsert($cust)
{
  $values = $cust->entityToArray();
  $email  = $cust->getEmail();
  unset($values['id']);
  $insert = 'INSERT INTO ' . $cust::TABLE_NAME . ' ';
  if ($this->flush($insert, $values)) {
    return $this->fetchByEmail($email);
  } else {
    return FALSE;
  }
}
```

Finally, we are in a position to define flush(), which does the actual preparation and execution:

protected function flush($sql, $values, $where = '')
{
  $sql .=  ' SET ';
  foreach ($values as $column => $value) {
    $sql .= $column . ' = :' . $column . ',';
  }
  // get rid of trailing ','
  $sql     = substr($sql, 0, -1) . $where;
  $success = FALSE;
  try {
    $stmt = $this->connection->pdo->prepare($sql);
    $stmt->execute($values);
    $success = TRUE;
  } catch (PDOException $e) {
    error_log(__METHOD__ . ':' . __LINE__ . ':' 
    . $e->getMessage());
    $success = FALSE;
  } catch (Throwable $e) {
    error_log(__METHOD__ . ':' . __LINE__ . ':' 
    . $e->getMessage());
    $success = FALSE;
  }
  return $success;
}

To round off the discussion, we need to define a remove() method, which deletes a customer from the database. Again, as with the save() method defined previously, we use fetchById() to ensure the operation was successful:

public function remove(Customer $cust)
{
  $sql = 'DELETE FROM ' . $cust::TABLE_NAME . ' WHERE id = :id';
  $stmt = $this->connection->pdo->prepare($sql);
  $stmt->execute(['id' => $cust->getId()]);
  return ($this->fetchById($cust->getId())) ? FALSE : TRUE;
}

How it works...

Copy the code described in steps 1 to 5 into a CustomerService.php file in the Application/Database folder. Define a chap_05_entity_to_query.php calling program. Have the calling program initialize the autoloader, using the appropriate classes:

<?php
define('DB_CONFIG_FILE', '/../config/db.config.php');
require __DIR__ . '/../Application/Autoload/Loader.php';
Application\Autoload\Loader::init(__DIR__ . '/..');
use Application\Database\Connection;
use Application\Database\CustomerService;

You can now create an instance of the service, and fetch a single customer at random. The service will then return a customer entity as a result:

// get service instance
$service = new CustomerService(new Connection(include __DIR__ . DB_CONFIG_FILE));

echo "\nSingle Result\n";
var_dump($service->fetchById(rand(1,79)));

Here is the output:

Now copy the code shown in steps 6 to 15 into the service class. Add the data to insert to the chap_05_entity_to_query.php calling program. We then generate a Customer entity instance using this data:

// sample data
$data = [
  'name'              => 'Doug Bierer',
  'balance'           => 326.33,
  'email'             => 'doug' . rand(0,999) . '@test.com',
  'password'          => 'password',
  'status'            => 1,
  'security_question' => 'Who\'s on first?',
  'confirm_code'      => 12345,
  'level'             => 'ADV'
];

// create new Customer
$cust = Customer::arrayToEntity($data, new Customer());

We can then examine the ID before and after the call to save():

echo "\nCustomer ID BEFORE Insert: {$cust->getId()}\n";
$cust = $service->save($cust);
echo "Customer ID AFTER Insert: {$cust->getId()}\n";

Finally, we modify the balance, and again call save(), viewing the results:

echo "Customer Balance BEFORE Update: {$cust->getBalance()}\n";
$cust->setBalance(999.99);
$service->save($cust);
echo "Customer Balance AFTER Update: {$cust->getBalance()}\n";
var_dump($cust);

Here is the output from the calling program:

There's more...

For more information on the relational model, please refer to https://en.wikipedia.org/wiki/Relational_model. For more information on RDBMS, please refer to https://en.wikipedia.org/wiki/Relational_database_management_system. For information on how PDOStatement::fetchObject() inserts property values even before the constructor, have a look at the comment by "rasmus at mindplay dot dk" in the php.net documentation reference on fetchObject() (http://php.net/manual/en/pdostatement.fetchobject.php).

Previous Chapter

Defining entities to match database tables

Next Chapter

Embedding secondary lookups into query results

Table of Contents for PHP 7: Real World Application Development

Tying entity classes to RDBMS queries

Note

How to do it...

Note

How it works...

There's more...

Table of Contents for
PHP 7: Real World Application Development