Relationships

We saw which part ran while a model was saved. The author of a post, however, sits in another table, and when the query that reaches it runs cannot be read from how the model is written. The question is this: when the posts are listed, does the query that fetches their authors run at that moment, or when the author is first accessed?

The measurement axis of this lesson is under what condition the relationship query ran in this request. The decision order is the classify() function itself: ran, deferred, skipped, not wired. The dominant outcome is deferred: the part is left outside the request and does not run until it is accessed. The number of queries will also be printed, but this is a different thing from M10/K03: there the number of runs was measured; here the number does not determine the outcome. The outcome comes from the classify() output, which says why and under what condition the relationship ran; the number is only the evidence placed beside it.

VD12. The tables are PHP arrays, and every Store::select() call counts as one query; the query counter is kept in the data layer itself. VD13. With lazy loading the Ledger::defer() record is written when the object is created; that is, the relationship counts as deferred from the moment the object exists. VD14. Ten posts are distributed among three authors, and there is no query cache. VD15. With eager loading the relationship query runs as a single query right after the list’s query.

The measurement setup comes unchanged.

<?php
// ran.php — did an expected part run in this request?
declare(strict_types=1);

final class Ledger {
    public static array $path = [];        // parts placed on the request's path
    public static array $ran = [];         // parts called within the request
    public static array $deferred = [];    // parts left outside the request
    public static function newRequest(): void { self::$path = self::$ran = self::$deferred = []; }
    public static function onPath(string $part): void { self::$path[] = $part; }
    public static function ran(string $part): void { self::$ran[] = $part; }
    public static function defer(string $part): void { self::$deferred[] = $part; }
}

function classify(string $part): string {
    return match (true) {
        in_array($part, Ledger::$ran, true) => 'ran',
        in_array($part, Ledger::$deferred, true) => 'deferred',
        in_array($part, Ledger::$path, true) => 'skipped',
        default => 'not wired',
    };
}

Building the Relationships

A relationship is a method that goes from one model to another. In a one-to-one relationship an author has a single profile. In a one-to-many relationship an author has many posts, and each post has a single author. In a many-to-many relationship a post has many tags and a tag has many posts, and the relationship is kept in a pivot table. Lazy loading queries the relationship on first access; eager loading fetches the relationship together with the list query, with a single extra query.

The file below builds the three relationship types and the two loading styles. If the author was not loaded beforehand, a Post object writes the relation:author record as deferred while it is created; when the author is accessed the query runs and the record becomes ran. If it was loaded beforehand, the list loader writes the record as ran immediately.

<?php
// relationship.php — relationship methods, lazy and eager loading
declare(strict_types=1);
require_once 'ran.php';

final class Store {
    public static int $queries = 0;
    public static array $tables = [
        'authors'  => [['id' => 1, 'name' => 'Alice'], ['id' => 2, 'name' => 'Mark'], ['id' => 3, 'name' => 'Dana']],
        'profiles' => [['author_id' => 1, 'site' => 'alice.test'], ['author_id' => 2, 'site' => 'mark.test']],
        'tags'     => [['id' => 1, 'name' => 'php'], ['id' => 2, 'name' => 'web']],
        'post_tag' => [['post_id' => 1, 'tag_id' => 1], ['post_id' => 1, 'tag_id' => 2], ['post_id' => 2, 'tag_id' => 1]],
        'posts'    => [],
    ];

    public static function select(string $table, callable $condition): array {
        self::$queries++;
        return array_values(array_filter(self::$tables[$table], $condition));
    }
}
Store::$tables['posts'] = array_map(
    fn($i) => ['id' => $i, 'title' => "Post $i", 'author_id' => ($i % 3) + 1],
    range(1, 10)
);

final class Author {
    public function __construct(public array $row) {}
    public function profile(): ?array {      // one-to-one
        return Store::select('profiles', fn($p) => $p['author_id'] === $this->row['id'])[0] ?? null;
    }
    public function posts(): array {         // one-to-many
        return Store::select('posts', fn($p) => $p['author_id'] === $this->row['id']);
    }
}

final class Post {
    private array $loaded = [];

    public function __construct(public array $row, ?array $author = null) {
        if ($author === null) Ledger::defer('relation:author');
        else $this->loaded['author'] = $author;
    }

    public function author(): array {        // the post's single author
        if (!isset($this->loaded['author'])) {
            Ledger::ran('relation:author');
            $this->loaded['author'] = Store::select('authors', fn($a) => $a['id'] === $this->row['author_id'])[0];
        }
        return $this->loaded['author'];
    }

    public function tags(): array {          // many-to-many, through the pivot table
        $ids = array_column(Store::select('post_tag', fn($r) => $r['post_id'] === $this->row['id']), 'tag_id');
        return Store::select('tags', fn($t) => in_array($t['id'], $ids, true));
    }

    public static function listAll(array $load = []): array {
        $rows = Store::select('posts', fn() => true);
        $authors = null;
        if (in_array('author', $load, true)) {
            Ledger::ran('relation:author');
            $ids = array_unique(array_column($rows, 'author_id'));
            $authors = array_column(Store::select('authors', fn($a) => in_array($a['id'], $ids, true)), null, 'id');
        }
        return array_map(fn($r) => new self($r, $authors[$r['author_id']] ?? null), $rows);
    }
}

function newRequest(): void { Ledger::newRequest(); Store::$queries = 0; }
function report(array $parts): void {
    foreach ($parts as $part) printf("  %-16s %s\n", $part, classify($part));
    echo "  queries=" . Store::$queries . "\n";
}

First, look at the results of the three relationship types; there is no outcome here, only the evidence that the relationships work.

<?php
// types.php — the three relationship types
require_once 'relationship.php';

$authors = array_map(fn($r) => new Author($r), Store::$tables['authors']);
echo "profile: " . $authors[0]->profile()['site'] . "\n";
echo "author 1 post count: " . count($authors[0]->posts()) . "\n";
$post = Post::listAll()[0];
echo "post 1 tags: " . implode(',', array_column($post->tags(), 'name')) . "\n";

profile: alice.test
author 1 post count: 3
post 1 tags: php,web

Lazy Loading

The measurement lists ten posts and does not yet look at their authors. Expected: the list query has run, the relationship is deferred, and the number of queries is 1. Then a loop accesses the author of every post. Expected: the relationship is ran and the number of queries is 1 + 10.

<?php
// lazy.php — lazy loading and the N+1 problem
require_once 'relationship.php';

newRequest();
$posts = Post::listAll();
echo "listed: " . count($posts) . " posts\n";
report(['relation:author']);
foreach ($posts as $post) $post->author();
echo "authors accessed\n";
report(['relation:author']);

listed: 10 posts
  relation:author  deferred
  queries=1
authors accessed
  relation:author  ran
  queries=11

When the list ran, the relationship query had not yet been executed: deferred, with one query. The loop ran a separate author query for each post; this is the N+1 problem: one list query, and one more query for every row in the list. The same author is queried more than once, because there is no cache. The number shows the problem; the outcome shows the cause of the problem: the relationship had been deferred until access, and the access was inside a loop.

The Opposing Measurement: Eager Loading

The same list is loaded by asking for the author relationship. Expected: the relationship ran together with the list, even before access, and the number of queries is 2.

<?php
// eager.php — with eager loading the relationship runs together with the list
require_once 'relationship.php';

newRequest();
$posts = Post::listAll(['author']);
echo "listed: " . count($posts) . " posts\n";
report(['relation:author']);
foreach ($posts as $post) $post->author();
echo "authors accessed\n";
report(['relation:author']);

listed: 10 posts
  relation:author  ran
  queries=2
authors accessed
  relation:author  ran
  queries=2

The relationship ran without any post’s author being looked at; the number of queries did not change after the loop. Its cost is that the query runs even if the authors are never looked at. The choice between lazy loading and eager loading depends on whether it is known that the relationship will be accessed.

The Bounding Measurement

Does a relationship that is not accessed stay deferred, or is it not wired? The answer depends on where the record is written. Here Ledger::defer() is written when the object is created, so the relationship counts as part of the existing object. In a request in which no post’s author is accessed, relation:author still stays deferred. In the same request, a relationship whose name is written nowhere, for example relation:profile, leaves no record and falls into the not wired outcome.

<?php
// unaccessed.php — an unaccessed relationship stays deferred, one with no record is not wired
require_once 'relationship.php';

newRequest();
$posts = Post::listAll();
echo "authors never accessed\n";
report(['relation:author', 'relation:profile']);

authors never accessed
  relation:author  deferred
  relation:profile not wired
  queries=1

relation:author is deferred, relation:profile is not wired. Neither ever ran, but the difference between them is large: a deferred relationship knows that it can run with an access; a part that is not wired never had such a path defined. In a design that writes the record not when the object is created but during access, relation:author would also be not wired in this request. So the outcome is information that depends on the choice of the record point, not on the relationship itself; this choice was given explicitly at the beginning of the lesson.

Summary

• One-to-one, one-to-many and many-to-many relationships are each a model method; the many-to-many relationship uses the pivot table.
• This lesson’s outcome is deferred: with lazy loading, the relationship query did not run until it was accessed, even after the list query.
• Access in a loop produced the N+1 problem; the number of queries showed the problem, and the outcome showed the cause of the deferral.
• With eager loading the relationship ran together with the list; its cost is that the query runs even if it is not accessed.
• A relationship that is never accessed stays deferred; a relationship with no record is not wired.

Next Step

Relationships rest on tables, and tables come from somewhere. What happens when the migration that builds the schema runs a second time? The next lesson, Migrations and Seeding, measures migrations and seeding, and shows that on the second run the migration falls into the skipped outcome.