Sling Academy
Home/PHP/PHP Doctrine: How to Filter Data

PHP Doctrine: How to Filter Data

Last updated: January 13, 2024

Introduction

Data filtering is an essential task when dealing with database management in any web application. PHP and the Doctrine ORM (Object-Relational Mapper) provide developers with an elegant and efficient way to retrieve and manipulate data according to various criteria. In this tutorial, we will dive deep into how to filter data using PHP Doctrine, touching upon fundamentals and providing some real-world examples.

Understanding Doctrine QueryBuilder

The QueryBuilder is a powerful tool in Doctrine that allows for writing SQL queries in a programmatic way using PHP code. It abstracts the database query creation process and enables you to select, update, or delete data in an object-oriented way.

To start using QueryBuilder, you first need an instance of an EntityManager, which is the central access point for ORM functionality. Here’s how we create it:

$entityManager = \Doctrine\ORM\Tools\Setup::createAnnotationMetadataConfiguration($paths, $isDevMode);
$entityManager = \Doctrine\ORM\EntityManager::create($dbParams, $config);

Selecting Data

Let’s start by looking at a basic selection:

$queryBuilder = $entityManager->createQueryBuilder();
$queryBuilder->select('u')
            ->from('User', 'u');
$query = $queryBuilder->getQuery();
$result = $query->getResult();

In the example above, you’d replace ‘User’ with the actual name of your entity. The ‘u’ is an alias for the User table used in your query, and this retrieves all records from that entity.

Adding Conditions

To filter data, we use the where method along with conditions:

$queryBuilder->select('u')
            ->from('User', 'u')
            ->where('u.age > :age')
            ->setParameter('age', 18);
$query = $queryBuilder->getQuery();
$result = $query->getResult();

This filters the users whose age is greater than 18.

Advanced Conditions

Doctrine allows you to use AND/OR conditions:

$queryBuilder->select('u')
            ->from('User', 'u')
            ->where('u.age > :age')
            ->andWhere('u.subscription = :subscribed')
            ->setParameters(array(
                'age' => 20,
                'subscribed' => true
            ));
$query = $queryBuilder->getQuery();
$result = $query->getResult();

Above, users selected will be older than 20 and have an active subscription.

Sorting Results

Sorting is accomplished using orderBy:

$queryBuilder->select('u')
            ->from('User', 'u')
            ->orderBy('u.username', 'ASC');
$query = $queryBuilder->getQuery();
$result = $query->getResult();

This will return users sorted alphabetically by their username.

Using the Repository Class

Each entity in Doctrine has a corresponding repository class, which can provide convenience methods for selecting data.

$userRepository = $entityManager->getRepository('User');
$users = $userRepository->findBy(array('age' => 20), array('username' => 'ASC'));

In this case, findByis used to retrieve all users of age 20 and sorts them by username.

Complex Filtering with DQL

For complex filtering, you can use Doctrine Query Language (DQL), which closely resembles SQL.

$query = $entityManager->createQuery('SELECT u FROM User u WHERE u.age > :age ORDER BY u.username ASC');
$query->setParameter('age', 18);
$users = $query->getResult();

With DQL, you have the full power of SQL combined with object-oriented features.

Handling Pagination

When dealing with large datasets, pagination is crucial. Doctrine’s Paginator class can handle this gracefully:

$dql = 'SELECT u FROM User u ORDER BY u.id ASC';
$query = $entityManager->createQuery($dql)
             ->setFirstResult(0)
             ->setMaxResults(100);
$paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query, $fetchJoinCollection = true);
foreach ($paginator as $user) {
    //.. Do something with $user
}

Conclusion

Doctrine offers a variety of ways to filter data with ease and efficiency – from using the QueryBuilder or repository functions, to writing custom DQL queries. Remember to use parameters to prevent SQL injection attacks, and take advantage of pagination to handle large amounts of data.

We’ve covered the basics and a bit more advanced subjects concerning filtering data using Doctrine in PHP. Apply these skills in your next project to make your data querying as precise and secure as possible!

Next Article: PHP Doctrine: How to count rows in a table (with & without filters)

Previous Article: How to implement pagination in Symfony & Doctrine

Series: Symfony & Doctrine Tutotirlas

PHP

You May Also Like

  • Pandas DataFrame.value_counts() method: Explained with examples
  • Constructor Property Promotion in PHP: Tutorial & Examples
  • Understanding mixed types in PHP (5 examples)
  • Union Types in PHP: A practical guide (5 examples)
  • PHP: How to implement type checking in a function (PHP 8+)
  • Symfony + Doctrine: Implementing cursor-based pagination
  • Laravel + Eloquent: How to Group Data by Multiple Columns
  • PHP: How to convert CSV data to HTML tables
  • Using ‘never’ return type in PHP (PHP 8.1+)
  • Nullable (Optional) Types in PHP: A practical guide (5 examples)
  • Explore Attributes (Annotations) in Modern PHP (5 examples)
  • An introduction to WeakMap in PHP (6 examples)
  • Type Declarations for Class Properties in PHP (5 examples)
  • Static Return Type in PHP: Explained with examples
  • PHP: Using DocBlock comments to annotate variables
  • PHP: How to ping a server/website and get the response time
  • PHP: 3 Ways to Get City/Country from IP Address
  • PHP: How to find the mode(s) of an array (4 examples)
  • PHP: Calculate standard deviation & variance of an array