Databases and the Doctrine ORM
Screencast
Do you prefer video tutorials? Check out the Doctrine screencast series.
Symfony provides all the tools you need to use databases in your applications thanks to Doctrine, the best set of PHP libraries to work with databases. These tools support relational databases like MySQL and PostgreSQL and also NoSQL databases like MongoDB.
Databases are a broad topic, so the documentation is divided in three articles:
- This article explains the recommended way to work with relational databases in Symfony applications;
- Read this other article if you need low-level access to perform raw SQL queries to relational databases (similar to PHP's PDO);
- Read DoctrineMongoDBBundle docs if you are working with MongoDB databases.
Installing Doctrine
First, install Doctrine support via the orm
Symfony pack,
as well as the MakerBundle, which will help generate some code:
1 2
$ composer require symfony/orm-pack
$ composer require --dev symfony/maker-bundle
Configuring the Database
The database connection information is stored as an environment variable called
DATABASE_URL
. For development, you can find and customize this inside .env
:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
# .env (or override DATABASE_URL in .env.local to avoid committing your changes)
# customize this line!
DATABASE_URL="mysql://db_user:db_password@127.0.0.1:3306/db_name?serverVersion=8.0.37"
# to use mariadb:
# Before doctrine/dbal < 3.7
# DATABASE_URL="mysql://db_user:db_password@127.0.0.1:3306/db_name?serverVersion=mariadb-10.5.8"
# Since doctrine/dbal 3.7
# DATABASE_URL="mysql://db_user:db_password@127.0.0.1:3306/db_name?serverVersion=10.5.8-MariaDB"
# to use sqlite:
# DATABASE_URL="sqlite:///%kernel.project_dir%/var/app.db"
# to use postgresql:
# DATABASE_URL="postgresql://db_user:db_password@127.0.0.1:5432/db_name?serverVersion=12.19 (Debian 12.19-1.pgdg120+1)&charset=utf8"
# to use oracle:
# DATABASE_URL="oci8://db_user:db_password@127.0.0.1:1521/db_name"
Caution
If the username, password, host or database name contain any character considered
special in a URI (such as : / ? # [ ] @ ! $ & ' ( ) * + , ; =
),
you must encode them. See RFC 3986 for the full list of reserved characters.
You can use the urlencode function to encode them or
the urlencode environment variable processor.
In this case you need to remove the resolve:
prefix in config/packages/doctrine.yaml
to avoid errors: url: '%env(DATABASE_URL)%'
Now that your connection parameters are setup, Doctrine can create the db_name
database for you:
1
$ php bin/console doctrine:database:create
There are more options in config/packages/doctrine.yaml
that you can configure,
including your server_version
(e.g. 8.0.37 if you're using MySQL 8.0.37), which may
affect how Doctrine functions.
Tip
There are many other Doctrine commands. Run php bin/console list doctrine
to see a full list.
Creating an Entity Class
Suppose you're building an application where products need to be displayed.
Without even thinking about Doctrine or databases, you already know that
you need a Product
object to represent those products.
You can use the make:entity
command to create this class and any fields you
need. The command will ask you some questions - answer them like done below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
$ php bin/console make:entity
Class name of the entity to create or update:
> Product
New property name (press <return> to stop adding fields):
> name
Field type (enter ? to see all types) [string]:
> string
Field length [255]:
> 255
Can this field be null in the database (nullable) (yes/no) [no]:
> no
New property name (press <return> to stop adding fields):
> price
Field type (enter ? to see all types) [string]:
> integer
Can this field be null in the database (nullable) (yes/no) [no]:
> no
New property name (press <return> to stop adding fields):
>
(press enter again to finish)
Whoa! You now have a new src/Entity/Product.php
file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
// src/Entity/Product.php
namespace App\Entity;
use App\Repository\ProductRepository;
use Doctrine\ORM\Mapping as ORM;
#[ORM\Entity(repositoryClass: ProductRepository::class)]
class Product
{
#[ORM\Id]
#[ORM\GeneratedValue]
#[ORM\Column]
private ?int $id = null;
#[ORM\Column(length: 255)]
private ?string $name = null;
#[ORM\Column]
private ?int $price = null;
public function getId(): ?int
{
return $this->id;
}
// ... getter and setter methods
}
Tip
Starting in MakerBundle: v1.57.0 - You can pass either --with-uuid
or
--with-ulid
to make:entity
. Leveraging Symfony's Uid Component,
this generates an entity with the id
type as Uuid
or Ulid instead of int
.
Note
Starting in v1.44.0 - MakerBundle: only supports entities using PHP attributes.
Note
Confused why the price is an integer? Don't worry: this is just an example. But, storing prices as integers (e.g. 100 = $1 USD) can avoid rounding issues.
Note
If you are using an SQLite database, you'll see the following error:
PDOException: SQLSTATE[HY000]: General error: 1 Cannot add a NOT NULL
column with default value NULL. Add a nullable=true
option to the
description
property to fix the problem.
Caution
There is a limit of 767 bytes for the index key prefix when using
InnoDB tables in MySQL 5.6 and earlier versions. String columns with 255
character length and utf8mb4
encoding surpass that limit. This means
that any column of type string
and unique=true
must set its
maximum length
to 190
. Otherwise, you'll see this error:
"[PDOException] SQLSTATE[42000]: Syntax error or access violation:
1071 Specified key was too long; max key length is 767 bytes".
This class is called an "entity". And soon, you'll be able to save and query Product
objects to a product
table in your database. Each property in the Product
entity can be mapped to a column in that table. This is usually done with attributes:
the #[ORM\Column(...)]
comments that you see above each property:
The make:entity
command is a tool to make life easier. But this is your code:
add/remove fields, add/remove methods or update configuration.
Doctrine supports a wide variety of field types, each with their own options.
Check out the list of Doctrine mapping types in the Doctrine documentation.
If you want to use XML instead of attributes, add type: xml
and
dir: '%kernel.project_dir%/config/doctrine'
to the entity mappings in your
config/packages/doctrine.yaml
file.
Caution
Be careful not to use reserved SQL keywords as your table or column names
(e.g. GROUP
or USER
). See Doctrine's Reserved SQL keywords documentation
for details on how to escape these. Or, change the table name with
#[ORM\Table(name: 'groups')]
above the class or configure the column name with
the name: 'group_name'
option.
Migrations: Creating the Database Tables/Schema
The Product
class is fully-configured and ready to save to a product
table.
If you just defined this class, your database doesn't actually have the product
table yet. To add it, you can leverage the DoctrineMigrationsBundle, which is
already installed:
1
$ php bin/console make:migration
Tip
Starting in MakerBundle: v1.56.0 - Passing --formatted
to make:migration
generates a nice and tidy migration file.
If everything worked, you should see something like this:
1 2 3 4
SUCCESS!
Next: Review the new migration "migrations/Version20211116204726.php"
Then: Run the migration with php bin/console doctrine:migrations:migrate
If you open this file, it contains the SQL needed to update your database! To run that SQL, execute your migrations:
1
$ php bin/console doctrine:migrations:migrate
This command executes all migration files that have not already been run against your database. You should run this command on production when you deploy to keep your production database up-to-date.
Migrations & Adding more Fields
But what if you need to add a new field property to Product
, like a
description
? You can edit the class to add the new property. But, you can
also use make:entity
again:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
$ php bin/console make:entity
Class name of the entity to create or update
> Product
New property name (press <return> to stop adding fields):
> description
Field type (enter ? to see all types) [string]:
> text
Can this field be null in the database (nullable) (yes/no) [no]:
> no
New property name (press <return> to stop adding fields):
>
(press enter again to finish)
This adds the new description
property and getDescription()
and setDescription()
methods:
1 2 3 4 5 6 7 8 9 10 11 12 13
// src/Entity/Product.php
// ...
+ use Doctrine\DBAL\Types\Types;
class Product
{
// ...
+ #[ORM\Column(type: Types::TEXT)]
+ private string $description;
// getDescription() & setDescription() were also added
}
The new property is mapped, but it doesn't exist yet in the product
table. No
problem! Generate a new migration:
1
$ php bin/console make:migration
This time, the SQL in the generated file will look like this:
1
ALTER TABLE product ADD description LONGTEXT NOT NULL
The migration system is smart. It compares all of your entities with the current state of the database and generates the SQL needed to synchronize them! Like before, execute your migrations:
1
$ php bin/console doctrine:migrations:migrate
This will only execute the one new migration file, because DoctrineMigrationsBundle
knows that the first migration was already executed earlier. Behind the scenes, it
manages a migration_versions
table to track this.
Each time you make a change to your schema, run these two commands to generate the migration and then execute it. Be sure to commit the migration files and execute them when you deploy.
Tip
If you prefer to add new properties manually, the make:entity
command can
generate the getter & setter methods for you:
1
$ php bin/console make:entity --regenerate
If you make some changes and want to regenerate all getter/setter methods,
also pass --overwrite
.
Persisting Objects to the Database
It's time to save a Product
object to the database! Let's create a new controller
to experiment:
1
$ php bin/console make:controller ProductController
Inside the controller, you can create a new Product
object, set data on it,
and save it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
// src/Controller/ProductController.php
namespace App\Controller;
// ...
use App\Entity\Product;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Attribute\Route;
class ProductController extends AbstractController
{
#[Route('/product', name: 'create_product')]
public function createProduct(EntityManagerInterface $entityManager): Response
{
$product = new Product();
$product->setName('Keyboard');
$product->setPrice(1999);
$product->setDescription('Ergonomic and stylish!');
// tell Doctrine you want to (eventually) save the Product (no queries yet)
$entityManager->persist($product);
// actually executes the queries (i.e. the INSERT query)
$entityManager->flush();
return new Response('Saved new product with id '.$product->getId());
}
}
Try it out!
Congratulations! You just created your first row in the product
table. To prove it,
you can query the database directly:
1 2 3 4
$ php bin/console dbal:run-sql 'SELECT * FROM product'
# on Windows systems not using Powershell, run this command instead:
# php bin/console dbal:run-sql "SELECT * FROM product"
Take a look at the previous example in more detail:
- line 13 The
EntityManagerInterface $entityManager
argument tells Symfony to inject the Entity Manager service into the controller method. This object is responsible for saving objects to, and fetching objects from, the database. - lines 15-18 In this section, you instantiate and work with the
$product
object like any other normal PHP object. - line 21 The
persist($product)
call tells Doctrine to "manage" the$product
object. This does not cause a query to be made to the database. - line 24 When the
flush()
method is called, Doctrine looks through all of the objects that it's managing to see if they need to be persisted to the database. In this example, the$product
object's data doesn't exist in the database, so the entity manager executes anINSERT
query, creating a new row in theproduct
table.
Note
If the flush()
call fails, a Doctrine\ORM\ORMException
exception
is thrown. See Transactions and Concurrency.
Whether you're creating or updating objects, the workflow is always the same: Doctrine is smart enough to know if it should INSERT or UPDATE your entity.
Validating Objects
The Symfony validator can reuse Doctrine metadata to perform some basic validation tasks. First, add or configure the auto_mapping option to define which entities should be introspected by Symfony to add automatic validation constraints.
Consider the following controller code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
// src/Controller/ProductController.php
namespace App\Controller;
use App\Entity\Product;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Attribute\Route;
use Symfony\Component\Validator\Validator\ValidatorInterface;
// ...
class ProductController extends AbstractController
{
#[Route('/product', name: 'create_product')]
public function createProduct(ValidatorInterface $validator): Response
{
$product = new Product();
// ... update the product data somehow (e.g. with a form) ...
$errors = $validator->validate($product);
if (count($errors) > 0) {
return new Response((string) $errors, 400);
}
// ...
}
}
Although the Product
entity doesn't define any explicit
validation configuration, if the auto_mapping
option
includes it in the list of entities to introspect, Symfony will infer some
validation rules for it and will apply them.
For example, given that the name
property can't be null
in the database, a
NotNull constraint is added automatically
to the property (if it doesn't contain that constraint already).
The following table summarizes the mapping between Doctrine metadata and the corresponding validation constraints added automatically by Symfony:
Doctrine attribute | Validation constraint | Notes |
---|---|---|
nullable=false |
NotNull | Requires installing the PropertyInfo component |
type |
Type | Requires installing the PropertyInfo component |
unique=true |
UniqueEntity | |
length |
Length |
Because the Form component as well as API Platform internally use the Validator component, all your forms and web APIs will also automatically benefit from these automatic validation constraints.
This automatic validation is a nice feature to improve your productivity, but it doesn't replace the validation configuration entirely. You still need to add some validation constraints to ensure that data provided by the user is correct.
Fetching Objects from the Database
Fetching an object back out of the database is even easier. Suppose you want to
be able to go to /product/1
to see your new product:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
// src/Controller/ProductController.php
namespace App\Controller;
use App\Entity\Product;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Attribute\Route;
// ...
class ProductController extends AbstractController
{
#[Route('/product/{id}', name: 'product_show')]
public function show(EntityManagerInterface $entityManager, int $id): Response
{
$product = $entityManager->getRepository(Product::class)->find($id);
if (!$product) {
throw $this->createNotFoundException(
'No product found for id '.$id
);
}
return new Response('Check out this great product: '.$product->getName());
// or render a template
// in the template, print things with {{ product.name }}
// return $this->render('product/show.html.twig', ['product' => $product]);
}
}
Another possibility is to use the ProductRepository
using Symfony's autowiring
and injected by the dependency injection container:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
// src/Controller/ProductController.php
namespace App\Controller;
use App\Entity\Product;
use App\Repository\ProductRepository;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Attribute\Route;
// ...
class ProductController extends AbstractController
{
#[Route('/product/{id}', name: 'product_show')]
public function show(ProductRepository $productRepository, int $id): Response
{
$product = $productRepository
->find($id);
// ...
}
}
Try it out!
When you query for a particular type of object, you always use what's known as its "repository". You can think of a repository as a PHP class whose only job is to help you fetch entities of a certain class.
Once you have a repository object, you have many helper methods:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
$repository = $entityManager->getRepository(Product::class);
// look for a single Product by its primary key (usually "id")
$product = $repository->find($id);
// look for a single Product by name
$product = $repository->findOneBy(['name' => 'Keyboard']);
// or find by name and price
$product = $repository->findOneBy([
'name' => 'Keyboard',
'price' => 1999,
]);
// look for multiple Product objects matching the name, ordered by price
$products = $repository->findBy(
['name' => 'Keyboard'],
['price' => 'ASC']
);
// look for *all* Product objects
$products = $repository->findAll();
You can also add custom methods for more complex queries! More on that later in the Databases and the Doctrine ORM section.
Tip
When rendering an HTML page, the web debug toolbar at the bottom of the page will display the number of queries and the time it took to execute them:
![The web dev toolbar showing the Doctrine item.](/doc/7.1/_images/doctrine_web_debug_toolbar.png)
If the number of database queries is too high, the icon will turn yellow to
indicate that something may not be correct. Click on the icon to open the
Symfony Profiler and see the exact queries that were executed. If you don't
see the web debug toolbar, install the profiler
Symfony pack
by running this command: composer require --dev symfony/profiler-pack
.
For more information, read the Symfony profiler documentation.
Automatically Fetching Objects (EntityValueResolver)
2.7.1
Autowiring of the EntityValueResolver
was introduced in DoctrineBundle 2.7.1.
In many cases, you can use the EntityValueResolver
to do the query for you
automatically! You can simplify the controller to:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
// src/Controller/ProductController.php
namespace App\Controller;
use App\Entity\Product;
use App\Repository\ProductRepository;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Attribute\Route;
// ...
class ProductController extends AbstractController
{
#[Route('/product/{id}')]
public function show(Product $product): Response
{
// use the Product!
// ...
}
}
That's it! The bundle uses the {id}
from the route to query for the Product
by the id
column. If it's not found, a 404 page is generated.
Tip
When enabled globally, it's possible to disable the behavior on a specific
controller, by using the MapEntity
set to disabled
:
1 2 3 4 5 6 7 8
public function show(
#[CurrentUser]
#[MapEntity(disabled: true)]
User $user
): Response {
// User is not resolved by the EntityValueResolver
// ...
}
Fetch Automatically
If your route wildcards match properties on your entity, then the resolver will automatically fetch them:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
/**
* Fetch via primary key because {id} is in the route.
*/
#[Route('/product/{id}')]
public function showByPk(Product $product): Response
{
}
/**
* Perform a findOneBy() where the slug property matches {slug}.
*/
#[Route('/product/{slug}')]
public function showBySlug(Product $product): Response
{
}
Automatic fetching works in these situations:
- If
{id}
is in your route, then this is used to fetch by primary key via thefind()
method. - The resolver will attempt to do a
findOneBy()
fetch by using all of the wildcards in your route that are actually properties on your entity (non-properties are ignored).
This behavior is enabled by default on all controllers. If you prefer, you can
restrict this feature to only work on route wildcards called id
to look for
entities by primary key. To do so, set the option
doctrine.orm.controller_resolver.auto_mapping
to false
.
When auto_mapping
is disabled, you can configure the mapping explicitly for
any controller argument with the MapEntity
attribute. You can even control
the EntityValueResolver
behavior by using the MapEntity options :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
// src/Controller/ProductController.php
namespace App\Controller;
use App\Entity\Product;
use Symfony\Bridge\Doctrine\Attribute\MapEntity;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Attribute\Route;
// ...
class ProductController extends AbstractController
{
#[Route('/product/{slug}')]
public function show(
#[MapEntity(mapping: ['slug' => 'slug'])]
Product $product
): Response {
// use the Product!
// ...
}
}
Fetch via an Expression
If automatic fetching doesn't work for your use case, you can write an expression using the ExpressionLanguage component:
1 2 3 4 5 6
#[Route('/product/{product_id}')]
public function show(
#[MapEntity(expr: 'repository.find(product_id)')]
Product $product
): Response {
}
In the expression, the repository
variable will be your entity's
Repository class and any route wildcards - like {product_id}
are
available as variables.
The repository method called in the expression can also return a list of entities. In that case, update the type of your controller argument:
1 2 3 4 5 6
#[Route('/posts_by/{author_id}')]
public function authorPosts(
#[MapEntity(class: Post::class, expr: 'repository.findBy({"author": author_id}, {}, 10)')]
iterable $posts
): Response {
}
7.1
The mapping of the lists of entities was introduced in Symfony 7.1.
This can also be used to help resolve multiple arguments:
1 2 3 4 5 6 7
#[Route('/product/{id}/comments/{comment_id}')]
public function show(
Product $product,
#[MapEntity(expr: 'repository.find(comment_id)')]
Comment $comment
): Response {
}
In the example above, the $product
argument is handled automatically,
but $comment
is configured with the attribute since they cannot both follow
the default convention.
If you need to get other information from the request to query the database, you
can also access the request in your expression thanks to the request
variable. Let's say you want the first or the last comment of a product depending on a query parameter named sort
:
1 2 3 4 5 6 7
#[Route('/product/{id}/comments')]
public function show(
Product $product,
#[MapEntity(expr: 'repository.findOneBy({"product": id}, {"createdAt": request.query.get("sort", "DESC")})')]
Comment $comment
): Response {
}
MapEntity Options
A number of options are available on the MapEntity
attribute to
control behavior:
id
-
If an
id
option is configured and matches a route parameter, then the resolver will find by the primary key:1 2 3 4 5 6
#[Route('/product/{product_id}')] public function show( #[MapEntity(id: 'product_id')] Product $product ): Response { }
mapping
-
Configures the properties and values to use with the
findOneBy()
method: the key is the route placeholder name and the value is the Doctrine property name:1 2 3 4 5 6 7 8
#[Route('/product/{category}/{slug}/comments/{comment_slug}')] public function show( #[MapEntity(mapping: ['category' => 'category', 'slug' => 'slug'])] Product $product, #[MapEntity(mapping: ['comment_slug' => 'slug'])] Comment $comment ): Response { }
exclude
-
Configures the properties that should be used in the
findOneBy()
method by excluding one or more properties so that not all are used:1 2 3 4 5 6 7
#[Route('/product/{slug}/{date}')] public function show( #[MapEntity(exclude: ['date'])] Product $product, \DateTime $date ): Response { }
stripNull
-
If true, then when
findOneBy()
is used, any values that arenull
will not be used for the query. objectManager
-
By default, the
EntityValueResolver
uses the default object manager, but you can configure this:1 2 3 4 5 6
#[Route('/product/{id}')] public function show( #[MapEntity(objectManager: 'foo')] Product $product ): Response { }
evictCache
- If true, forces Doctrine to always fetch the entity from the database instead of cache.
disabled
-
If true, the
EntityValueResolver
will not try to replace the argument. message
-
An optional custom message displayed when there's a NotFoundHttpException, but only in the development environment (you won't see this message in production):
1 2 3 4 5 6
#[Route('/product/{product_id}')] public function show( #[MapEntity(id: 'product_id', message: 'The product does not exist')] Product $product ): Response { }
7.1
The message
option was introduced in Symfony 7.1.
Updating an Object
Once you've fetched an object from Doctrine, you interact with it the same as with any PHP model:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
// src/Controller/ProductController.php
namespace App\Controller;
use App\Entity\Product;
use App\Repository\ProductRepository;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Attribute\Route;
// ...
class ProductController extends AbstractController
{
#[Route('/product/edit/{id}', name: 'product_edit')]
public function update(EntityManagerInterface $entityManager, int $id): Response
{
$product = $entityManager->getRepository(Product::class)->find($id);
if (!$product) {
throw $this->createNotFoundException(
'No product found for id '.$id
);
}
$product->setName('New product name!');
$entityManager->flush();
return $this->redirectToRoute('product_show', [
'id' => $product->getId()
]);
}
}
Using Doctrine to edit an existing product consists of three steps:
- fetching the object from Doctrine;
- modifying the object;
- calling
flush()
on the entity manager.
You can call $entityManager->persist($product)
, but it isn't necessary:
Doctrine is already "watching" your object for changes.
Deleting an Object
Deleting an object is very similar, but requires a call to the remove()
method of the entity manager:
1 2
$entityManager->remove($product);
$entityManager->flush();
As you might expect, the remove()
method notifies Doctrine that you'd
like to remove the given object from the database. The DELETE
query isn't
actually executed until the flush()
method is called.
Querying for Objects: The Repository
You've already seen how the repository object allows you to run basic queries without any work:
1 2 3
// from inside a controller
$repository = $entityManager->getRepository(Product::class);
$product = $repository->find($id);
But what if you need a more complex query? When you generated your entity with
make:entity
, the command also generated a ProductRepository
class:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
// src/Repository/ProductRepository.php
namespace App\Repository;
use App\Entity\Product;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
class ProductRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Product::class);
}
}
When you fetch your repository (i.e. ->getRepository(Product::class)
), it is
actually an instance of this object! This is because of the repositoryClass
config that was generated at the top of your Product
entity class.
Suppose you want to query for all Product objects greater than a certain price. Add a new method for this to your repository:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
// src/Repository/ProductRepository.php
// ...
class ProductRepository extends ServiceEntityRepository
{
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Product::class);
}
/**
* @return Product[]
*/
public function findAllGreaterThanPrice(int $price): array
{
$entityManager = $this->getEntityManager();
$query = $entityManager->createQuery(
'SELECT p
FROM App\Entity\Product p
WHERE p.price > :price
ORDER BY p.price ASC'
)->setParameter('price', $price);
// returns an array of Product objects
return $query->getResult();
}
}
The string passed to createQuery()
might look like SQL, but it is
Doctrine Query Language. This allows you to type queries using commonly
known query language, but referencing PHP objects instead (i.e. in the FROM
statement).
Now, you can call this method on the repository:
1 2 3 4 5 6
// from inside a controller
$minPrice = 1000;
$products = $entityManager->getRepository(Product::class)->findAllGreaterThanPrice($minPrice);
// ...
See Service Container for how to inject the repository into any service.
Querying with the Query Builder
Doctrine also provides a Query Builder, an object-oriented way to write queries. It is recommended to use this when queries are built dynamically (i.e. based on PHP conditions):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
// src/Repository/ProductRepository.php
// ...
class ProductRepository extends ServiceEntityRepository
{
public function findAllGreaterThanPrice(int $price, bool $includeUnavailableProducts = false): array
{
// automatically knows to select Products
// the "p" is an alias you'll use in the rest of the query
$qb = $this->createQueryBuilder('p')
->where('p.price > :price')
->setParameter('price', $price)
->orderBy('p.price', 'ASC');
if (!$includeUnavailableProducts) {
$qb->andWhere('p.available = TRUE');
}
$query = $qb->getQuery();
return $query->execute();
// to get just one result:
// $product = $query->setMaxResults(1)->getOneOrNullResult();
}
}
Querying with SQL
In addition, you can query directly with SQL if you need to:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
// src/Repository/ProductRepository.php
// ...
class ProductRepository extends ServiceEntityRepository
{
public function findAllGreaterThanPrice(int $price): array
{
$conn = $this->getEntityManager()->getConnection();
$sql = '
SELECT * FROM product p
WHERE p.price > :price
ORDER BY p.price ASC
';
$resultSet = $conn->executeQuery($sql, ['price' => $price]);
// returns an array of arrays (i.e. a raw data set)
return $resultSet->fetchAllAssociative();
}
}
With SQL, you will get back raw data, not objects (unless you use the NativeQuery functionality).
Configuration
See the Doctrine config reference.
Relationships and Associations
Doctrine provides all the functionality you need to manage database relationships (also known as associations), including ManyToOne, OneToMany, OneToOne and ManyToMany relationships.
For info, see How to Work with Doctrine Associations / Relations.
Database Testing
Read the article about testing code that interacts with the database.
Doctrine Extensions (Timestampable, Translatable, etc.)
Doctrine community has created some extensions to implement common needs such as "set the value of the createdAt property automatically when creating an entity". Read more about the available Doctrine extensions and use the StofDoctrineExtensionsBundle to integrate them in your application.
Learn more
- How to Work with Doctrine Associations / Relations
- Doctrine Events
- How to Implement a Registration Form
- How to Register custom DQL Functions
- How to Use Doctrine DBAL
- How to Work with Multiple Entity Managers and Connections
- How to Define Relationships with Abstract Classes and Interfaces
- How to Generate Entities from an Existing Database
- How to Test a Doctrine Repository