Can Doctrine define MySQL's Generated columns?

Question

Is it correct (and if so how) to use mySQL's generated column within Symfony entities?

For example, the GENERATED ALWAYS in the below example:

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    fullname varchar(101) GENERATED ALWAYS AS (concat(first_name,' ',last_name)),
    email VARCHAR(100) NOT NULL
);

I understand this could be done in an entities __construct() but would it be more correct to have it handled by Doctrine/mySQL? Something like:

/**
 * @ORM\Column(type="text")
 * @ORM\Generated(concat(first_name,' ',last_name))
 */
private $fullname;

Answer 1

Firstly, I'd like apologise for this answer being so late, but I was able to create a workaround for basic SELECT and JOIN queries via DQL. I have not tested this with UPDATE queries as a result of modifying the resulting entity.

As the user dbu above noted, you'll need to first follow this guide: https://www.liip.ch/en/blog/doctrine-and-generated-columns

This prevents doctrine from attempting to modify the generated column when running the schema builder. This does not have doctrine ignore the column when entity updates occur.

You'll want to ensure that you create a migration that adds the generated column. I achieved this like so:

/**
 * Adds a generated column for fullname to the contacts table
 *
 * @param Schema $schema
 */
public function up(Schema $schema)
{
    $this->addSql('
        ALTER TABLE
            contacts
        ADD COLUMN 
            fullname varchar(101) GENERATED ALWAYS AS (concat(first_name,' ',last_name));
    ');
}

With the above groundwork in place, you should be able to have doctrine use the schema builder to generate your DB schema as normal, and not have your generated column interfered when it is added via migrations.

Now the next problem is to ensure that your data can be hydrated on to your contacts entity without it trying to modify the result on the database during UPDATE and INSERT queries.

The trick is to create another entity which extends you current Contacts entity, and is used solely for SELECT and JOIN queries.

<?php

    namespace Embark\ApiBundle\Entity;

    use Doctrine\ORM\Mapping as ORM;

    /**
     * Generated values from the contacts table
     *
     * @ORM\Table(name="contacts")
     * @ORM\MappedSuperclass
     */
    class ContactGenerated extends Contact
    {
        /**
         *
         * @ORM\Column(name="fullname", type="string", nullable=true)
         */
        private $fullname;
    }

The @MappedSuperClass annotation prevents doctrines schema builder attempting to create a table with the same name.

You can then use DQL to get the data:

    $queryBuilder->select('contact')
        ->from(ContactGenerated::class, 'contact');

This will return you an array of ContactGenerated entities. You'll run in to issues if you try to persist them, you should really treat these as read only.

I'll leave it up to you to figure out how to convert these to standard Contact classes, which drop the non-existent "fullname" property which would allow you to conduct UPDATE queries from the select.

I really hope this helps you as it did me for my use case - any questions, feel free to ask :)

Answer 2

Mapping generated columns to Doctrine entities is possible, but it comes with some limitations:

• You can't persist a new entity, because Doctrine will try to insert into the generated column and it results in an error.
• Schema tool (orm:schema-tool:update --dump-sql) will always think you need to alter the column.

One possible workaround is to use triggers like in the old times:

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    fullname varchar(101) NOT NULL,
    email VARCHAR(100) NOT NULL
);

CREATE TRIGGER contacts_before_insert BEFORE INSERT ON contacts
FOR EACH ROW BEGIN
    SET NEW.fullname = (CONCAT(NEW.first_name, ' ', NEW.last_name));
END;

CREATE TRIGGER contacts_before_update BEFORE UPDATE ON contacts
FOR EACH ROW BEGIN
    SET NEW.fullname = (CONCAT(NEW.first_name, ' ', NEW.last_name));
END;

Other possible workaround is moving the updating logic into the entity, but then it won't reflect changes performed directly into the database.