In Symfony, how do you fill a new column with values related to existing column values?

Question

Let's say I have a database and a table called User, which is filled up all with "name", "age". Name is like "Peter David Smith" or "Adam Pitt". Inside the User entity, I want to create a new column.

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

/**
* @ORM\Column(type="integer")
*/
private $age

// THIS IS THE NEW COLUMN
/**
* @ORM\Column(type="string", length=255, nullable=true)
*/
private $monogram;

How do I tell Doctrine that the new column called "monogram" should be filled up with the first letters of the $name's words? So in the case of "Peter Daniel Smith" it should be "PDS", at "Adam Pitt" -> "AD". The database has a lot or records with "name" and "age" so the database is not empty.

I've tried this in the constructor, doesn't work:

public function __construct()
{
    // ...

    $words = explode(" ", $this->name);

    foreach ($words as $w){
       $this->monogram .= $w[0];
    }
}

After this I make the migration, run it, but the monogram column is null everywhere. Is there any way to fill the $monogram based on the existing database data on this level?

You could have made a custom function within the migration file to do this, looping through all current records and calculating each `$monogram`. When doing `php bin/console make:migration` it will create the migration file, you can add to the `up` method then run `php bin/console doctrine:migrations:migrate` to run it. Then for future insertions you can use `@ORM\HasLifecycleCallbacks` in your entity and a method `onPrePersist(){...}` setting the `$monogram` automatically on every insert. — Bossman, May 19 '22 at 16:23
1st question: So in the migration file I have to reproduce the same functionality I would do in the onPrePersist function, only with SQL? — PizzaPeet, May 20 '22 at 15:20
2nd question: How do I apply the migration file if I have made changes in that: - I suppose - I wrote the SQL that creates the initials (monograms)? @Bossman — PizzaPeet, May 20 '22 at 15:22
1) Yes correct. 2) You can create a blank migration class file with `doctrine:migrations:generate` [ref](https://symfony.com/bundles/DoctrineMigrationsBundle/current/index.html#usage). Then put the logic in to convert and insert etc.. — Bossman, May 20 '22 at 15:27
Thanks, one last question, in the migration file, how do I iterate through all the records with SQL and set the actual record's initial (monogram) to the record's name's? @Bossman — PizzaPeet, May 20 '22 at 15:30
I'm not at a computer right now but will try and write up an answer tomorrow for you if no one else answers.. Have a Google in the mean time regarding the migration class. — Bossman, May 20 '22 at 16:20
[Here's](https://stackoverflow.com/questions/65357641/access-entity-manager-inside-symfony-5-migration-class) an example for you to work with — Bossman, May 20 '22 at 16:22

score 1 · Answer 1 · answered May 21 '22 at 14:42

First update current records via a migration file. You have already created the monogram field.

Create a new migration file with doctrine:migrations:generate. This will be located /migrations/VersionXXXXXXXXXXXXXX.php.

Edit your migration file, so it is like this:

declare(strict_types=1);

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
final class VersionXXXXXXXXXXXXXX extends AbstractMigration
{
    public function getDescription(): string
    {
        return '';
    }

    public function up(Schema $schema): void
    {
        foreach ($this->connection->fetchAll('SELECT id, name FROM user') as $user) {
            $this->addSql(
                'UPDATE user SET monogram = :monogram_field WHERE id = :user_id',
                array(
                    'monogram_field' => $this->generateInitials($user['name']),
                    'user_id' => $user['id']
                )
            );
        }
    }

    public function down(Schema $schema): void
    {
        // this down() migration is auto-generated, please modify it to your needs

    }

    private function generateInitials(?string $string): ?string
    {
        if (null === $string || empty($string)) return null;
        
        // Regex to match first letter of each word
        preg_match_all('/(?<=\b)[a-z]/i', $string, $matches);

        return strtoupper(implode('', $matches[0]));
    }
}

You can run doctrine:migrations:migrate or migrations:execute --up 'DoctrineMigrations\VersionXXXXXXXXXXXXXX' to run it.

Now for future insertions, update your User entity to include these functions:

/**
 * Gets triggered only on insert
 * @ORM\PrePersist
 */
public function onPrePersist()
{
    $this->monogram = $this->generateInitials($this->name);
}

private function generateInitials(?string $string): ?string
{
    if (null === $string || empty($string)) return null;

    // Regex to match first letter of each word
    preg_match_all('/(?<=\b)[a-z]/i', $string, $matches);

    return strtoupper(implode('', $matches[0]));
}

Remember to add @ORM\HasLifecycleCallbacks to the top of the class. When a new User is inserted it will populate the monogram field.

I haven't fully tested this. Test it locally first on dummy data and tweak as you see fit.

In Symfony, how do you fill a new column with values related to existing column values?

1 Answers1