Queries Behind Object–Relational Mapping

The schema is now on record. When an object–relational mapping is built on top of it, the application code sees objects instead of rows and does not write the queries. The queries are still sent; only where they go and how many of them go can no longer be read from the code. The question is this: how many queries go to the database while a list is printed?

This lesson does not build a model. The Object–Relational Mapping and Relationships lessons in the Web Application Framework course covered the model and when a relationship is loaded. An earlier course also measured the query count; in that measurement the number was only a count of runs. Here the number is a result and what is asked is its owner: does the client, the environment or the server determine the number? The decision order is the same: client, third party, environment; if none of them can change the result, the server. The party cell of this lesson is the server, and the core is below.

<?php
// party.php — who can change this result?
declare(strict_types=1);

// The parties outside the code, in decision order: every request, every external call, every deployment.
const EXTERNAL_PARTIES = ['client', 'third party', 'environment'];

/** Computes the result once without deviation, then once with each external party's deviation. */
function probe(callable $result, array $deviations): array {
    $baseline = $result([]);
    $changed = [];
    foreach (EXTERNAL_PARTIES as $party) {
        if (array_key_exists($party, $deviations)) {
            $changed[$party] = $result([$party => $deviations[$party]]) !== $baseline;
        }
    }
    return $changed;
}

/** The first external party that can change the result; the server if none can. */
function party(array $changed): string {
    foreach (EXTERNAL_PARTIES as $p) {
        if ($changed[$p] ?? false) return $p;
    }
    return 'server';
}

function partyReport(string $name, callable $result, array $deviations): string {
    $changed = probe($result, $deviations);
    $lines = [];
    foreach ($changed as $p => $c) $lines[] = $p . ($c ? ' changed' : ' could not change');
    $owner = party($changed);
    echo "$name → $owner\n    ", implode(' · ', $lines), "\n";
    return $owner;
}

DA13. What the mapping does is shown with a small class that wraps PDO calls; every select() call counts as one query and the query text is recorded as well. DA14. There are three authors and six posts; the authors of the posts are 1 and 2, and author 3 has no posts yet. There is no query cache. DA15. The client’s deviation is adding one more post; the value of the deviation is the author number of the added post. DA16. The result is the number of queries sent while a list is printed.

The Counter and Two Loaders

The file below sets up the counter and the data, and then loads the list in two ways. Lazy loading fetches the list with one query and then asks for the author of each post with a separate query. Eager loading fetches the list with one query and then brings all the required authors in a single additional query with an IN (...) list. This behavior in the lazy form is called the N+1 problem: one list query, plus one query per row.

<?php
// counter.php — a wrapper that counts PDO calls and two list loaders
declare(strict_types=1);

final class CountingDatabase {
    public int $queries = 0;
    public array $texts = [];

    public function __construct(private PDO $pdo) {}

    public function select(string $sql, array $values = []): array {
        $this->queries++;
        $this->texts[] = $sql;
        $statement = $this->pdo->prepare($sql);
        $statement->execute($values);
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }
}

function setUpData(?int $extraAuthor = null): CountingDatabase {
    $pdo = new PDO('sqlite::memory:');
    $pdo->exec('CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT)');
    $pdo->exec('CREATE TABLE posts (id INTEGER PRIMARY KEY, author_id INTEGER, title TEXT)');
    $pdo->exec("INSERT INTO authors VALUES (1, 'alice'), (2, 'bob'), (3, 'carol')");
    $insert = $pdo->prepare('INSERT INTO posts (author_id, title) VALUES (?, ?)');
    for ($i = 1; $i <= 6; $i++) $insert->execute([($i % 2) + 1, "post $i"]);
    if ($extraAuthor !== null) $insert->execute([$extraAuthor, 'post 7']);
    return new CountingDatabase($pdo);
}

function listLazy(CountingDatabase $db): array {
    $lines = [];
    foreach ($db->select('SELECT author_id, title FROM posts ORDER BY id') as $post) {
        $author = $db->select('SELECT name FROM authors WHERE id = ?', [$post['author_id']]);
        $lines[] = $post['title'] . ' — ' . $author[0]['name'];
    }
    return $lines;
}

function listEager(CountingDatabase $db): array {
    $posts = $db->select('SELECT author_id, title FROM posts ORDER BY id');
    $ids = array_values(array_unique(array_column($posts, 'author_id')));
    sort($ids);
    $placeholders = implode(', ', array_fill(0, count($ids), '?'));
    $names = array_column($db->select("SELECT id, name FROM authors WHERE id IN ($placeholders)", $ids), 'name', 'id');
    return array_map(fn(array $p) => $p['title'] . ' — ' . $names[$p['author_id']], $posts);
}

Run the two loaders on the same data. The list should be the same; the difference shows up only in the query count.

<?php
// list.php — two loaders, the same list, a different query count
declare(strict_types=1);
require 'counter.php';

$lazy = setUpData();
$lazyList = listLazy($lazy);
$eager = setUpData();
$eagerList = listEager($eager);

echo 'first line: ', $lazyList[0], "\n";
echo 'same list: ', var_export($lazyList === $eagerList, true), "\n";
echo 'lazy: ', $lazy->queries, " queries\n";
echo 'eager: ', $eager->queries, " queries\n";

first line: post 1 — bob
same list: true
lazy: 7 queries
eager: 2 queries

Both loaders produced the same six lines. Lazy loading sent seven queries: one for the list and six for the authors. Eager loading sent two. The mapping tool makes the two look the same on the code side; the difference remains only in the traffic that goes to the database.

Whose Number Is It

Now treat the number as a result. The client’s deviation is one more post being added to the list, and the author of the post is number 3. In lazy loading every post brings an author query, so the count goes from seven to eight. In eager loading the author query stays single; the new post does not take part in the query count, it only adds one value to the IN list.

<?php
// query-count.php — who can change the query count?
declare(strict_types=1);
require 'party.php';
require 'counter.php';

function queryCount(string $loading, ?int $extraAuthor): int {
    $db = setUpData($extraAuthor);
    $loading === 'lazy' ? listLazy($db) : listEager($db);
    return $db->queries;
}

partyReport('lazy loading', fn(array $d) => queryCount('lazy', $d['client'] ?? null), ['client' => 3]);
partyReport('eager loading', fn(array $d) => queryCount('eager', $d['client'] ?? null), ['client' => 3]);

lazy loading → client
    client changed
eager loading → server
    client could not change

The client could change the query count of lazy loading because it grows with the number of rows: every new post raised the count by one. In eager loading the count stayed at two; because the deviation had no effect, the result passed to the server. The defense mechanism, that is eager loading, moved the result from the client to the server. This means that the loading style is chosen in the code; the choice is made by the code that is written, not by the incoming request.

When the Author Is Never Read

Eager loading does not always send fewer queries. If the list prints only the titles, the author is never accessed; in this case lazy loading never sends the relationship query, while eager loading still sends a query whose result will not be used. The difference between the two loading styles depends on whether the relationship is accessed. The file below rebuilds the same list with the two loaders so that only the titles are printed.

<?php
// titles.php — a list in which the relationship is never accessed
declare(strict_types=1);
require 'counter.php';

$lazy = setUpData();
$titles = array_column($lazy->select('SELECT title FROM posts ORDER BY id'), 'title');
echo 'lazy, titles only, query count: ', $lazy->queries, "\n";

$eager = setUpData();
listEager($eager);
echo 'eager, authors fetched in vain, query count: ', $eager->queries, "\n";
$titles2 = array_column($eager->select('SELECT title FROM posts ORDER BY id'), 'title');
echo 'titles the same: ', var_export($titles === $titles2, true), "\n";

lazy, titles only, query count: 1
eager, authors fetched in vain, query count: 2
titles the same: true

When lazy loading asked only for the titles, a single query was enough. Eager loading fetched the authors needlessly. For this reason the protection that eager loading moves to the server holds for lists in which the relationship is actually used; for a relationship that is not used, the same choice sends an extra query.

The Bounding Measurement

The query count of eager loading is constant; but what about the query itself? The number of placeholders in the IN list is built from the number of distinct authors in the list. If the text of the second query is taken as the result instead of the query count, the question changes. Try the two forms of the client’s deviation: when the new post belongs to a new author (3) and when it belongs to an author who is already in the list (1).

<?php
// query-text.php — the query text, not the query count
declare(strict_types=1);
require 'party.php';
require 'counter.php';

function secondQuery(?int $extraAuthor): string {
    $db = setUpData($extraAuthor);
    listEager($db);
    return $db->texts[1];
}

partyReport('IN list, new author', fn(array $d) => secondQuery($d['client'] ?? null), ['client' => 3]);
partyReport('IN list, existing author', fn(array $d) => secondQuery($d['client'] ?? null), ['client' => 1]);

IN list, new author → client
    client changed
IN list, existing author → server
    client could not change

The result depends on the data sent. When a new author entered the list, the number of placeholders grew and the query text changed by the client’s hand. When a post of an existing author was added, the text stayed the same. The query count had moved to the server; the query text had not. Which result a defense protects depends on what that result measures.

Summary

• An object–relational mapping hides the queries; a counter that wraps PDO calls makes visible how many queries go out.
• In lazy loading the query count grows with the number of rows (the N+1 problem); the client can change the count.
• In eager loading the query count stays at two with the IN list; the party in this lesson is the server.
• In the same mechanism the query text can be changed by the client according to the data sent.
• What a defense protects depends on the measured result: the count was protected, the text was not.

Next Step

While the number and the text of the queries are under control, the data the queries return also crosses a boundary. Data that comes from a partner outside the application arrives as JSON, XML or CSV, and its format can change. The next lesson handles what happens when the partner’s data changes format.