Doctrine Query Language
UP Web
AU: 2024/2025
Plan
• Introduction
• DQL Basics
• DQL Functions
2
Introduction
• Doctrine Query Language (DQL) is a powerful aspect specific to Doctrine ORM
• DQL allows you to interact with your database in a more flexible and powerful
way than using raw SQL queries.
• DQL is a query language for the object model, not the relational schema.
3
Introduction
DQL is used for: Simplicity DQL allows you to write queries more intuitively by
using entities rather than tables.
Portability:
DQL queries are independent of the database type, making it easy to migrate to
another DBMS.
Security:
DQL uses named parameters to prevent SQL injections.
Productivity:
Seamless integration with Doctrine entities, facilitating rapid development.
4
Introduction
• DQL as a query language allows the following statements: SELECT, UPDATE, and
DELETE.
• INSERT statements are not allowed in DQL, because entities and their relationships
must be introduced into the persistence context via EntityManager#persist() to
ensure the consistency of your object model.
• DQL SELECT statements are a very powerful way to retrieve parts of the model that
are not accessible via associations.
• DQL UPDATE and DELETE statements provide a way to perform bulk edits on
entities.
5
DQL Basics
Syntax of a DQL Query
SELECT alias FROM App\Entity\EntityName alias WHERE alias.property = :value
This query will be placed in the repository like the following example:
SELECT: Used to select entities or entity properties.
FROM: Indicates the entity from which the selection is made.
WHERE: Used to add conditions to the selection.
alias: Refers to the EntityName class
alias. property is a path expression. The path expression alias.property refers to the
property field of the EntityName class.
6
DQL Basics
To define and execute a DQL query, we must:
• Access the EntityManager: This is the main service to interact with the DB using
Doctrine.
• Create a DQL Query: createQuery to define your query.
• Define Parameters: setParameter to secure the query.
• Execute the Query: getResult or getSingleResult to get the results.
Syntax:
public function findByValue($value){
$query = $this->getEntityManager()
->createQuery(‘SELECT alias FROM App\Entity\EntityName alias WHERE
alias.property = :value’)->setParameter(value', $ value);
return $query->getResult();}
7
DQL Basics
Example:
Sélectionner tous les étudiants par ordre croissant de leurs ages
public function findStudentOrderByAge($age) {
// 1. Access the Entity Manager
$em = $this->getEntityManager();
// 2. Create the query with createQuery
$query= $em->createQuery('SELECT s FROM App\Entity\Student s WHERE s.age
> :age ORDER BY s.age ASC’);
// 3. Define Params
$query->setParameter(age’, $age);
// 4. Execute the query
$results = $query->getResult(); return $results; }
8
DQL Basics
There are 2 places to create and execute a DQL query.
In the repository file Directly in the controller
Example of a query in the controller
public function findStudentsOrderByAge(ManagerRegistry $manager){
$em=$manager->getManager();
$query= $em->createQuery('SELECT s FROM App\Entity\Student s
WHERE s.age > :age ORDER BY p.age ASC’)->getResult();
$query->setParameter(‘age’, $age);
$students= $query->getResult();
return $this->render(‘student/list.html.twig’,[‘students’=>$students]);
9
DQL Basics
Example of a query in the Repository
Retrieve all students whose emails end with ‘@esprit.tn’:
StudentRepository.php
public function ListStudent(StudentRepository $repo){
$students= $repo->findAllDql();
StudentController.php return $this->render(‘student/list.html.twig’,
[‘students’=>$students]);
}
10
DQL Basics
SELECT with join
• Exemple: Get student list by class
NB:
classroom attribute contained in the Student entity and which joins the
Student entity to the Classroom entity (ManyToOne relationship)
11
DQL Basics
• There are 2 types of parameters: positional parameters and named
parameters
• Positional parameters are specified with numbers, for example ? 1, ? 2
and so on.
$request=$em->createQuery(‘ SELETCT s FROM App/Entity/Student s WHERE u.age=?
1’);
$request->setParameter(1, 18)
return $request->getResult();
• Named parameters are specified with :name1, :name2 and so on.
$request=$em->createQuery (‘ SELETCT s FROM App/Entity/Student s WHERE
u.username= :name’);
$request->setParameter(‘name’,’Ahmed’)
return $request->getResult();
12
DQL basics
Update request
• DQL not only allows you to select your entities using field names, but you can
also perform bulk updates on a set of entities using a DQL-UPDATE query.
$request=$em->createQuery(
‘ UPDATE App/Entity/Student s SET s.age =20 WHERE s.id IN (1, 2, 3)’);
$students=$request->getResult();
13
Les bases de DQL
Delete:
• DQL also allows you to delete a set of entities using a DELETE query. :
$request=$em->createQuery(‘ DELETE App/Entity/Student s WHERE s.id=1 ’);
$students=$request->getResult();
• DQL DELETE statements are carried directly into a SQL DELETE statement.
Therefore, some limitations apply:
• Lifecycle events of the affected entities are not executed.
• A cascade delete operation is not performed for related entities.
• You can rely on cascade operations at the database level by configuring each join
column with the onDelete option.
14
DQL Basics
There are several DQL functions:
• Aggregation functions (COUNT, AVG, SUM, MAX, MIN)
SELECT COUNT(u.id) FROM App\Entity\Student s
•String Functions (CONCAT, SUBSTRING)
SELECT CONCAT(u.firstName, ' ', u.lastName) FROM App\Entity\User u
•Date Functions (CURRENT_DATE, CURRENT_TIME)
SELECT CURRENT_DATE() FROM App\Entity\Order o
15
DQL Basics
Example: count student number
StudentRepository.php
statistics.html.twig
StudentController.php
Student number: <i>{{nbr}}</i>
public function countStudent(StudentRepository $repo){
$students= $repo->findAllDql();
return $this->render(‘student/statistique.html.twig’,
[‘nbr’=>$number]);
}
Student number: 4 16
References
https://www.doctrine-project.org/projects/doctrine-orm/en/2.19/tutorials/getting-sta
rted.html
17
Thanks for your attention
18
