Populate Projection with multiple tables

[webdevilopers]

Came from:

• https://twitter.com/webdevilopers/status/1423593062920593411

Here is a current example where we need to get guest data from a different stream before we insert a related (guestId) visit data - promoting the unique ID - at all. What are the options beside event enriching? Separate repository calls sound like a bad idea.

As suggested by @prooph and @gquemener for SQL projections:

Instead of storing guest data in projection state, write it to a table that is only used by the projection.

IIUC, you could store your guest data (guestId, postalCode, ...) in a separate table and make a classic inner join when querying your "audience" table (as you have the guestId when inserting into this table, you can reference it). You may even use your existing read model class, with specialized methods (eg. insertAudience, insertGuest, ...). There's no need to make a separate read model for the guest table, IMO. Only advice would be to limit join within the read model (as in a single-table read model).

I will post two possible solutions for this in Prooph soon.

[webdevilopers]

Solution 1: A single Manager and Read Model for Projection "audience"

Both tables are managed by a single read model.

// ...

final class PgsqlAudienceProjection implements ReadModelProjection
{
    public function project(ReadModelProjector $projector): ReadModelProjector
    {
        $projector->fromStreams('guest_profile_stream', 'visit_stream', 'guest_preference_stream')
            ->when([
                GuestProfileCreated::class => function ($state, GuestProfileCreated $event) {
                     /** @var PgsqlAudienceReadModel $readModel */
                    $readModel = $this->readModel();
                    $readModel->stack('insertGuest', [/*...*/]);
                },
                GuestSelfCheckedIn::class => function ($state, GuestSelfCheckedIn $event) {
                    /** @var PgsqlAudienceReadModel $readModel */
                    $readModel = $this->readModel();
                    $readModel->stack('insert', [/*...*/]);
                }
            ]);

        return $projector;
    }
}

final class PgsqlAudienceReadModel extends AbstractReadModel
{
    private \PDO $connection;

    public function __construct(\PDO $connection)
    {
        $this->connection = $connection;
    }

    public function init() : void
    {
        $this->connection->exec(sprintf('create table %1$s (
            visit_id uuid not null,
            guest_id uuid not null,
            // ...
            primary key (visit_id)
        );', ProjectionName::AUDIENCE));

        $this->connection->exec(sprintf('create table %1$s (
            guest_id uuid not null,
            // ...
            primary key (guest_id)
        );', ProjectionName::GUEST_DATA));
    }

    public function reset() : void
    {
        $this->connection->exec(sprintf('TRUNCATE TABLE %s', ProjectionName::AUDIENCE));
        $this->connection->exec(sprintf('TRUNCATE TABLE %s', ProjectionName::GUEST_DATA));
    }

    public function delete() : void
    {
        $this->connection->exec(sprintf('DROP TABLE %s', ProjectionName::AUDIENCE));
        $this->connection->exec(sprintf('DROP TABLE %s', ProjectionName::GUEST_DATA));
    }

    protected function insertGuest(array $data): void
    {
        // ...
    }

    protected function insert(array $data): void
    {
        // ...
    }

    // ...
}

Solution 2: A single Manager and separate Read Models for Projection "audience"

// ...

final class PgsqlAudienceProjection implements ReadModelProjection
{
    public function project(ReadModelProjector $projector): ReadModelProjector
    {
        $projector->fromStreams('guest_profile_stream', 'visit_stream', 'guest_preference_stream')
            ->when([
                GuestProfileCreated::class => function ($state, GuestProfileCreated $event) {
                     /** @var PgsqlGuestDataReadModel $readModel */
                    $readModel = $this->readModel();
                    $readModel->stack('insert', [/*...*/]);
                },
                GuestSelfCheckedIn::class => function ($state, GuestSelfCheckedIn $event) {
                    $guest = $state['guests'][$event->guestId()->toString()];

                    /** @var PgsqlAudienceReadModel $readModel */
                    $readModel = $this->readModel();
                    $readModel->stack('insert', [/*...*/]);
                }
            ]);

        return $projector;
    }
}

Which one would you prefer @codeliner @prolic?

[prolic]

I have projectors per use case, not per table. It could be however, that your use case involves one projector per table.