How do I use a complex criteria inside a doctrine 2 entity's repository?

How do I use a complex criteria inside a doctrine 2 entity's repository?

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

How do I use a complex criteria inside a doctrine 2 entity's repository?

When working with Doctrine 2 repositories, the findBy method is often the first tool developers reach for because it maps directly to familiar array syntax. Passing an argument like ['name' => 'billy'] feels intuitive; it translates immediately to a simple equality check. However, this convenience creates a friction point when you need to express more complex logic, such as date ranges or inequality checks.

Consider a scenario involving a Festival entity where each record stores a startDate and an endDate. To find all festivals happening on a specific day, you cannot simply pass a single value to findBy. You need to select records where the start date is less than or equal to the query date, and the end date is greater than or equal to that same date. The standard array syntax supported by findBy does not natively support operators like >, <, !=, IN, NOT IN, or LIKE.

To bridge this gap, you must bypass the simplified array signature of findBy and interact with Doctrine's QueryBuilder directly. This approach gives you full access to the DQL (Doctrine Query Language) parsing logic, allowing you to construct the exact conditions required for your complex criteria.

Here is how you would implement this in a repository class:

use Doctrine\ORM\EntityRepository;

class FestivalRepository extends EntityRepository
{
    public function getFestivalsOnDate($date)
    {
        $qb = $this->createQueryBuilder('f');

        // Construct the date range logic using DQL
        return $qb
            ->select('f')
            ->where('f.startDate <= :date')
            ->andWhere('f.endDate >= :date')
            ->setParameter('date', $date)
            ->getQuery()
            ->getResult();
    }
}

In this example, we instantiate the QueryBuilder and explicitly chain .where() clauses. This is necessary because the array passed to findBy is essentially a shorthand wrapper around a single WHERE clause using equality operators. When you need multiple conditions or different comparison operators, you must define them manually within the query chain.

Handling Operators and Complex Logic

Once you are inside the QueryBuilder, the available operators expand significantly beyond simple equality. You can mix and match operators to build robust queries.

For instance, if you wanted to find festivals that are not happening today, you could use != or <>. If you needed to filter by a list of city IDs, the IN operator becomes essential. Doctrine handles these DQL keywords naturally within the where() clause.

// Example using IN operator
$qb->where('f.countryId IN (:ids)')
    ->setParameter('ids', [105, 203, 401]);

Similarly, string matching is handled via LIKE. You might construct a query to find festivals with names containing "Summer":

$qb->where('f.name LIKE :pattern')
    ->setParameter('pattern', 'Summer%');

Trade-offs and Pitfalls

The primary trade-off here is the loss of convenience. Using findBy for complex criteria forces you to write more verbose code compared to the one-liner array syntax. However, this verbosity is intentional; it prevents ambiguity in how conditions are combined.

A common pitfall when transitioning from array-based criteria to QueryBuilder is forgetting to call setParameter(). If you insert raw values directly into the DQL string (e.g., $qb->where('f.name = ' . $input)), you open your application to SQL injection vulnerabilities. Doctrine's parameter binding handles escaping automatically, but this only works if you use the named parameters defined in the query and bind them via setParameter().

Another consideration is performance. While simple queries benefit from Doctrine's caching of compiled DQL, highly complex queries with many conditions might require specific indexing on your database columns to maintain efficiency. For example, filtering by date ranges requires indexes on startDate and endDate to ensure the database engine can quickly isolate the relevant rows rather than scanning the entire table.

By moving away from the restrictive array syntax of findBy and embracing the QueryBuilder's DQL capabilities, you regain full control over your data retrieval logic without compromising on security or clarity.

Our network