CODEWITHSUNDEEP
phpmysqldoctrine-ormsymfony
Bendy
Bendy

Reputation: 3576

Can Doctrine define MySQL's Generated columns?

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;

Upvotes: 10

Views: 4105

Answers (5)

Jim
Jim

Reputation: 3579

As of January 2022 with the release of ORM 2.11, Doctrine now supports generated columns via annotations or attributes. Note that as of version 3, docblock annotations are no longer supported; You should use attributes instead. The generated parameter was also added that allows you to indicate when to fetch the column.

Attribute:

#[ORM\Column(
    type: "text",
    columnDefinition: "AS (concat(first_name,' ',last_name))",
    insertable: false,
    updatable: false,
    generated: "ALWAYS"
)]
private $fullname;

DocBlock annotation (no longer supported since version 3):

/**
 * @ORM\Column(type="text", insertable=false, updatable=false,generated="ALWAYS",columnDefinition="AS (concat(first_name,' ',last_name))")
 */
private $fullname;

This allows you to use the column while telling Doctrine that the column cannot have a value assigned to it. It will not attempt to persist (insert/update) the column to the database.

As a note, the Generated() annotation/attribute is not for generated columns other than IDs. It is to specify a strategy to assign values to an auto-updated primary key. For instance, using a UUID instead of an incrementing number. It changes the way create/update table queries are formed when generating or updating tables and may persist a value to the column, depending on the strategy.

Upvotes: 5

dnlwtsn
dnlwtsn

Reputation: 210

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 :)

Upvotes: 6

dbu
dbu

Reputation: 1562

When you also need to write entities, there is a workaround to add the Generated Column in SQL but completely hide it from Doctrine ORM with a Schema Listener. You can then only use that column in Doctrine DBAL queries, not with the QueryBuilder/DQL. I explained how it works in this blog post: https://www.liip.ch/en/blog/doctrine-and-generated-columns

Upvotes: 2

VaclavSir
VaclavSir

Reputation: 625

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.

Upvotes: 2

Dmitry Malyshenko
Dmitry Malyshenko

Reputation: 3051

I believe it's just 

/**
 * @ORM\Column(name="fullname", type="text")
 */
private $fullname;

The data will be read just as from a usual column. But you should not allow to edit this field (make no setter).

Upvotes: 0

Related Questions