Embedding secondary lookups into query results

On the road towards implementing relationships between entity classes, let us first take a look at how we can embed the code needed to perform a secondary lookup. An example of such a lookup is when displaying information on a customer, have the view logic perform a second lookup that gets a list of purchases for that customer.

Note

The advantage of this approach is that processing is deferred until the actual view logic is executed. This will ultimately smooth the performance curve, with the workload distributed more evenly between the initial query for customer information, and the later query for purchase information. Another benefit is that a massive JOIN is avoided with its inherent redundant data.

How to do it...

First of all, define a function that finds a customer based on their ID. For the purposes of this illustration, we will simply fetch an array using the fetch mode PDO::FETCH_ASSOC. We will also continue to use the Application\Database\Connection class discussed in Chapter 1, Building a Foundation:
```
function findCustomerById($id, Connection $conn)
{
  $stmt = $conn->pdo->query(
    'SELECT * FROM customer WHERE id = ' . (int) $id);
  $results = $stmt->fetch(PDO::FETCH_ASSOC);
  return $results;
}
```

Next, we analyze the purchases table to see how the customer and product tables are linked. As you can see from the CREATE statement for this table, the customer_id and product_id foreign keys form the relationships:

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')
);

We now expand the original findCustomerById() function, defining the secondary lookup in the form of an anonymous function, which can then be executed in a view script. The anonymous function is assigned to the $results['purchases'] element:

function findCustomerById($id, Connection $conn)
{
  $stmt = $conn->pdo->query(
       'SELECT * FROM customer WHERE id = ' . (int) $id);
  $results = $stmt->fetch(PDO::FETCH_ASSOC);
  if ($results) {
    $results['purchases'] = 
      // define secondary lookup
      function ($id, $conn) {
        $sql = 'SELECT * FROM purchases AS u '
           . 'JOIN products AS r '
           . 'ON u.product_id = r.id '
           . 'WHERE u.customer_id = :id '
           . 'ORDER BY u.date';
          $stmt = $conn->pdo->prepare($sql);
          $stmt->execute(['id' => $id]);
          while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            yield $row;
          }
      };
  }
  return $results;
}

Assuming we have successfully retrieved customer information into a $results array, in the view logic, all we need to do is to loop through the return value of the anonymous function. In this example, we retrieve customer information at random:
```
$result = findCustomerById(rand(1,79), $conn);
```

In the view logic, we loop through the results returned by the secondary lookup. The call to the embedded anonymous function is highlighted in the following code:

<table>
  <tr>
<th>Transaction</th><th>Date</th><th>Qty</th>
<th>Price</th><th>Product</th>
  </tr>
<?php 
foreach ($result['purchases']($result['id'], $conn) as $purchase) : ?>
  <tr>
    <td><?= $purchase['transaction'] ?></td>
    <td><?= $purchase['date'] ?></td>
    <td><?= $purchase['quantity'] ?></td>
    <td><?= $purchase['sale_price'] ?></td>
    <td><?= $purchase['title'] ?></td>
  </tr>
<?php endforeach; ?>
</table>

How it works...

Create a chap_05_secondary_lookups.php calling program and insert the code needed to create an instance of Application\Database\Connection:

<?php
define('DB_CONFIG_FILE', '/../config/db.config.php');
include __DIR__ . '/../Application/Database/Connection.php';
use Application\Database\Connection;
$conn = new Connection(include __DIR__ . DB_CONFIG_FILE);

Next, add the findCustomerById()function shown in step 3. You can then pull information for a random customer, ending the PHP part of the calling program:

function findCustomerById($id, Connection $conn)
{
  // code shown in bullet #3 above
}
$result = findCustomerById(rand(1,79), $conn);
?>

For the view logic, you can display core customer information as shown in several of the preceding recipes:

<h1><?= $result['name'] ?></h1>
<div class="row">
<div class="left">Balance</div>
<div class="right"><?= $result['balance']; ?></div>
</div>
<!-- etc.l -->

You can display information on purchases like so:

<table>
<tr><th>Transaction</th><th>Date</th><th>Qty</th>
<th>Price</th><th>Product</th></tr>
  <?php 
  foreach ($result['purchases']($result['id'], $conn) as $purchase) : ?>
  <tr>
    <td><?= $purchase['transaction'] ?></td>
    <td><?= $purchase['date'] ?></td>
    <td><?= $purchase['quantity'] ?></td>
    <td><?= $purchase['sale_price'] ?></td>
    <td><?= $purchase['title'] ?></td>
  </tr>
<?php endforeach; ?>
</table>

The critical piece is that the secondary lookup is performed as part of the view logic by calling the embedded anonymous function, $result['purchases']($result['id'], $conn). Here is the output:

Previous Chapter

Tying entity classes to RDBMS queries

Next Chapter

Implementing jQuery DataTables PHP lookups

Table of Contents for PHP 7: Real World Application Development

Embedding secondary lookups into query results

Note

How to do it...

How it works...

Table of Contents for
PHP 7: Real World Application Development