abstract custom hydrator poc

FetchEventsWithActivitiesAndRegistrations.php

<?php

use Doctrine\DBAL\Connection;

final class JoinMapping
{
    /**
     * @param non-empty-string $idFieldAlias DB column alias for ID
     * @param array<non-empty-string, non-empty-string> $fieldMap alias => outputKey
     * @param array<non-empty-string, self> $children outputKey => mapping
     */
    public function __construct(
        public readonly string $idFieldAlias,
        public readonly array $fieldMap,
        public readonly array $children = []
    ) {}
}


final class NestedJoinHydrator
{
    /**
     * @param list<array<string, mixed>> $rows
     * @return list<array<string, mixed>>
     */
    public function hydrate(array $rows, JoinMapping $mapping): array
    {
        $result = [];

        foreach ($rows as $row) {
            $this->processRow($result, $row, $mapping);
        }

        return array_values($result);
    }

    /**
     * @param array<string, array<string, mixed>> $resultRef
     * @param array<string, mixed> $row
     */
    private function processRow(array &$resultRef, array $row, JoinMapping $mapping): void
    {
        $id = (string) $row[$mapping->idFieldAlias];
        if (!isset($resultRef[$id])) {
            $item = [];
            foreach ($mapping->fieldMap as $alias => $key) {
                $item[$key] = $row[$alias];
            }
            foreach ($mapping->children as $childKey => $_) {
                $item[$childKey] = [];
            }
            $resultRef[$id] = $item;
        }

        foreach ($mapping->children as $childKey => $childMapping) {
            $childId = $row[$childMapping->idFieldAlias];
            if ($childId === null) {
                continue;
            }
            $this->processRow($resultRef[$id][$childKey], $row, $childMapping);
        }
    }
}


$rows = $connection->fetchAllAssociative(<<<SQL
    SELECT e.id AS e_id, e.name AS e_name,
           ea.id AS ea_id, ea.name AS ea_name,
           ear.member_id AS ear_member_id, ear.volunteer AS ear_volunteer
    FROM event e
    INNER JOIN event_activity ea ON e.id = ea.event_id
    LEFT JOIN event_activity_registration ear ON ea.id = ear.activity_id
    ORDER BY e.id, ea.id
SQL);


$mapping = new JoinMapping(
    idFieldAlias: 'e_id',
    fieldMap: [
        'e_id' => 'id',
        'e_name' => 'name',
    ],
    children: [
        'activities' => new JoinMapping(
            idFieldAlias: 'ea_id',
            fieldMap: [
                'ea_id' => 'id',
                'ea_name' => 'name',
            ],
            children: [
                'registrations' => new JoinMapping(
                    idFieldAlias: 'ear_member_id',
                    fieldMap: [
                        'ear_member_id' => 'mid',
                        'ear_volunteer' => 'vol',
                    ]
                )
            ]
        )
    ]
);
$events = (new NestedJoinHydrator())->hydrate($rows, $mapping);