What is DQL?

DQL stands for Doctrine Query Language, and it is:

• An object-oriented query language.
• Used to query PHP entity objects and their relationships.
• Similar to JPQL (Java Persistence Query Language) and somewhat inspired by SQL, but works at the object level (entities), not directly with table names or columns.

Why Use DQL?

• To fetch, filter, and join entities using their PHP class names and field names.
• It abstracts away the database schema and lets you focus on the object model.
• It allows complex queries (joins, group by, etc.) while staying in the ORM world.

Basic SELECT Query

Raw SQL:

SELECT * FROM users;

DQL:

$query = $entityManager->createQuery('SELECT u FROM Application\Entity\User u');
$result = $query->getResult();

SELECT with WHERE Clause

Raw SQL:

SELECT * FROM users WHERE id > 0;

DQL:

$query = $entityManager->createQuery('SELECT u FROM Application\Entity\User u WHERE u.id > 0');
$result = $query->getResult();

With Parameter:

$query = $entityManager->createQuery('SELECT u FROM Application\Entity\User u WHERE u.id = :id');
$query->setParameter('id', 1);
$result = $query->getResult();

SELECT with JOIN (INNER JOIN)

Raw SQL:

SELECT u.*, p.* FROM users u INNER JOIN profiles p ON u.id = p.user_id WHERE u.id > 0;

DQL:

$query = $entityManager->createQuery(
 'SELECT u, p
  FROM Application\Entity\User u
  JOIN u.profile p
  WHERE u.id > :id'
);
$query->setParameter('id', 0);
$result = $query->getResult();

AND Condition

Raw SQL:

SELECT * FROM users WHERE status = 'active' AND age > 18;

DQL:

$query = $entityManager->createQuery(
 'SELECT u FROM Application\Entity\User u WHERE u.status = :status AND u.age > :age'
);
$query->setParameters([
 'status' => 'active',
 'age' => 18
]);
$result = $query->getResult();

NOT Condition

Raw SQL:

SELECT * FROM users WHERE NOT active;

DQL:

$query = $entityManager->createQuery('SELECT u FROM Application\Entity\User u WHERE NOT u.active = true');
$result = $query->getResult();

ORDER BY

Raw SQL:

SELECT * FROM users ORDER BY created_at DESC;

DQL:

$query = $entityManager->createQuery('SELECT u FROM Application\Entity\User u ORDER BY u.createdAt DESC');
$result = $query->getResult();

LIMIT and OFFSET (via Query API)

Raw SQL:

SELECT * FROM users LIMIT 10 OFFSET 20;

DQL:

$query = $entityManager->createQuery('SELECT u FROM Application\Entity\User u');
$query->setFirstResult(20)->setMaxResults(10);
$result = $query->getResult();

COUNT, SUM, AVG, MIN, MAX

Raw SQL:

SELECT COUNT(*) FROM users;

DQL:

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

(Similarly for SUM, AVG, MIN, MAX)

LIKE

Raw SQL:

SELECT * FROM users WHERE name LIKE '%john%';

DQL:

$query = $entityManager->createQuery(
    'SELECT u FROM Application\Entity\User u WHERE u.name LIKE :name'
)->setParameter('name', '%john%');
$result = $query->getResult();

BETWEEN

Raw SQL:

SELECT * FROM users WHERE age BETWEEN 20 AND 30;

DQL:

$query = $entityManager->createQuery(
    'SELECT u FROM Application\Entity\User u WHERE u.age BETWEEN :min AND :max'
)->setParameters(['min' => 20, 'max' => 30]);
$result = $query->getResult();

IN

Raw SQL:

SELECT * FROM users WHERE id IN (1,2,3);

DQL:

$query = $entityManager->createQuery(
    'SELECT u FROM Application\Entity\User u WHERE u.id IN (:ids)'
)->setParameter('ids', [1,2,3]);
$result = $query->getResult();

Aliases

Raw SQL:

SELECT u.id AS user_id FROM users u;

DQL:

$query = $entityManager->createQuery(
    'SELECT u.id AS user_id FROM App\Entity\User u'
);
$result = $query->getResult();

INNER JOIN

Raw SQL:

SELECT * FROM users u INNER JOIN profiles p ON u.id = p.user_id;

DQL:

$query = $entityManager->createQuery(
    'SELECT u, p FROM Application\Entity\User u JOIN u.profile p'
);
$result = $query->getResult();

LEFT JOIN

Raw SQL:

SELECT * FROM users u LEFT JOIN profiles p ON u.id = p.user_id;

DQL:

$query = $entityManager->createQuery(
    'SELECT u, p FROM Application\Entity\User u LEFT JOIN u.profile p'
);
$result = $query->getResult();

SELF JOIN

Raw SQL:

SELECT * FROM users u1 JOIN users u2 ON u1.manager_id = u2.id;

DQL:

$query = $entityManager->createQuery(
    'SELECT u1, u2 FROM Application\Entity\User u1 JOIN Application\Entity\User u2 WITH u1.manager = u2'
);
$result = $query->getResult();

GROUP BY

Raw SQL:

SELECT status, COUNT(id) FROM users GROUP BY status;

DQL:

$query = $entityManager->createQuery(
    'SELECT u.status, COUNT(u.id) FROM Application\Entity\User u GROUP BY u.status'
);
$result = $query->getResult();

HAVING

Raw SQL:

SELECT status, COUNT(id) as total FROM users GROUP BY status HAVING total > 1;

DQL:

$query = $entityManager->createQuery(
    'SELECT u.status, COUNT(u.id) as total FROM Application\Entity\User u GROUP BY u.status HAVING total > 1'
);
$result = $query->getResult();

EXISTS

Raw SQL:

SELECT * FROM users u WHERE EXISTS (
    SELECT id FROM profiles p WHERE p.user_id = u.id
);

DQL:

$query = $entityManager->createQuery(
    'SELECT u FROM Application\Entity\User u WHERE EXISTS (
        SELECT p.id FROM Application\Entity\Profile p WHERE p.user = u
    )'
);
$result = $query->getResult();

NULL values

Raw SQL:

SELECT * FROM users WHERE email IS NULL;

DQL:

$query = $entityManager->createQuery(
    'SELECT u FROM Application\Entity\User u WHERE u.email IS NULL'
);
$result = $query->getResult();

CASE

Raw SQL:

SELECT name, CASE WHEN status = 1 THEN 'Active' ELSE 'Inactive' END FROM users;

DQL:

$query = $entityManager->createQuery(
    'SELECT u.name, CASE WHEN u.status = 1 THEN 'Active' ELSE 'Inactive' END FROM Application\Entity\User u'
);
$result = $query->getResult();