Is there possible to use createQueryBuilder for insert/update? If not, what function should I use?

Question

For now I succeded to create a function that retrieves data from the database using Doctrine's function createQueryBuilder.

Does anybody know if there is a similar function to insert or update the database? Or how can i use createQueryBuilder?

Answer 1

Doctrine 2 ORM does not support INSERT via DQL or the DQL query builder. For a complete syntax, check the EBNF of DQL.

To handle inserts in ORM, you always manually instantiate an entity and persist it with the entity manager:

$user = new \My\Entity\User();

$entityManager->persist($user);
$entityManager->flush();

You can only handle SELECT, UPDATE and DELETE via DQL in Doctrine ORM:

• Select:

  SELECT u FROM My\Entity\User u WHERE u.id = :userId
  

• Update:

  UPDATE My\Entity\User u SET u.status = 'banned' WHERE u.id = :userId
  

• Delete

  DELETE My\Entity\User u WHERE u.id = :userId
  

You can handle these operations with the QueryBuilder as well:

• Select:

    $queryBuilder = $entityManager->createQueryBuilder();
    $queryBuilder
        ->select('u')
        ->from('My\Entity\User', 'u')
        ->where($queryBuilder->expr()->eq('u.id', ':userId'));

• Delete:

    $queryBuilder = $entityManager->createQueryBuilder();
    $queryBuilder
        ->delete('My\Entity\User', 'u')
        ->where($queryBuilder->expr()->eq('u.id', ':userId'));

• Update:

    $queryBuilder = $entityManager->createQueryBuilder();
    $queryBuilder
        ->update('My\Entity\User', 'u')
        ->set('u.status', 'banned')
        ->where($queryBuilder->expr()->eq('u.id', ':userId'));

Answer 2

Another option you have instead using a QueryBuilder, is using Doctrine DBAL prepare and execute functions. Probably is not as flexible as use QueryBuilder, but for do INSERTs in some situations could be useful.

The way to use is getting the Database Connection from the Entity Manager.

$sql = "INSERT INTO table (field1, field2) VALUES ('foo', 'var')";
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue(':invoice', $invoiceId);
$result = $stmt->execute();

Answer 3

If you use DBAL queryBuilder, it is possible to insert.

$qb = $connection->createQueryBuilder();

To insert with the queryBuilder it is like this :

$qb->insert('MuBundle:MyClass', 'momc')
   ->values (array(
       'property1 (id for example)' => '?'
       'property2 (name for exmaple)' => '?'
   ))
   ->setParameter(0, $id)
   ->setparameter(1, $name)