Advanced Filtering of Associated Entity Collection

Question

If I have an associated entity which is a collection, what options do you have when fetching?

e.g. Lets say I have a $view entity 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 the all the versions associated with the entity like this:

$view->getVersions();

This will return a collection. Great. But is it possible to take that collection and filter it by criteria, e.g. newer than a certain date? Or order it by some (other) criteria?

Or at this point are you just expected to do a query on the repository:

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

Answer 1

I would personally avoid using order by on annotation directly. Yes, you are supposed to do a query, just as you would if you were using raw SQL without Doctrine at all.

However, I wouldn't do it at that point but even before. In your specific case I would create an 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'm writing code from the top of my head here directly so sorry if some syntax or method naming error sneaked in.

Answer 2

There is a surprisingly unknown feature in recent versions of Doctrine, which makes these sort of queries much easier.

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

Collections have a filtering API that allows to slice parts of data from a collection. If the collection has not been loaded from the database yet, the filtering API can work on the SQL level to make optimized access to large collections.

In your case you could write a method like this on your View entity.

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:

1. If the collection is hydrated, it will use PHP to filter the existing collection.
2. If the collection is not hydrated, it will fetch a partial collection from the database using SQL constraints.

Which is really great, because hooking up repository methods to your views is usually messy and prone to break.

I also like @igor-pantovic's answer, although I've seen the method cause some funny bugs.