doctrine not setting foreign key value when persisting-CodePudding

I'm trying to setup entities with Doctrine and I'm stuck with this error : When persisting the data, I've got a SQL error about a non null constraint violation. Indeed, Doctrine has generated an insert statement without the value of a foreign key.

An exception occurred while executing 'INSERT INTO user_access_company (end_at, created_at, updated_at, user_id, company_id) VALUES (?, ?, ?, ?, ?)' with params [null, "2022-12-12 23:06:57 0000", "2022-12-12 23:06:57 0000", null, 82]:  SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column "user_id" violates not-null constraint

Here are my entities

class Participant implements ParticipantInterface
{
    protected $id;

    protected $source;
    protected $identifier;
    protected $firstName;
    protected $lastName;
    protected $emailAddress;
    protected $createdAt;
    protected $updatedAt;
}

class UserAccessCompany
{
    private Participant $user;
    private Company $company;
    private ?\DateTimeImmutable $endAt;
    private \DateTimeImmutable $createdAt;
    private \DateTimeImmutable $updatedAt;
}

and the related xml mappings

<?xml version="1.0" encoding="UTF-8"?>
<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping"
                  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                  xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping
                  http://doctrine-project.org/schemas/orm/doctrine-mapping.xsd"
                  xmlns:gedmo="http://gediminasm.org/schemas/orm/doctrine-extensions-mapping"
                  >

    <entity name="Participant" table="message_participant">
        <id name="id" type="bigint">
            <generator strategy="AUTO" />
        </id>

        <field name="source" type="string" />
        <field name="identifier" type="integer" />
        <field name="firstName" type="string" />
        <field name="lastName" type="string" nullable="true"/>
        <field name="emailAddress" type="string" nullable="true">

        <field name="createdAt" type="datetime"><gedmo:timestampable on="create"/></field>
        <field name="updatedAt" type="datetime"><gedmo:timestampable on="update"/></field>
    </entity>
</doctrine-mapping>

<?xml version="1.0" encoding="utf-8"?>
<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                  xmlns:gedmo="http://gediminasm.org/schemas/orm/doctrine-extensions-mapping"
                  xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping https://www.doctrine-project.org/schemas/orm/doctrine-mapping.xsd">
  <entity name="UserAccessCompany" table="user_access_company">
    <id name="user" association-key="true"/>
    <id name="company" association-key="true"/>
    <field name="endAt" type="datetimetz_immutable" column="end_at" nullable="true"/>
    <field name="createdAt" type="datetimetz_immutable" column="created_at"><gedmo:timestampable on="create"/></field>
    <field name="updatedAt" type="datetimetz_immutable" column="updated_at"><gedmo:timestampable on="create"/></field>
    <many-to-one field="user" target-entity="Participant">
      <join-columns>
        <join-column name="user_id" referenced-column-name="identifier"/>
      </join-columns>
    </many-to-one>
    <many-to-one field="company" target-entity="Company">
      <join-columns>
        <join-column name="company_id"/>
      </join-columns>
    </many-to-one>
  </entity>
</doctrine-mapping>

I'm using a simple code like this to insert the data :

$userAccessCompany = new UserAccessCompany($user, $company, $endAt);
$this->getEntityManager()->persist($userAccessCompany);
$this->getEntityManager()->flush();

$user and $company are entities that are already persisted in the db, if I dump $user->identifier I have a value inside. Yet, The generated SQL statement is this : INSERT INTO user_access_company (end_at, created_at, updated_at, user_id, company_id) VALUES (null, "2022-12-12 23:06:57 0000", "2022-12-12 23:06:57 0000", null, 82) so the user_id value is missing

The company entity is declared similarly (without specifying the inversedBy/mappedBy) and when I have generated the migration There was a foreign key between on userAccessCompany.user_id referencing participant.identifier so doctrine seems to be aware of this relation

Few additional notes, I've simplified the entities to make them readable here, I have getters and setters defined in all entities. I also tried to use annotation mapping without luck and adding an id field into the userAccessCompany with the removal of the primary key on user/company

For reference, I'm using Symfony 4.4 and doctrine/orm 2.7.3

CodePudding user response：

This is a known limitation of doctrine, I missed that in the doc.

https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/limitations-and-known-issues.html#join-columns-with-non-primary-keys

Join-Columns with non-primary keys

It is not possible to use join columns pointing to non-primary keys. Doctrine will think these are the primary keys and create lazy-loading proxies with the data, which can lead to unexpected results. Doctrine can for performance reasons not validate the correctness of this settings at runtime but only through the Validate Schema command.