Awesome Doctrine

A curated list of useful Doctrine snippets.

Contributions are highly encouraged and very welcome :)

Table of Contents

• DQL
  • Defining a Column to be the Key of the Result Hydrated as Array
  • Fetch Only Parts of an Entity
  • IN Clause in Raw SQL
  • INDEX BY in QueryBuilder
  • Get Single Row or Null
  • Ordering with Expressions
  • Return Only a Value
  • Select Directly by Foreign Key Without Join the Foreign Table
  • WHERE IN Clause
• Performance
  • Bulk Update Using 'update' Statement Instead of Iterating Through Entities - Object Persisting
  • Temporarily Mark Entities as Read-Only at Runtime
• Raw SQL - DBAL
  • Update, Insert
  • Query Data
    • Select with Parameters
    • Select with Named Parameters
  • Transaction
  • Truncate Table

DQL

Defining a Column to be the Key of the Result Hydrated as Array

$em = $this->getEntityManager();

$query = $em->createQuery('SELECT c FROM SomeBundle:Configuration c INDEX BY c.name');
$query->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

[1]

Fetch Only Parts of an Entity

SELECT partial b.{id, title} FROM Book b

IN Clause in Raw SQL

$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)
    )
;

[1]

INDEX BY in QueryBuilder

$qb = $em->createQueryBuilder();

$qb->select('u')
   ->from('SomeUserBundle:User', 'u', 'u.id')
   ->add('where', $qb->expr()->like('u.roles', ':role'))
   ->setParameter('role', $role);

Get Single Row or Null

$query->getOneOrNullResult();

• no result: return null
• more than one result: throw an NonUniqueResultException exception

[1] [2]

Ordering with Expressions

SELECT m, (m.comments + m.likes_count) AS HIDDEN score FROM Midia m ORDER BY score

Return Only a Value

$query = $entityManager->createQuery('SELECT COUNT(u.id) FROM User u');
$count = $query->getSingleScalarResult();

Select Directly by Foreign Key Without Join the Foreign Table

$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) = :shopId

WHERE IN Clause

Doctrine 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);

Performance

Bulk Update Using 'update' Statement Instead of Iterating Through Entities - Object Persisting

$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();

Temporarily Mark Entities as Read-Only at Runtime

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)

Raw SQL - DBAL

Update, Insert

$count = $conn->executeUpdate('UPDATE user SET username = ? WHERE id = ?', array('andreia', 1));
echo $count; // 1

Query Data

Select with Parameters

$sql = "SELECT * FROM site WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bindValue(1, $id);
$stmt->execute();
$sites = $stmt->fetchAll();

Select with Named Parameters

$sql = "SELECT * FROM user WHERE name = :name";
$stmt = $conn->prepare($sql);
$stmt->bindValue("name", $name);
$stmt->execute();
$users = $stmt->fetchArray();

Transaction

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();
        }
    }

    // ...
}

Truncate Table

$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;');

[1]