Advanced filtering of a collection of related persons in Symfony

Question

If I have a linked object that is a collection, what options do you have when retrieving?

eg. Suppose I have a $view object with this definition inside it:

 /** * @ORM\OneToMany(targetEntity="\Gutensite\CmsBundle\Entity\View\ViewVersion", mappedBy="entity") * @ORM\OrderBy({"timeMod" = "DESC"}) */ protected $versions; public function getVersions() { return $this->versions; } 

And I want to get all versions related to the entity, like this:

 $view->getVersions(); 

This will return the collection. Excellent. But is it possible to take this collection and filter it by criteria, for example. newer than a certain date? Or order it according to some (other) criteria?

Or at the moment you are just expecting to make a request in the repository:

 $versions = $em->getRepository("GutensiteCmsBundle:View\ViewVersion")->findBy(array( array( 'viewId', $view->getId(), 'timeMod', time()-3600 ) // order by array('timeMod', 'DESC') )); 

Answer 1

Recent versions of Doctrine have a surprisingly unknown feature that makes these queries much easier.

It doesn't seem to have a name, but you can read about it in the Doctrine docs in 9.8 Filtering Collections.

Collections have a filtering API that allows you to trim parts of the data from the collection. If the collection is not already loaded from the database, the filtering API can work at the SQL level to provide optimized access to large collections.

In your case, you can write such a method on your View object.

 use Doctrine\Common\Collections\Criteria; class View { // ... public function getVersionsNewerThan(\DateTime $time) { $newerCriteria = Criteria::create() ->where(Criteria::expr()->gt("timeMod", $time)); return $this->getVersions()->matching($newerCriteria); } } 

This will do one of two things:

• If the collection is hydrated, it will use PHP to filter the existing collection.
• If the collection is not hydrated, it will retrieve the partial collection from the database using SQL constraints.

Which is really great, because connecting repository methods to your views is usually messy and prone to breaking.

I also like the @ igor-pantovic answer, although I saw that the method causes some funny errors.

Answer 2

I would personally avoid using the annotation order directly. Yes, you should query, as if you were using raw SQL without Doctrine at all.

However, I would not have done it at that moment, but even earlier. In your particular case, I would create a ViewRepository class:

 class ViewRepository extends EntityRepository { public function findWithVersionsNewerThan($id, \DateTime $time) { return $this->createQueryBuilder('view') ->addSelect('version') ->join('view.versions', 'version') ->where('view.id = :id') ->andWhere('version.timeMod > :time') ->setParameter('time', $time) ->setParameter('id', $id) ->getQuery() ->getOneOrNullResult(); } } 

Now you can do:

 $yourDateTime = // Calculate it here ... ; $view = $em->getRepository("GutensiteCmsBundle:View\ViewVersion")->findWithVersionsNewerThan($yourDateTime); $versions = $view->getVersions(); // Will only contain versions newer than datetime provided 

I am writing code from the top of my head here, so it is unfortunate if a syntax or named error is stitched.