Relationships

A relationship is a method on a record class naming the table on the other side and the column that joins them. There are four — two spellings of "one", one of "many", and one for the inverse — plus with(), which loads a relationship alongside the query that fetched the parent.

Declaring Relationships#

Each relationship method calls one of the four, passing the class on the other side and the joining column. The $options and $eager arguments are declared on your method and handed straight through, which matters more than it looks — the section on eager loading below is the reason.

<?php

namespace App\Table;

use Pop\Db\Record;

class Authors extends Record
{

    public function role(?array $options = null, bool $eager = false)
    {
        return $this->hasOneOf('App\Table\Roles', 'role_id', $options, $eager);
    }

    public function profile(?array $options = null, bool $eager = false)
    {
        return $this->hasOne('App\Table\Profiles', 'author_id', $options, $eager);
    }

    public function posts(?array $options = null, bool $eager = false)
    {
        return $this->hasMany('App\Table\Posts', 'author_id', $options, $eager);
    }

}

An authors row has one role, one profile and many posts. Which of the four you reach for is decided by one question: which table holds the joining column?

hasOneOf() and belongsTo() do the same lookup and differ only in intent: belongsTo() is the name to use on the child of a hasOne()/hasMany() pair, and hasOneOf() for a plain lookup table that nothing else owns. role_id above is a lookup, so it's hasOneOf().

The first argument is a class name as a string, and a string is never resolved against the file's namespace declaration. Inside namespace App\Table;, hasOne('Profiles', ...) looks for a global \Profiles and fails with Error: Class "Profiles" not found the first time the relationship is called. Write the fully-qualified name every time.

Declare every relationship method as public function posts(?array $options = null, bool $eager = false) and pass both through, so eager loading can reach it.

One-to-One#

The child side of a hasOne() names the same column and points back:

<?php

namespace App\Table;

use Pop\Db\Record;

class Profiles extends Record
{

    protected array $primaryKeys = ['author_id'];

    public function author(?array $options = null, bool $eager = false)
    {
        return $this->belongsTo('App\Table\Authors', 'author_id', $options, $eager);
    }

}

Calling either side runs one query and returns one record:

use App\Table\Authors;
use App\Table\Profiles;

$author = Authors::findById(1);

echo $author->profile()->bio;
echo $author->role()->role;

$profile = Profiles::findById(1);

echo $profile->author()->username;

A relationship with nothing on the other side comes back as an empty record of that class, exactly the way an unmatched findById() does — not null. Test it the same way, by checking a column:

use App\Table\Authors;

$profile = Authors::findById(3)->profile();

if (!isset($profile->author_id)) {
    // This author has no profile row
}

One-to-Many#

hasMany() returns a Pop\Db\Record\Collection, so it's countable, iterable, and every element is a live record you can modify and save. An author with no posts gives an empty collection rather than nothing to loop over:

use App\Table\Authors;

$author = Authors::findById(1);

foreach ($author->posts() as $post) {
    echo $post->title;
}

echo count($author->posts());

Two helpers collapse a hasMany() down to a single row. latest() and oldest() are called on the parent record, before the relationship, and take the column to sort by — id unless you say otherwise:

use App\Table\Authors;

$author = Authors::findById(1);

$newest = $author->latest()->posts();               // highest id
$first  = $author->oldest('created_at')->posts();   // earliest created_at

echo $newest->title;

Underneath, both set order and limit on the relationship and unwrap the one-row collection, so what comes back is a Posts record rather than a collection of one.

latest() and oldest() set a flag that holds for the life of the record instance, so fetch a fresh one for an unlimited hasMany() afterward.

Many-to-Many#

There's no fifth method for many-to-many. A join table is two one-to-many relationships, and a hasMany() carrying a join option walks both of them in one query. post_tags holds post_id and tag_id; the relationship selects from tags, joins post_tags onto it, and matches on post_id:

<?php

namespace App\Table;

use Pop\Db\Record;

class Posts extends Record
{

    public function author(?array $options = null, bool $eager = false)
    {
        return $this->belongsTo('App\Table\Authors', 'author_id', $options, $eager);
    }

    public function tags(?array $options = null, bool $eager = false)
    {
        return $this->hasMany('App\Table\Tags', 'post_id', [
            'select' => ['tags.id', 'tags.tag', 'post_tags.post_id'],
            'join'   => ['table' => 'post_tags', 'columns' => ['post_tags.tag_id' => 'tags.id']]
        ], $eager);
    }

}

use App\Table\Posts;

foreach (Posts::findById(1)->tags() as $tag) {
    echo $tag->tag;
}

That returns Tags records, one query, on MySQL, PostgreSQL and SQLite alike. The joining column is named unqualified because hasMany() builds the predicate from it — which works as long as one of the two joined tables has that column. Qualify it as 'post_tags.post_id' if both do.

When the join table carries columns of its own — a sort position, a timestamp — give it a record class instead and treat it as a one-to-many with a hasOneOf() hanging off it. A join table keyed on both columns declares both, the same way any composite key does:

<?php

namespace App\Table;

use Pop\Db\Record;

class PostTags extends Record
{

    protected array $primaryKeys = ['post_id', 'tag_id'];

    public function tag(?array $options = null, bool $eager = false)
    {
        return $this->hasOneOf('App\Table\Tags', 'tag_id', $options, $eager);
    }

}

Posts then carries an ordinary hasMany('App\Table\PostTags', 'post_id') beside the tags() above. Each element of it is a join row with its own columns, and $postTag->tag() reaches the tag from there — one query per join row rather than one for the set, which is the price of having somewhere to put those extra columns.

Eager Loading#

Calling a relationship inside a loop over a collection runs one query per row. with() names the relationships up front so they are fetched with the parent instead, and it's a static call that replaces the finder's find prefix with get:

use App\Table\Authors;

$author = Authors::with('posts')->getById(1);

foreach ($author->posts as $post) {
    echo $post->title;
}

The result is a property rather than a method call. with() accepts an array for more than one, and a dotted name walks a relationship on the loaded records — posts.tags loads each author's posts and each of those posts' tags:

use App\Table\Authors;

$author = Authors::with(['role', 'profile', 'posts'])->getById(1);
$author = Authors::with('posts.tags')->getById(1);

Write the property form in a view: $author->posts returns the eager-loaded collection when with() asked for it and calls the relationship method when it did not. An unmatched relationship loaded through with() gives null for the one-record forms and an empty collection for hasMany().

Batched eager loading is portable across every adapter — each relationship resolves as one query, built with the placeholder form the adapter uses.

Shaping a Relationship with Options#

Every relationship method takes the same $options array a finder takes — select, order, limit, offset, join and group, documented in full on Records & the ORM — and applies it to the related rows:

use App\Table\Authors;

$recent = Authors::findById(1)->posts([
    'order' => 'created_at DESC',
    'limit' => 5
]);

There's a seventh key that only a relationship reads. columns takes a predicate array in the same shorthand a findBy() takes and adds it as a further condition on the related rows, so it's how you ask for some of them:

use App\Table\Authors;

$published = Authors::findById(1)->posts([
    'columns' => ['status' => 'published']
]);

findBy(), findAll() and the other direct finders never read columns — it does nothing at all outside a relationship. Within one, hasOne() and hasMany() both honor it; hasOneOf() and belongsTo() ignore it, since both fetch a single row by primary key and there's nothing to narrow.

Include the joining column in a select on an eager hasMany() — the batch is keyed back to its parents by that column. An unrecognized key raises an E_USER_NOTICE on the lazy path, which goes through findBy(); the eager path builds its SQL directly and raises nothing.

See Also#

• Records & the ORM — defining the record classes, the finders and the $options array
• Query Builder — joins and subqueries, for the questions a relationship does not express
• Querying — the adapter underneath, and the profiler that counts the queries a loop costs
• pop-db README — composite-key relationships and the full API surface