Generated/Virtual Columns - How to define it

[acrsouza]

Hello, there.

I couldn't find the in the docs neither in Google the correct way to define generated columns in the mapping.

What I want to achieve is this:
ALTER TABLE events ADD country_code VARCHAR(2) GENERATED ALWAYS AS (content->>"$.country_code");

What I've tried in my XML mapping:

<field name="countryCode" column="country_code" length="2" column-definition='AS (content->>"$.country_code")' insertable="false" updatable="false" generated="ALWAYS"/>

This generates the following SQL:
ALTER TABLE events ADD country_code AS (content->>"$.country_code");

As you can see, it's missing the type of the field and the GENERATE ALWAYS statement.

Can someone tell me the correct way to define this column, please?

I will gladly update the documentation afterwards.

[acrsouza]

Based on this example I found in the Generated/Virtual Columns PR

#[Column(type: "datetime", 
        columnDefinition: "TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", 
        insertable: false,
        updatable: false,
        generated: "ALWAYS")]
    public $created;

I implemented the following:

        <field name="countryCode" column="country_code" column-definition="VARCHAR(2) GENERATED ALWAYS AS (content->>&quot;$.country_code&quot;)" insertable="false" updatable="false"/>

Seems to be working fine, but I'm not sure if it is the right way to implement it.
It would be nice if someone could validate it.
Besides that, I didn't understand exactly how generated: "ALWAYS" works, so I didn't include it.

[jimbo2150]

I would also like to know if this is the suggested answer to adding a generated virtual column using an expression.

[collmomo]

how come there's no documentation on the official reference for this use case, per chatGPT documentation...:
Explanation:

type: 'boolean': Specifies that the column is of boolean type.
nullable: false: Indicates the column cannot be NULL.
insertable: false, updatable: false: Tells Doctrine not to include this field in INSERT or UPDATE statements, as its value is managed by the database.
columnDefinition: Provides the exact SQL definition of the column. This allows Doctrine to generate the correct SQL when creating or updating the schema.
generated: 'ALWAYS': Specifies that the column is generated by the database on both INSERT and UPDATE operations.
options: ['computed' => true]: Commented out because it's not necessary in this context.