Doctrine ORM with PHP Symfony - part 2

When building web pages with PHP based on the Symfony framework, you can easily integrate with database tables using the EntityManagerInterface with Doctrine ORM (Object Relational Mapper) along with generated entity classes. Depending on the used table structures, as well as which SQL query that is to be executed, you may need to implement a workaround for this though.

Let's say, for simplicity, that you want to insert a new row in a table in which the structure is based on the following code, where a new primary key is auto incremented with every new insert:

CREATE TABLE Person (
    Id INT AUTO_INCREMENT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    Firstname VARCHAR(30),
    Lastname VARCHAR(30)
);

In a Symfony application with Doctrine ORM installed - along with a generated entity class with getters and setters corresponding the above database table and columns - you can easily add a new row to the table according to the following example code.

namespace App\Repository;

use App\Entity\Person;
use Doctrine\ORM\EntityManagerInterface;

class PersonRepository
{
    /**
     * Inject dependency to EntityManagerInterface.
     */
    public function createPerson(EntityManagerInterface $em)
    {
        $person = new Person();
        $person->setEmail("jane.doe@mail.com");
        $person->setFirstname("Jane");
        $person->setLastname("Doe");

        $em->persist($person);
        $em->flush();
    }
}

So far so good! But let's say that you want to insert a customized primary key for every new row, e.g. based on the following table structure:

CREATE TABLE Person (
    Email VARCHAR(100) PRIMARY KEY,
    Firstname VARCHAR(30),
    Lastname VARCHAR(30)
);

Then you have to find another way of handling this as the persist() and flush() methods tend to oppose you with these kind of inserts. And one way to cope with this while still using the EntityManagerInterface is to also use the Doctrine DBAL (DataBase Abstraction Layer):

namespace App\Repository;

use Doctrine\ORM\EntityManagerInterface;

class PersonRepository
{   
    public function createPerson(EntityManagerInterface $em)
    {
        $email = 'jane.doe@mail.com';
        $firstName = 'Jane';
        $lastName = 'Doe';

        // Prevent SQL injections by using placeholders ("?") in the query:
        $sql = 'INSERT INTO Person (Email, Firstname, Lastname) VALUES (?, ?, ?)';

        $em->getConnection()
            ->prepare($sql)
            ->execute(
                [
                    $email,
                    $firstName,
                    $lastName
                 ]
             );
     }
}

The above example deviates the ORM principle. However, when using INSERT queries in specific, it can be easiest to just use the above persistence instead when you want to add customized primary keys.