A Repository Class for a Table

SQL scattered through controllers and templates is hard to find, test or change. A repository gathers the queries for one table behind methods named after what the application needs, receives its connection through the constructor (Subsection 4.9.15), and returns the Product objects of Subsection 4.16.9:

ProductRepository: the products table behind named methodsPHP

<?php
require 'Product.php';
final class ProductRepository {
  private const string SELECT = 'SELECT id, sku, title, price, stock FROM products';
  public function __construct(private readonly PDO $pdo) {}
  public function find(int $id): ?Product {
    $st = $this->pdo->prepare(self::SELECT . ' WHERE id = ?');
    $st->execute([$id]);
    return $st->fetchObject(Product::class) ?: null;
  }
  /** @return list<Product> */
  public function inCategory(int $categoryId): array {
    $st = $this->pdo->prepare(self::SELECT . ' WHERE category_id = ? ORDER BY title');
    $st->execute([$categoryId]);
    return $st->fetchAll(PDO::FETCH_CLASS, Product::class);
  }
}
$products = new ProductRepository(require 'db.php');
echo $products->find(99)?->label() ?? 'no product 99', "\n";
foreach ($products->inCategory(3) as $p) echo $p->label(), ", stock $p->stock\n";

Output

no product 99
BK-SQL-02 Indexing Deep Dive (29.00), stock 0
BK-SQL-01 SQL Queries That Scale (44.50), stock 12

fetchObject() returns false when no row matches; ?: null turns that into the promised ?Product, so callers write ?->. The typed constant (PHP 8.3) keeps the column list, which must match the class, in one place. Writes follow the same pattern: a restock(string $sku, int $qty) method runs its UPDATE and returns rowCount() === 1. Because the class is given its PDO, a test can hand it an in-memory SQLite 4,756 connection.

MENU

A Repository Class for a Table