Doctrine - mapping to a non entity class

Question

Is there a possibility to map doctrine's native query to a class that is not annotated as ORM entity? I have a basic class under App\Model

class BasicModel
{
    private int $weight;
    private int $points;
}

And my goal is to map the result of a query directly into this non entity class.

$rsm = new ResultSetMapping();
$rsm->addEntityResult(BasicModel::class, 'b');
$rsm->addFieldResult('b', 'points', 'points');
$rsm->addFieldResult('b', 'weight', 'weight');
$query = $this->em->createNativeQuery('select points, weight from some_table', $rsm);
$result = $query->getResult();

Currently, an error occurs

The class 'App\Model\BasicModel' was not found in the chain configured namespaces App\Entity

I don't want to annotate this class as Entity, since I don't need this information to be stored in database.

My current stack:

• Symfony 5
• MySQL 8
• Doctrine 2

Answer 1

The question is essentially how do you work with non-Doctrine mapped database tables in Doctrine. For anyone who has worked with legacy data knows, the simple answers of (1) you can't do that and (2) convert all the other tables to Doctrine Entities is NOT GOOD ENOUGH. This is because (1) The inability to use Doctrine to manage newly added tables will impact programmer productivity and (2) converting all the existing tables into Doctrine mapped entities is a potentially huge undertaking and is simply not worth the effort.

Doctrine has an import command doctrine:mapping:import, but this command is no longer supported it seems. It is still in Symfony 6 because (I think) it still has some application in some very narrow use cases. Do not use the import! Will lead to tears.

Much better when adding capability to legacy data to add new tables as Doctrine entities and let nature take its course with all the other tables.

To get the Doctrine migration stuff to work you need to adopt a table naming scheme such that Doctrine can recognize the Doctrine mapped tables. You do this in doctrine.yaml like so...

doctrine:
    dbal:
        schema_filter: ~^((?=en_)|doctrine_migration_versions)~
        url: '%env(resolve:DATABASE_URL)%'

The schema filter, in this case ignores all tables except for those tables that start with "en_". Also it recognizes the special table "doctrine_migration_versions" as a Doctrine table.

If you add this schema filter, doctrine migration operations such as...

symfony console make:migration

...will work properly.

Now the hard part is how to connect your new Doctrine Entity tables to legacy tables. You cannot use any of the Doctrine mapping attributes such as OneToMany and ManyToOne and so on because these all require a Doctrine Entity as the other table. What you need to do is simply add the raw key value into the Doctrine entity. So for example If your new en_customer_email table needs to reference the legacy customer table whose primary key is an int, then add this to your Doctrine Entity...

#[ORM\Column(type: 'integer')]
private $customerId;

With accessors...

public function getCustomerId(): ?int
{
    return $this->customerId;
}

public function setCustomerId(int $customerId): self
{
    $this->customerId = $customerId;

    return $this;
}

To make this useable we need some way to retrieve the email records based on customer id, so we add code to the repository for the new entity...

public function getCustomerEmails(int $customerId) : array
{
    return $this->findBy(["customerId" => $customerId]);
}

I find the Repository classes for the new entities to be the right place to put these legacy support functions. Going the other way, given an email record, to get the customer record you need to get the key and then use the legacy machinery to get that. Not ideal, but the best you can do.

We are not done yet! All this will work but will have issues. The first is performance. We need an index for this new column. This can be easily done using Doctrine as documented in this answer: https://stackoverflow.com/a/73805858/7422838

The second issue is orphaned email records when the customer record is deleted. Here we need a foreign key constraint and there is no clean way to do this.

My approach is the following:

1. After having created the table using a migration, add the foreign key constraint manually to the database table.
2. Then create a migration (symfony console make:migration). You will see in the up() method the foreign key being deleted and in the down() method it being recreated again.
3. Exchange the code in the up() and down() methods.
4. Delete the manually created foreign key constraint.
5. Run the new migration to create the foreign key constraint.

Any there you have it! The Doctrine created Entity has been coupled to a legacy database table. Note the following:

1. I am creating only the most basic relationships between Doctrine and legacy tables. Anything more complex and I consider converting the legacy table into a Doctrine Entity.
2. Once you add these essentially manual relationships, any further creation of migrations will want to delete those manual relationships. So you will need to carefully prune these SQL entries from future migrations.
3. I am not considering how to add references to Doctrine entities to legacy tables. I want to avoid that at all costs! Legacy is legacy. If I need to create a relationship to a legacy table and adding a new column with the table primary key will not work, I simply add a new Doctrine table to manage the association.

Finally I am using Symfony 6, MySQL 8, Doctrine 3.

Answer 2

Unfortunately that is not possible. Doctrine is build as a Database Abstraction Layer (DBAL) and does not allow for this use case.

I see two paths forward. I know you say you do not want that, but since you have MySQL in your stack, you could opt to add the data anyway to the database and have the entity as a proper entity class. The other option is to not use Doctrine and build a different implementation to list all entities.