Db — TableRepository + Query

TableRepository doet CRUD op gewone tabellen (members, orders, …); Query is de fluent builder erachter. Onveilige veldnamen worden geweigerd, massa-delete/update zonder filter throwt — gebruik ->deleteAll() als je dat echt wil. Alle voorbeelden hier draaien tegen een SQLite-fixture die we per snippet opbouwen.

CRUD-basics: insert, find, update, delete

find($id) is een shortcut rond query()->where(pk, $id)->first().

$members = new TableRepository($pdo, 'members');

$id  = $members->insert(['email' => 'jan@x', 'name' => 'Jan', 'age' => 28, 'country' => 'nl', 'active' => 1]);
$row = $members->find($id);

$members->update($id, ['name' => 'Jan Jansen']);
$members->delete($id);

return [
    'inserted_id'  => $id,
    'after_delete' => $members->find($id),    // null — net gewist
];

Array
(
    [inserted_id] => 6
    [after_delete] => 
)

Query builder — where / whereIn / orderBy

where(col, value) is shortcut voor =. Met operator als 2e arg krijg je <, >=, !=, …

$members = new TableRepository($pdo, 'members');

return $members->query()
    ->where('active', 1)
    ->where('age', '>=', 18)
    ->whereIn('country', ['nl', 'be'])
    ->orderBy('name')
    ->limit(10)
    ->get();

Array
(
    [0] => Array
        (
            [id] => 1
            [email] => anna@x
            [name] => Anna
            [age] => 25
            [country] => nl
            [active] => 1
        )

    [1] => Array
        (
            [id] => 5
            [email] => eva@x
            [name] => Eva
            [age] => 35
            [country] => nl
            [active] => 1
        )

)

Groeperen — nest()/unnest() of whereGroup(closure)

Twee stijlen, dezelfde uitkomst. nest() opent een sub-tree, unnest() sluit hem.

$members = new TableRepository($pdo, 'members');

// Stijl 1: nest/unnest
$a = $members->query()
    ->where('active', 1)
    ->nest()
        ->where('age', '>=', 30)
        ->orWhere('country', 'be')
    ->unnest()
    ->toSql();

// Stijl 2: closure
$b = $members->query()
    ->where('active', 1)
    ->whereGroup(fn($q) => $q->where('age', '>=', 30)->orWhere('country', 'be'))
    ->toSql();

return ['nest' => $a[0], 'closure' => $b[0]];

Array
(
    [nest] => SELECT * FROM `members` WHERE `active` = ? AND (`age` >= ? OR `country` = ?)
    [closure] => SELECT * FROM `members` WHERE `active` = ? AND (`age` >= ? OR `country` = ?)
)

paginate(page, perPage)

Returnt items + meta (total, totalPages, hasMore).

$members = new TableRepository($pdo, 'members');

return $members->query()->orderBy('id')->paginate(page: 1, perPage: 2);
// ['items', 'page', 'perPage', 'total', 'totalPages', 'hasMore']

Array
(
    [items] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [email] => anna@x
                    [name] => Anna
                    [age] => 25
                    [country] => nl
                    [active] => 1
                )

            [1] => Array
                (
                    [id] => 2
                    [email] => bert@x
                    [name] => Bert
                    [age] => 17
                    [country] => be
                    [active] => 1
                )

        )

    [page] => 1
    [perPage] => 2
    [total] => 5
    [totalPages] => 3
    [hasMore] => 1
)

Safety: massa-delete zonder filter throwt

Tegen het per ongeluk wissen van een hele tabel. Gebruik deleteAll() als je dat echt wil.

$members = new TableRepository($pdo, 'members');

try {
    $members->query()->delete();   // geen where → exception
    return 'unexpected';
} catch (\LogicException $e) {
    return ['safety' => $e->getMessage()];
}

Array
(
    [safety] => delete() zonder filter is niet toegestaan — gebruik deleteAll() als je dit echt bedoelt.
)

columns() whitelist

Ongedefinieerde kolommen worden gericht geweigerd — nuttig wanneer je vanuit een form direct schrijft.

$members = (new TableRepository($pdo, 'members'))->columns(['email', 'name']);

try {
    $members->insert(['email' => 'x', 'name' => 'X', 'rogue' => 1]);
    return 'unexpected';
} catch (\InvalidArgumentException $e) {
    return ['blocked' => $e->getMessage()];
}

Array
(
    [blocked] => Kolom 'rogue' staat niet in de whitelist van tabel 'members'.
)

Events — BeforeInsert kan data muteren

Listeners op de PSR-14 EventDispatcher kunnen $data herschrijven (audit-velden, hashing, etc.).

$dispatcher = new class implements \Psr\EventDispatcher\EventDispatcherInterface {
    public function dispatch(object $event): object {
        if ($event instanceof \Framework\Events\Db\BeforeInsertEvent) {
            $d = $event->getData();
            $d['name'] = strtoupper($d['name']);
            $event->setData($d);
        }
        return $event;
    }
};

$members = new TableRepository($pdo, 'members', events: $dispatcher);
$id = $members->insert(['email' => 'a@b', 'name' => 'jan', 'age' => 20, 'country' => 'nl', 'active' => 1]);

return ['name_after_event' => $members->find($id)['name']];   // 'JAN'

Array
(
    [name_after_event] => JAN
)

DynamicWriter — schrijven naar DynamicItems

Voor DynamicItems is er een aparte writer. create/update/setActive/delete + auto-cache-warm.

$writer = new \Framework\Dynamic\DynamicWriter($pdo, $structure, $cacheWarmer);

$id = $writer->create('contact', [
    'name'  => 'Jan',
    'email' => 'jan@x.nl',
], language: 'nl');

$writer->update($id, ['phone' => '06-1234']);
$writer->setActive($id, false);
$writer->delete($id);

Werkt — zie /dynamic-cache voor een live demo van het cache-pad. Voor een PHP-side demo zonder live DB hier zou ik een SQLite-fixture met DynamicTypes/DynamicItems/DynamicItemsValues moeten opzetten — dat doet tests/Framework/Dynamic/DynamicWriterTest.php al. Bekijk daar de roundtrip.