[Help] How to mapping many-to-many relationships and accessing intermediate table

[thalesbarbosab]

Hey guys.

I would really appreciate the community's help, because I'm following the fluent documentation step by step, but I'm not succeeding.

I'm migrating a project from eloquent to doctrine, where the tables are outside the class mapping convention.

I've reached an impasse in the many-to-many relationship where I need access to intermediate table columns.

I ran the command php artisan doctrine:migrations:diff to check the mapping with the current database state and I only had a few changes in the name of the foreign keys.

But when trying to access the relationship, I don't have access to the objects of the intermediate class, an empty collection is returned to me.

Calls and responses

When calling the estimate repository and the entry types property that represents the intermediate table, I get this return.

$estimate_repository = EntityManager::getRepository(Estimate::class);
$estimate = $estimate_repository->findOneBy(['id'=>1]);
dd($estimate);

ER Diagram:

Entities:

Estimate

namespace App\Domain\Estimate;

use DateTime;

use App\Domain\Entry\EntryType;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;

class Estimate
{
    public $id;
    private $created_at;
    private $updated_at;

    public Collection $entry_types;

    public function __construct(
        public DateTime $period,
        public float $start_balance,
        public ?string $description = null,
        public bool $active,
    )
    {
        $this->entry_types = new ArrayCollection();
    }
}

EntryType

namespace App\Domain\Entry;

use Doctrine\Common\Collections\ArrayCollection;

class EntryType
{
    public $id;
    private $created_at;
    private $updated_at;

    public $estimates;

    public function __construct(
        public string $description,
        public string $type,
        public string $status,
    ){
        $this->estimates = new ArrayCollection();
    }
}

EstimateEntryType (intermediate)

namespace App\Domain\Estimate;

use App\Domain\Entry\EntryType;

class EstimateEntryType
{
    private $created_at;
    private $updated_at;

    public function __construct(
        public Estimate $estimate,
        public EntryType $entry_type,
        public float $value,
        public string $comment,
    )
    {
        //
    }
}

Mappers

Estimate

namespace App\Infrastructure\LaravelDoctrine\Mappings\Estimate;

use LaravelDoctrine\Fluent\Fluent;
use LaravelDoctrine\Fluent\EntityMapping;
use LaravelDoctrine\Fluent\Builders\Field;

use App\Domain\Estimate\Estimate;
use App\Domain\Entry\EntryType;
use App\Domain\Estimate\EstimateEntryType;

class EstimateMapper extends EntityMapping
{
    /**
     * Returns the fully qualified name of the class that this mapper maps.
     *
     * @return string
     */
    public function mapFor()
    {
        return Estimate::class;
    }

    /**
     * Load the object's metadata through the Metadata Builder object.
     *
     * @param Fluent $builder
     */
    public function map(Fluent $builder)
    {
        $builder->table('orcamentos');
        $builder->increments('id')->unsigned();
        $builder->string('description', function(Field $field){
            $field->name('descricao')->length(150)->nullable();
        });
        $builder->date('period', function(Field $field){
            $field->name('data');
        });
        $builder->unique(['data']);
        $builder->float('start_balance', function(Field $field){
            $field->name('saldo_inicial_quanto_sobra')->default(0)->nullable();
        });
        $builder->boolean('active', function(Field $field){
            $field->name('ativo')->default(0);
        });
        $builder->dateTime('created_at')->nullable()->timestampable()->onCreate();
        $builder->dateTime('updated_at')->nullable()->timestampable()->onUpdate();
        $builder->oneToMany(EstimateEntryType::class, 'entry_types')
                ->mappedBy('estimate')
                ->fetch('EAGER');
    }
} 

EntryType

namespace App\Infrastructure\LaravelDoctrine\Mappings\Entry;

use LaravelDoctrine\Fluent\Fluent;
use LaravelDoctrine\Fluent\EntityMapping;
use LaravelDoctrine\Fluent\Builders\Field;

use App\Domain\Entry\EntryType;
use App\Domain\Estimate\Estimate;
use App\Domain\Estimate\EstimateEntryType;

class EntryTypeMapper extends EntityMapping
{
    /**
     * Returns the fully qualified name of the class that this mapper maps.
     *
     * @return string
     */
    public function mapFor()
    {
        return EntryType::class;
    }

    /**
     * Load the object's metadata through the Metadata Builder object.
     *
     * @param Fluent $builder
     */
    public function map(Fluent $builder)
    {
        $builder->table('tipolancamentos');
        $builder->increments('id')->unsigned();
        $builder->string('description', function(Field $field){
            $field->name('descricao')->length(150);
        });
        $builder->string('type', function(Field $field){
            $field->name('tipo')->length(1)->nullable();
        });
        $builder->string('status')->length(1);
        $builder->dateTime('created_at')->nullable()->timestampable()->onCreate();
        $builder->dateTime('updated_at')->nullable()->timestampable()->onUpdate();

        $builder->oneToMany(EstimateEntryType::class, 'estimates')
                ->mappedBy('entry_types');
    }
} 

EstimateEntryType (intermediate table)

namespace App\Infrastructure\LaravelDoctrine\Mappings\Estimate;

use LaravelDoctrine\Fluent\Fluent;
use LaravelDoctrine\Fluent\EntityMapping;
use LaravelDoctrine\Fluent\Builders\Field;

use App\Domain\Estimate\Estimate;
use App\Domain\Entry\EntryType;
use App\Domain\Estimate\EstimateEntryType;

class EstimateEntryTypeMapper extends EntityMapping
{
    /**
     * Returns the fully qualified name of the class that this mapper maps.
     *
     * @return string
     */
    public function mapFor()
    {
        return EstimateEntryType::class;
    }

    /**
     * Load the object's metadata through the Metadata Builder object.
     *
     * @param Fluent $builder
     */
    public function map(Fluent $builder)
    {
        $builder->table('orcamentotipolancamentos');
        $builder->string('value', function(Field $field){
            $field->name('valor')->length(50)->nullable();
        });
        $builder->text('comment', function(Field $field){
            $field->name('comentario')->nullable();
        });
        $builder->dateTime('created_at')->nullable()->timestampable()->onCreate();
        $builder->dateTime('updated_at')->nullable()->timestampable()->onUpdate();
        $builder->manyToOne(EntryType::class,'entry_type')
                     ->foreignKey('tipolancamento_id')
                     ->fetch('EAGER');
        $builder->manyToOne(Estimate::class,'estimate')
                ->foreignKey('orcamento_id')
                ->fetch('EAGER');
        $builder->primary(['entry_type','estimate']);
    }
}

Generated diff migration

declare(strict_types=1);

namespace Database\Migrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
final class Version20230301190808 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Rename orcamentotipolancamentos indexes';
    }

    public function up(Schema $schema): void
    {
        $this->addSql('ALTER TABLE orcamentotipolancamentos ADD CONSTRAINT FK_ED4B2808E421AB39 FOREIGN KEY (tipolancamento_id) REFERENCES tipolancamentos (id)');
        $this->addSql('ALTER TABLE orcamentotipolancamentos ADD CONSTRAINT FK_ED4B2808A01CF1CA FOREIGN KEY (orcamento_id) REFERENCES orcamentos (id)');
        $this->addSql('ALTER TABLE orcamentotipolancamentos RENAME INDEX orcamentotipolancamentos_orcamento_id_foreign TO IDX_ED4B2808A01CF1CA');
    }

    public function down(Schema $schema): void
    {
        $this->addSql('ALTER TABLE orcamentotipolancamentos DROP FOREIGN KEY FK_ED4B2808E421AB39');
        $this->addSql('ALTER TABLE orcamentotipolancamentos DROP FOREIGN KEY FK_ED4B2808A01CF1CA');
        $this->addSql('ALTER TABLE orcamentotipolancamentos RENAME INDEX idx_ed4b2808a01cf1ca TO orcamentotipolancamentos_orcamento_id_foreign');
    }
}

[eigan]

Note that the find* call wont take a trip to the database if the entity is already in UnitOfWork. Instead, it will just return that entity. So if you create an EstimateEntryType, flush it, and not refresh the Estimate entity, will it not magically update the collection. EntityManager::refresh($estimate) will update the entity for you. What you could do is let the Estimate entity create the EstimateEntryType and update the collection.

Just a heads up: I would recommend to use the attribute driver instead. The fluent driver is laravel-doctrine specific and currently no active maintainer have any knowledge of this mapping driver.

[thalesbarbosab]

Hello @eigan I figured something would be inconsistent in the package code itself. I've been trying to map these classes since February 24th and following the doc. I will do as instructed. I'm going to switch from drive fluent to annotations. Thanks for your great help.