16

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
user1482442
  • 567
  • 2
  • 7
  • 14
  • You, dude, need to take a depth look to Symfony2, Doctrine and so on .... – DonCallisto Mar 25 '13 at 15:56
  • Check the [official documentation](http://docs.doctrine-project.org/en/latest/) and the [Query Builder docs](http://docs.doctrine-project.org/en/latest/reference/query-builder.html). – Vadim Ashikhman Mar 25 '13 at 15:49

4 Answers4

33

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'));
Prisoner
  • 27,391
  • 11
  • 73
  • 102
Ocramius
  • 25,171
  • 7
  • 103
  • 107
9

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();
Javier Seixas
  • 319
  • 3
  • 11
  • 3
    there's a reason why the ORM does not allow this. The idea is to always go through the various listeners, the computed commit order and the DBAL types conversion. – Ocramius Mar 27 '13 at 03:38
  • 3
    @Ocramius there is also reasons why you need this, for example inserting logs without entering ORM's lifecycle callbacks – Erdal G. Jul 14 '20 at 14:15
9

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)
mlwacosmos
  • 4,391
  • 16
  • 66
  • 114
0

using QueryBuilder to insert data is not possible unless your are willing to write the DQL or SQL. If you are looking for a way to simply insert data to your database table, you have to first of all make sure the data is loaded into an Entity class for the table in which you want to insert your data. For example $em->persist($entity);

Rancantemos
  • 53
  • 1
  • 5