Symfony-Doctrine : join a table with a view after persisting entity

Question

In my database, I have a table T and a view V. The view has some columns of my table and other data (from other tables).

In Symfony, I declared my view as a read-only Entity.

/**
  * @ORM\Table(name="V")
  * @ORM\Entity(readOnly=true, repositoryClass="AppBundle\Entity\Repository\VRepository")
  */
class V
{

In my T entity, I did a Join :

/**
  * @ORM\OneToOne(targetEntity="V")
  * @ORM\JoinColumn(name="T_id", referencedColumnName="V_id")
  */
 private $view;

And I added just the getter :

/**
 * Get view
 *
 * @return \AppBundle\Entity\V
 */
public function getView()
{
     return $this->view;
}

Everything is working well when I want to read and show data.

But I have a problem after persisting a new T entity. Symfony seems to lost posted data of my form when I create a new T entity (editAction() works perfectly).

An exception occurred while executing 'INSERT INTO T (T_id, T_name, updated_at) VALUES (?, ?, ?)' with params [null, null, "2017-09-01 15:30:41"]:

SQLSTATE[23000]: Integrity constraint violation: 1048 Field 'T_id' cannot be empty (null)

When I remove ORM annotations of the $view property, it creates correctly my new record T in the database.

I think the problem is due to the fact that the V entity (the record in my SQL view) will exist just after the creation of T. And when I persist/flush data in Symfony, V doesn't exist yet. They are "created" at the same time.

I tried to add Doctrine @HasLifecycleCallbacks on my T entity and the @PostPersist event on the getView() method but it doesn't change anything...

Any idea to differ the Join after the creation of the entity ?

I know it's not conventional to use views as entities with Symfony but I haven't other choice.

Answer 1

I've just checked, it works fine with Bidirectional One-To-One relation

In my case tables are defined like:

create table T (`id` int(11) NOT NULL AUTO_INCREMENT, name varchar(100), primary key (id));
create view V as select id as entity, name, '123' as number from T;

Annotations in T:

/**
 * @ORM\Table(name="T")
 * @ORM\Entity()
 */
class T
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, nullable=true)
     */
    private $name;

    /**
     * @var V
     *
     * @ORM\OneToOne(targetEntity="V", mappedBy="entity")
     */
    private $view;

Annotations in V:

/**
 * @ORM\Table(name="V")
 * @ORM\Entity(readOnly=true)
 */
class V
{
    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, nullable=true)
     */
    private $name;

    /**
     * @var string
     *
     * @ORM\Column(name="number", type="string", length=255, nullable=true)
     */
    private $number;

    /**
     * @var T
     *
     * @ORM\Id
     * @ORM\OneToOne(targetEntity="T", inversedBy="view")
     * @ORM\JoinColumn(name="entity", referencedColumnName="id")
     */
    private $entity;

And a test snippet to prove that it saves, updates and reads fine:

public function testCRUD()
{
    /** @var EntityManager $manager */
    $manager = $this->client->getContainer()->get('doctrine.orm.default_entity_manager');

    $t = new T();
    $t->setName('Me');

    $manager->persist($t);
    $manager->flush();

    $t->setName('He');
    $manager->flush();

    $manager->clear();
    /** @var T $t */
    $t = $manager->find(T::class, $t->getId());
    $this->assertEquals('He', $t->getView()->getName());
}