CODEWITHSUNDEEP
phpdoctrine-ormdoctrine
ba0708
ba0708

Reputation: 10599

Filtering on association with criteria object in Doctrine 2

Say that I have an entity Profile which has an association with an Account entity. I want to fetch the profile with profileCode = 12345 and where its related Account has the e-mail address of [email protected]. So, I need to specify a condition on both entities.

For this I have created a custom repository Repository\Profile and now I am wondering how to implement this. I know that I can solve all of this with a "raw" DQL query or by using the query builder. However, I feel as if it is not as pretty as I would like, because it is very close to raw SQL. Sure the syntax is a bit different, but conceptually, I'd be thinking more in terms of SQL than OOP. I will be doing these kind of things a lot, so I am really trying to do it the best way.

I have done a bit of reading on the Criteria object (the documentation is sparse), and it really seems great as long as I am filtering on a single entity. I was unable to find any solution to using Criteria when filtering on associated entities.

So basically my question is: is there any way in which I can use the Criteria object for filtering on multiple entities directly from the database, and within a custom repository? I would really prefer to code this within a custom repository than the entity itself as I have seen some people do. Is this possible, or are there any good alternatives, or is plain DQL or the query builder really the way to go?

Upvotes: 8

Views: 6467

Answers (2)

Will B.
Will B.

Reputation: 18436

I do not see why not, though not sure how your association is related. One-to-One, One-to-Many, or Many-to-Many

If you're trying to retrieve records from the 3rd association depth level such as Account.profile -> Profile.code -> Code.xxx (not id) you would need to use DQL or or the QueryBuilder to establish what Profile and Code were joining Account with or resolve the Code entity to provide it to Criteria.

Assuming a One-to-Many where one Account has many Profiles. You can also optionally just define it within the entity itself to make it easier to manage and reduce having the extra call to getProfiles().

Repository Usage

use Doctrine\ORM\EntityRepository;
use Doctrine\Common\Collections\Criteria;

class AccountRepository extends EntityRepository {
   
    /**
     * @var string|Account $accountOrEmail
     * @var string $code
     * @return Profile|null
     */
    public function getAccountProfileByEmailAndCode($accountOrEmail, $code)
    {
         try{
             $account = ($accountOrEmail instanceof Account ? $accountOrEmail: $this->findOneBy(['email' => $accountOrEmail]));
             if (!$account instanceof Account) {
                 throw new \InvalidArgumentException('Unknown Account Specified.');
             }
             $criteria = Criteria::create()->setMaxResult(1);
             $expr = $criteria::expr();
             $criteria->where($expr->eq('code', $code));

             return $account->getProfiles()->matching($criteria)->first() ?: null;
         } catch(\Exception $e) {
             return null;
         }
    }

}

$accountRepo = $em->getRepository('\Entities\Account');
$profile = $accountRepo->getAccountProfileByEmailAndCode('[email protected]', '12345');

Entity usage

use Doctrine\Common\Collections\Criteria;

/**
 * @ORM\Entity(repositoryClass="AccountRepository")
 */
class Account {

    // ...
    
    public function getProfiles(Criteria $criteria = null)
    {
        if ($criteria instanceof Criteria) {
            return $this->profiles->matching($criteria);
        }

        return $this->profiles;
    }

    /**
     * @var string $code
     * @return null|Profile
     */
    public function getProfileByCode($code)
    {
         $criteria = Criteria::create()->setMaxResult(1);
         $expr = $criteria::expr();
         $criteria->where($expr->eq('code', $code));

         return $this->getProfiles($criteria)->first() ?: null;
    }

}

$account = $em->getRepository('\Entities\Account')->findOneBy(['email' => '[email protected]']);
$profile = $account->getProfileByCode('12345');

Upvotes: 0

K. Norbert
K. Norbert

Reputation: 10684

Criteria can only filter the associations on the entity itself, but if I understand your use case, you need to filter 2 levels deep, so it won't work.

You are trying to shape Doctrine into something that is not, by wanting to do things "your way". It will only hurt you in the long run. The solution is already there, it is perfectly fine to use DQL, especially in repositories.

Upvotes: 1

Related Questions