Implementing object-relational mapping

There are two primary techniques to achieve a relational mapping between objects. The first technique involves pre-loading the related child objects into the parent object. The advantage to this approach is that it is easy to implement, and all parent-child information is immediately available. The disadvantage is that large amounts of memory are potentially consumed, and the performance curve is skewed.

The second technique is to embed a secondary lookup into the parent object. In this latter approach, when you need to access the child objects, you would run a getter that would perform the secondary lookup. The advantage of this approach is that performance demands are spread out throughout the request cycle, and memory usage is (or can be) more easily managed. The disadvantage of this approach is that there are more queries generated, which means more work for the database server.

Note

Please note, however, that we will show how the use of prepared statements can be used to greatly offset this disadvantage.

How to do it...

Let's have a look at two techniques to implement object-relational mapping.

Technique #1 - pre-loading all child information

First, we will discuss how to implement object relational mapping by pre-loading all child information into the parent class. For this illustration, we will use three related database tables, customer, purchases, and products:

We will use the existing Application\Entity\Customer class (defined in Chapter 5, Interacting with a Database, in the Defining entity classes to match database tables recipe) as a model to develop an Application\Entity\Purchase class. As before, we will use the database definition as the basis of the entity class definition. Here is the database definition for the purchases table:

CREATE TABLE `purchases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction` varchar(8) NOT NULL,
  `date` datetime NOT NULL,
  `quantity` int(10) unsigned NOT NULL,
  `sale_price` decimal(8,2) NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_C3F3` (`customer_id`),
  KEY `IDX_665A` (`product_id`),
  CONSTRAINT `FK_665A` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `FK_C3F3` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
);

Based on the customer entity class, here is how Application\Entity\Purchase might look. Note that not all getters and setters are shown:

namespace Application\Entity;

class Purchase extends Base
{

  const TABLE_NAME = 'purchases';
  protected $transaction = '';
  protected $date = NULL;
  protected $quantity = 0;
  protected $salePrice = 0.0;
  protected $customerId = 0;
  protected $productId = 0;

  protected $mapping = [
    'id'            => 'id',
    'transaction'   => 'transaction',
    'date'          => 'date',
    'quantity'      => 'quantity',
    'sale_price'    => 'salePrice',
    'customer_id'   => 'customerId',
    'product_id'    => 'productId',
  ];

  public function getTransaction() : string
  {
    return $this->transaction;
  }
  public function setTransaction($transaction)
  {
    $this->transaction = $transaction;
  }
  // NOTE: other getters / setters are not shown here
}

We are now ready to define Application\Entity\Product. Here is the database definition for the products table:

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sku` varchar(16) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` varchar(4096) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `special` int(11) NOT NULL,
  `link` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_38C4` (`sku`)
);

Based on the customer entity class, here is how Application\Entity\Product might look:

namespace Application\Entity;

class Product extends Base
{

  const TABLE_NAME = 'products';
  protected $sku = '';
  protected $title = '';
  protected $description = '';
  protected $price = 0.0;
  protected $special = 0;
  protected $link = '';

  protected $mapping = [
    'id'          => 'id',
    'sku'         => 'sku',
    'title'       => 'title',
    'description' => 'description',
    'price'       => 'price',
    'special'     => 'special',
    'link'        => 'link',
  ];

  public function getSku() : string
  {
    return $this->sku;
  }
  public function setSku($sku)
  {
    $this->sku = $sku;
  }
  // NOTE: other getters / setters are not shown here
}

Next, we need to implement a way to embed related objects. We will start with the Application\Entity\Customer parent class. For this section, we will assume the following relationships, illustrated in the following diagram:
- One customer, many purchases
- One purchase, one product

Accordingly, we define a getter and setter that process purchases in the form of an array of objects:

protected $purchases = array();
public function addPurchase($purchase)
{
  $this->purchases[] = $purchase;
}
public function getPurchases()
{
  return $this->purchases;
}

Now we turn our attention to Application\Entity\Purchase. In this case, there is a 1:1 relationship between a purchase and a product, so there's no need to process an array:
```
protected $product = NULL;
public function getProduct()
{
  return $this->product;
}
public function setProduct(Product $product)
{
  $this->product = $product;
}
```
Note
Notice that in both entity classes, we do not alter the $mapping array. This is because implementing object relational mapping has no bearing on the mapping between entity property names and database column names.
Since the core functionality of obtaining basic customer information is still needed, all we need to do is to extend the Application\Database\CustomerService class described in Chapter 5, Interacting with a Database, in the Tying entity classes to RDBMS queries recipe. We can create a new Application\Database\CustomerOrmService_1 class, which extends Application\Database\CustomerService:
```
namespace Application\Database;
use PDO;
use PDOException;
use Application\Entity\Customer;
use Application\Entity\Product;
use Application\Entity\Purchase;
class CustomerOrmService_1 extends CustomerService
{
  // add methods here
}
```

We then add a method to the new service class that performs a lookup and embeds the results, in the form of Product and Purchase entities, into the core customer entity. This method performs a lookup in the form of a JOIN. This is possible because there is a 1:1 relationship between purchase and product. Because the id column has the same name in both tables, we need to add the purchase ID column as an alias. We then loop through the results, creating Product and Purchase entities. After overriding the ID, we can then embed the Product entity into the Purchase entity, and then add the Purchase entity to the array in the Customer entity:

protected function fetchPurchasesForCustomer(Customer $cust)
{
  $sql = 'SELECT u.*,r.*,u.id AS purch_id '
    . 'FROM purchases AS u '
    . 'JOIN products AS r '
    . 'ON r.id = u.product_id '
    . 'WHERE u.customer_id = :id '
    . 'ORDER BY u.date';
  $stmt = $this->connection->pdo->prepare($sql);
  $stmt->execute(['id' => $cust->getId()]);
  while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $product = Product::arrayToEntity($result, new Product());
    $product->setId($result['product_id']);
    $purch = Purchase::arrayToEntity($result, new Purchase());
    $purch->setId($result['purch_id']);
    $purch->setProduct($product);
    $cust->addPurchase($purch);
  }
  return $cust;
}

Next, we provide a wrapper for the original fetchById() method. This block of code needs to not only get the original Customer entity, but needs to look up and embed Product and Purchase entities. We can call the new fetchByIdAndEmbedPurchases() method and accept a customer ID as an argument:
```
public function fetchByIdAndEmbedPurchases($id)
{
  return $this->fetchPurchasesForCustomer(
    $this->fetchById($id));
}
```

Technique #2 - embedding secondary lookups

Now we will cover embedding secondary lookups into the related entity classes. We will continue to use the same illustration as above, using the entity classes defined that correspond to three related database tables, customer, purchases, and products:

The mechanics of this approach are quite similar to those described in the preceding section. The main difference is that instead of doing the database lookup, and producing entity classes right away, we will embed a series of anonymous functions that will do the same thing, but called from the view logic.
We need to add a new method to the Application\Entity\Customer class that adds a single entry to the purchases property. Instead of an array of Purchase entities, we will be supplying an anonymous function:
```
public function setPurchases(Closure $purchaseLookup)
{
  $this->purchases = $purchaseLookup;
}
```

Next, we will make a copy of the Application\Database\CustomerOrmService_1 class, and call it Application\Database\CustomerOrmService_2:

namespace Application\Database;
use PDO;
use PDOException;
use Application\Entity\Customer;
use Application\Entity\Product;
use Application\Entity\Purchase;
class CustomerOrmService_2 extends CustomerService
{
  // code
}

We then define a fetchPurchaseById() method, which looks up a single purchase based on its ID and produces a Purchase entity. Because we will ultimately be making a series of repetitive requests for single purchases in this approach, we can regain database efficiency by working off the same prepared statement, in this case, a property called $purchPreparedStmt:

public function fetchPurchaseById($purchId)
{
  if (!$this->purchPreparedStmt) {
      $sql = 'SELECT * FROM purchases WHERE id = :id';
      $this->purchPreparedStmt = 
      $this->connection->pdo->prepare($sql);
  }
  $this->purchPreparedStmt->execute(['id' => $purchId]);
  $result = $this->purchPreparedStmt->fetch(PDO::FETCH_ASSOC);
  return Purchase::arrayToEntity($result, new Purchase());
}

After that, we need a fetchProductById() method that looks up a single product based on its ID and produces a Product entity. Given that a customer may have purchased the same product several times, we can introduce an additional level of efficiency by storing acquired product entities in a $products array. In addition, as with purchases, we can perform lookups on the same prepared statement:

public function fetchProductById($prodId)
{
  if (!isset($this->products[$prodId])) {
      if (!$this->prodPreparedStmt) {
          $sql = 'SELECT * FROM products WHERE id = :id';
          $this->prodPreparedStmt = 
          $this->connection->pdo->prepare($sql);
      }
      $this->prodPreparedStmt->execute(['id' => $prodId]);
      $result = $this->prodPreparedStmt
      ->fetch(PDO::FETCH_ASSOC);
      $this->products[$prodId] = 
        Product::arrayToEntity($result, new Product());
  }
  return $this->products[$prodId];
}

We can now rework the fetchPurchasesForCustomer() method to have it embed an anonymous function that makes calls to both fetchPurchaseById() and fetchProductById(), and then assigns the resulting product entity to the newly found purchase entity. In this example, we do an initial lookup that just returns the IDs of all purchases for this customer. We then embed a sequence of anonymous functions in the Customer::$purchases property, storing the purchase ID as the array key, and the anonymous function as its value:

public function fetchPurchasesForCustomer(Customer $cust)
{
  $sql = 'SELECT id '
    . 'FROM purchases AS u '
    . 'WHERE u.customer_id = :id '
    . 'ORDER BY u.date';
  $stmt = $this->connection->pdo->prepare($sql);
  $stmt->execute(['id' => $cust->getId()]);
  while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $cust->addPurchaseLookup(
    $result['id'],
    function ($purchId, $service) { 
      $purchase = $service->fetchPurchaseById($purchId);
      $product  = $service->fetchProductById(
                  $purchase->getProductId());
      $purchase->setProduct($product);
      return $purchase; }
    );
  }
  return $cust;
}

How it works...

Define the following classes based on the steps from this recipe as follows:

Class	Technique #1 steps
`Application\Entity\Purchase`	1 - 2, 7
`Application\Entity\Product`	3 - 4
`Application\Entity\Customer`	6, 16, + described in Chapter 5, Interacting with a Database.
`Application\Database\CustomerOrmService_1`	8 - 10

The second approach to this would be as follows:

Class	Technique #2 steps
`Application\Entity\Customer`	2
`Application\Database\CustomerOrmService_2`	3 - 6

In order to implement approach #1, where entities are embedded, define a calling program called chap_11_orm_embedded.php, which sets up autoloading and uses 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\CustomerOrmService_1;

Next, create an instance of the service, and look up a customer using a random ID:

$service = new CustomerOrmService_1(new Connection(include __DIR__ . DB_CONFIG_FILE));
$id   = rand(1,79);
$cust = $service->fetchByIdAndEmbedPurchases($id);

In the view logic, you will have acquired a fully populated Customer entity by way of the fetchByIdAndEmbedPurchases() method. Now all you need to do is to call the right getters to display information:

  <!-- Customer Info -->
  <h1><?= $cust->getname() ?></h1>
  <div class="row">
    <div class="left">Balance</div><div class="right">
      <?= $cust->getBalance(); ?></div>
  </div>
    <!-- etc. -->

The logic needed to display purchase information would then look something like the following HTML. Notice that Customer::getPurchases() returns an array of Purchase entities. To get product information from the Purchase entity, inside the loop, call Purchase::getProduct(), which produces a Product entity. You can then call any of the Product getters, in this example, Product::getTitle():

  <!-- Purchases Info -->
  <table>
  <?php foreach ($cust->getPurchases() as $purchase) : ?>
  <tr>
  <td><?= $purchase->getTransaction() ?></td>
  <td><?= $purchase->getDate() ?></td>
  <td><?= $purchase->getQuantity() ?></td>
  <td><?= $purchase->getSalePrice() ?></td>
  <td><?= $purchase->getProduct()->getTitle() ?></td>
  </tr>
  <?php endforeach; ?>
</table>

Turning your attention to the second approach, which uses secondary lookups, define a calling program called chap_11_orm_secondary_lookups.php, which sets up autoloading and uses 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\CustomerOrmService_2;

Next, create an instance of the service, and look up a customer using a random ID:

$service = new CustomerOrmService_2(new Connection(include __DIR__ . DB_CONFIG_FILE));
$id   = rand(1,79);

You can now retrieve an Application\Entity\Customer instance and call fetchPurchasesForCustomer() for this customer, which embeds the sequence of anonymous functions:

$cust = $service->fetchById($id);
$cust = $service->fetchPurchasesForCustomer($cust);

The view logic for displaying core customer information remains the same as described previously. The logic needed to display purchase information would then look something like the following HTML code snippet. Notice that Customer::getPurchases() returns an array of anonymous functions. Each function call returns one specific purchase and related products:

<table>
  <?php foreach($cust->getPurchases() as $purchId => $function) : ?>
  <tr>
  <?php $purchase = $function($purchId, $service); ?>
  <td><?= $purchase->getTransaction() ?></td>
  <td><?= $purchase->getDate() ?></td>
  <td><?= $purchase->getQuantity() ?></td>
  <td><?= $purchase->getSalePrice() ?></td>
  <td><?= $purchase->getProduct()->getTitle() ?></td>
  </tr>
  <?php endforeach; ?>
</table>

Here is an example of the output:

Tip

Best practice

Although each iteration of the loop represents two independent database queries (one for purchase, one for product), efficiency is retained by the use of prepared statements. Two statements are prepared in advance: one that looks up a specific purchase, and one that looks up a specific product. These prepared statements are then executed multiple times. Also, each product retrieval is independently stored in an array, resulting in even greater efficiency.

Table of Contents for
PHP 7: Real World Application Development

Implementing object-relational mapping

Note

How to do it...

Technique #1 - pre-loading all child information

Note

Technique #2 - embedding secondary lookups

How it works...

Tip

See also

Table of Contents for PHP 7: Real World Application Development

Implementing object-relational mapping

Note

How to do it...

Technique #1 - pre-loading all child information

Note

Technique #2 - embedding secondary lookups

How it works...

Tip

See also

Table of Contents for
PHP 7: Real World Application Development