**Course Title:** Mastering Symfony: Building Enterprise-Level PHP Applications **Section Title:** Doctrine ORM and Database Integration **Topic:** Database queries using Doctrine’s QueryBuilder and repository pattern **Introduction** In the previous topics, we covered the basics of Doctrine ORM and database integration in Symfony. We learned how to create entities, define relationships, and generate database schemas. In this topic, we will dive deeper into database queries using Doctrine's QueryBuilder and repository pattern. **What is QueryBuilder?** QueryBuilder is a powerful tool provided by Doctrine that allows you to create complex database queries in a programmatic way. It provides a fluent interface for building queries, making it easy to write and maintain code. **Creating a QueryBuilder** To create a QueryBuilder, you need to inject the `Entity` Manager into your controller or service. The `EntityManager` provides a `getRepository()` method that returns a `Repository` object for a specific entity. ```php use App\Entity\User; use Doctrine\ORM\EntityManagerInterface; // Inject the EntityManager in your controller or service private $entityManager; public function __construct(EntityManagerInterface $entityManager) { $this->entityManager = $entityManager; } // Get the repository for the User entity $userRepository = $this->entityManager->getRepository(User::class); ``` **Building Queries with QueryBuilder** To build a query, you need to create a QueryBuilder instance from the repository. ```php use Doctrine\ORM\QueryBuilder; $queryBuilder = $userRepository->createQueryBuilder('u'); ``` The QueryBuilder instance provides a fluent interface for building queries. You can add conditions, join tables, and specify the fetching mode. ```php $queryBuilder ->where('u.isActive = 1') ->andWhere('u.createdAt > :date') ->setParameter('date', '2020-01-01') ->orderBy('u.username', 'ASC') ; ``` **Using the repository pattern** The repository pattern is a design pattern that provides a layer of abstraction between the business logic and the data storage. In Doctrine, a repository is an object that provides methods for data access and manipulation. **Creating a custom repository** To create a custom repository, you need to create a new class that extends the `EntityRepository` class provided by Doctrine. ```php use Doctrine\ORM\EntityRepository; class UserRepository extends EntityRepository { public function findActiveUsers() { $queryBuilder = $this->createQueryBuilder('u'); $queryBuilder ->where('u.isActive = 1') ->orderBy('u.username', 'ASC') ; return $queryBuilder->getQuery()->getResult(); } } ``` **Using the custom repository** To use the custom repository, you need to inject the `Repository` object into your controller or service. ```php use App\Repository\UserRepository; // Inject the UserRepository in your controller or service private $userRepository; public function __construct(UserRepository $userRepository) { $this->userRepository = $userRepository; } // Use the custom repository method $activeUsers = $this->userRepository->findActiveUsers(); ``` **Best Practices** Here are some best practices for using QueryBuilder and repository pattern: * Use meaningful aliases for tables and fields. * Use the `setParameter()` method to bind parameters to the query. * Use the `getResult()` method to execute the query and get the result. * Use custom repositories to encapsulate complex queries. * Use the repository pattern to provide a layer of abstraction between the business logic and the data storage. **Conclusion** In this topic, we covered database queries using Doctrine's QueryBuilder and repository pattern. We learned how to create a QueryBuilder, build queries, and use the repository pattern. We also covered best practices for using QueryBuilder and repository pattern. **Next Topic** In the next topic, we will cover building forms using Symfony's Form component. **Recommended Reading** * [Doctrine Query Language (DQL)](https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/dql-doctrine-query-language.html) * [Repository Pattern](https://www.martinfowler.com/eaaCatalog/repository.html) **Leave a Comment or Ask for Help** If you have any questions or need help with this topic, please leave a comment below.