A curated list of useful Doctrine snippets.
Contributions are highly encouraged and very welcome :)
$em = $this->getEntityManager(); $query = $em->createQuery('SELECT c FROM SomeBundle:Configuration c INDEX BY c.name'); $query->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);SELECT partial b.{id, title} FROM Book b$stmt = $this->getDoctrine()->getEntityManager() ->getConnection() ->prepare('SELECT t.id, t.name FROM table t WHERE t.id IN (:ids)'); $stmt->bindValue('ids', array(1, 2, 3, 4, 5, 6), \Doctrine\DBAL\Connection::PARAM_INT_ARRAY); $stmt->execute();or
$stmt = $this->getDoctrine()->getEntityManager() ->getConnection() ->executeQuery('SELECT t.id, t.name FROM table t WHERE t.id IN (:ids)', array('ids' => array(1, 2, 3, 4, 5, 6)), array('ids' => \Doctrine\DBAL\Connection::PARAM_INT_ARRAY) ) ;$qb = $em->createQueryBuilder(); $qb->select('u') ->from('SomeUserBundle:User', 'u', 'u.id') ->add('where', $qb->expr()->like('u.roles', ':role')) ->setParameter('role', $role);$query->getOneOrNullResult();- no result: return
null - more than one result: throw an
NonUniqueResultExceptionexception
SELECT m, (m.comments + m.likes_count) AS HIDDEN score FROM Midia m ORDER BY score$query = $entityManager->createQuery('SELECT COUNT(u.id) FROM User u'); $count = $query->getSingleScalarResult();$q = $rep->createQueryBuilder('t') ->where('IDENTITY(t.user) = :userId') ->orderBy('t.id', 'DESC') ->setParameter('userId', $id) ->getQuery();or
SELECT p FROM Product p WHERE IDENTITY(p.shop) = :shopIdDoctrine 2.4
$categories = ... $categoryIds = array(); foreach ($categories as $category) { $categoryIds[] = $category->getId(); } $queryBuilder = $this ->where('model.category IN (:category_ids)') ->setParameter('category_ids', $categoryIds);Doctrine 2.5+ supports ArrayCollection
$queryBuilder = $this ->where('model.category IN (:categories)') ->setParameter('categories', $categories);$em = $this->getDoctrine()->getManager(); $repo = $em->getRepository('AppBundle:User'); $active = true; $qb = $repo->createQueryBuilder('u'); $qb->update() ->set('u.active', ':userActive') ->setParameter('userActive', $active); $qb->getQuery()->execute();If you have a very large UnitOfWork but know that a large set of entities has not changed, just mark them as read only.
$entityManager->getUnitOfWork()->markReadOnly($entity)$count = $conn->executeUpdate('UPDATE user SET username = ? WHERE id = ?', array('andreia', 1)); echo $count; // 1$sql = "SELECT * FROM site WHERE id = ?"; $stmt = $conn->prepare($sql); $stmt->bindValue(1, $id); $stmt->execute(); $sites = $stmt->fetchAll();$sql = "SELECT * FROM user WHERE name = :name"; $stmt = $conn->prepare($sql); $stmt->bindValue("name", $name); $stmt->execute(); $users = $stmt->fetchArray();use Doctrine\DBAL\Connection; class SomeClass { private $conn; // ... public function __construct(Connection $conn) { $this->conn = $conn; } // ... function updateDatabase() { // ... try { $this->conn->beginTransaction(); $this->conn->setAutoCommit(false); $this->conn->executeUpdate('INSERT INTO table1 (field1, field2, field3) VALUES(?, ?, ?)', [$field1, $field2, $field3]); $this->conn->executeUpdate('INSERT INTO table2 (field1, field2) VALUES(?, ?)', [$field1, $field2]); $this->conn->commit(); } catch (\Exception $e) { // ... $this->conn->rollback(); } } // ... }$platform = $this->conn->getDatabasePlatform(); $this->conn->executeQuery('SET FOREIGN_KEY_CHECKS = 0;'); $truncateSql = $platform->getTruncateTableSQL('table_name'); $this->conn->executeUpdate($truncateSql); $this->conn->executeQuery('SET FOREIGN_KEY_CHECKS = 1;');