Doctrine and LIKE queries: Why findBy fails and how to fix it
You are attempting to combine a strict equality check on the email address with a pattern match using LIKE in your SQL query. Specifically, you want to filter orders where OrderEmail equals 'some@mail.com' and Product starts with 'My Products'. When you try to replicate this logic using Doctrine's findBy() methodâpassing arguments like $this->findBy(['email' => 'some@mail.com', 'product' => 'My Products%'])âit fails. This isn't a bug in your entity mapping or an IDE glitch; it is a fundamental limitation of how Doctrine constructs queries for magic methods versus raw SQL.
The findBy() method relies on Doctrine's internal query builder to translate your arguments into a DQL (Doctrine Query Language) statement. Under the hood, Doctrine generates a WHERE clause by joining your provided keys to their corresponding column names. However, the engine expects simple scalar values for these keys. It does not automatically append wildcard characters like % or _ to the value you pass unless you explicitly construct the string with them inside the array key itself, which is often misleading and brittle.
More critically, findBy() is designed for exact matches when used with standard indexing strategies in many contexts, though it technically supports parameter binding. The real issue arises because findBy() attempts to build a static query tree. While you might think passing 'My Products%' into the array will work, Doctrine's internal logic often treats the value as a literal string for equality comparison unless wrapped in specific DQL structures that handle wildcards gracefully across different database drivers. In practice, relying on findBy for LIKE conditions is notoriously unreliable and can lead to syntax errors or unexpected behavior depending on the underlying DBAL version.
To achieve your goal without writing raw SQL strings (which you mentioned wanting to avoid), you need to use Doctrine's QueryBuilder directly. This approach gives you precise control over how parameters are bound and allows you to inject wildcards explicitly while keeping the logic within the ORM layer.
Here is how you should structure your repository method:
public function findByEmailAndProduct(string $email, string $product)
{
return $this->createQueryBuilder('o')
->where('o.email = :email')
->andWhere('o.product LIKE :product')
->setParameter('email', $email)
->setParameter('product', $product . '%') // Append the wildcard explicitly
->getQuery()
->getResult();
}
In this implementation, we create a QueryBuilder instance and chain the conditions. Notice how we append the % character directly to the $product variable before passing it to setParameter. This is the correct way to handle leading wildcards in Doctrine. If your requirement were trailing wildcards (e.g., searching for products ending with "My"), you would append that instead, or use a raw parameter if using DQL with specific syntax requirements.
Using QueryBuilder ensures that your parameters are safely bound, preventing SQL injection risks even when dealing with patterns. It also keeps your code readable and maintainable compared to mixing raw SQL strings with Doctrine entities. While findBy offers convenience for simple exact-match lookups, complex pattern matching requires the explicit flexibility of the builder.
If you find yourself needing to mix multiple conditions where some are exact matches and others require wildcards, or if you need to combine this with joins and groupings, stick to the QueryBuilder pattern. It is the standard approach for advanced filtering in Symfony applications and ensures compatibility across different database drivers without hitting the limitations of the findBy magic method.
