JOIN clause compilation

Advanced5 min read·fw-07-004

sql

Concept

JOIN clause compilation extends the Query Builder to support SQL JOIN operations. JOINs combine rows from multiple tables based on a related column. The Query Builder must track join definitions and compile them into the correct SQL fragment.

JOIN types:

INNER JOIN (or JOIN): Only rows where the join condition matches in BOTH tables.
LEFT JOIN: All rows from the left table, NULL for right-table columns when no match.
RIGHT JOIN: All rows from the right table, NULL for left-table columns when no match.
CROSS JOIN: Cartesian product — every row from left × every row from right. Rare.

Join condition: ON left_table.column = right_table.column. Can also include additional AND conditions.

Query Builder join() API:

join($table, $first, $operator, $second): INNER JOIN.
leftJoin($table, $first, $operator, $second): LEFT JOIN.
joinSub($query, $alias, ...): Join against a subquery.
joinWhere($table, $first, $operator, $value): Join where the second operand is a literal value (not a column).

Building the SQL: The Grammar's compileJoins(array $joins) method iterates the join definitions and builds the SQL fragment: INNER JOIN users ON posts.user_id = users.id.

Alias support: JOIN users AS u ON posts.user_id = u.id — use aliases to disambiguate columns when joining a table multiple times.

Code Example

php

<?php
namespace Framework\Database;

class JoinClause
{
    private array $ons = [];

    public function __construct(
        public readonly string $type,   // 'INNER JOIN', 'LEFT JOIN', 'RIGHT JOIN'
        public readonly string $table,
    ) {}

    public function on(string $first, string $operator, string $second): static
    {
        $this->ons[] = ['type' => 'on', 'first' => $first, 'operator' => $operator, 'second' => $second];
        return $this;
    }

    public function andOn(string $first, string $operator, string $second): static
    {
        return $this->on($first, $operator, $second);
    }

    public function getOns(): array { return $this->ons; }
}

class QueryBuilder
{
    private array $joins = [];

    public function join(string $table, string $first, string $operator, string $second): static
    {
        $join = new JoinClause('INNER JOIN', $table);
        $join->on($first, $operator, $second);
        $this->joins[] = $join;
        return $this;
    }

    public function leftJoin(string $table, string $first, string $operator, string $second): static
    {
        $join = new JoinClause('LEFT JOIN', $table);
        $join->on($first, $operator, $second);
        $this->joins[] = $join;
        return $this;
    }
}

class Grammar
{
    public function compileJoins(array $joins): string
    {
        return implode(' ', array_map(function(JoinClause $join) {
            $ons = array_map(
                fn($on) => "{$on['first']} {$on['operator']} {$on['second']}",
                $join->getOns()
            );
            return "{$join->type} {$join->table} ON " . implode(' AND ', $ons);
        }, $joins));
    }
}

// Usage
$query = $builder
    ->table('posts')
    ->select(['posts.id', 'posts.title', 'users.name AS author_name', 'users.email AS author_email'])
    ->join('users', 'posts.user_id', '=', 'users.id')
    ->leftJoin('categories', 'posts.category_id', '=', 'categories.id')
    ->where('posts.status', '=', 'published')
    ->orderBy('posts.published_at', 'DESC');

// Compiled:
// SELECT posts.id, posts.title, users.name AS author_name, users.email AS author_email
// FROM posts
// INNER JOIN users ON posts.user_id = users.id
// LEFT JOIN categories ON posts.category_id = categories.id
// WHERE posts.status = ?
// ORDER BY posts.published_at DESC

PreviousWHERE clause compilation — conditions and bindingsQuery Builder from Scratch NextINSERT, UPDATE, DELETE buildersQuery Builder from Scratch