Filtering Associations with Doctrine 2

Doctrine 2

While working on a Symfony2 web application recently, I was tasked with mapping a complex data model to Doctrine 2 entities and checking that the design we chose had minimal impact on performance. Now, although I’ve used Doctrine 2 before, this was my first foray into doing something a little out of the ordinary (besides associating Blog to Comment, for example).

Along the way, I found some issues with filtering associations and this blog post will hopefully explain the problem and provide a couple of solutions if you’re experiencing a similar situation.

Firstly, here’s a simple chunk of the Entity Relationship Diagram we came up with and the area that was causing me a headache.

The Problem

I went ahead and added the annotations for the employee association to the department entity; I was then able to retrieve all the employees in that department by calling $department->getEmployees()

class Department
{
    /**
     * @ORMOneToMany(targetEntity="Employee", mappedBy="department", cascade={"persist"})
     */
    protected $employees;
 
   public function __construct()
    {
       $this->employees = new ArrayCollection();
    }
 
   /**
     * Get Employees
     *
     * @return Collection
     */
    public function getEmployees()
    {
        return $this->employees;
    }
} 

Ok, that was pretty quick to get working, basic stuff. I could now retrieve all the employees in a department:

class DepartmentController extends Controller
{
    public function indexAction()
    {
        $department = $this->getDoctrine()
                           ->getRepository('MyBundle:Department')
                           ->find(1);
  
      $employees = $department->getEmployees();
  
      //...etc....
    }
}

I then wanted to be able to get all the employees in a department that are of a particular employee type (full-time, part-time, etc.). Surely, I could do that in 5 minutes? I assumed I could just do something like this:

$employees = $department->getEmployees()->filter('employeeTypeId' => 1);

I did some digging to find the methods I needed but it seems that filtering my employee collection at the Doctrine level can’t be done (see here).

Doctrine Filter

So, I decided to go down the route of creating a method in a repository class which used QueryBuilder to fetch a department’s employees that are of a particular employee type:

class EmployeeRepository extends EntityRepository
{
    public function getFilteredEmployees($employeeTypeId, $departmentId)
    {
        $qb = $this->createQueryBuilder('e')
                   ->innerJoin('e.department', 'd')
                   ->innerJoin('e.employeeType', 'et')
                   ->where('d.id = :departmentId')
                   ->andWhere('et.id = :employeeTypeId')
                   ->setParameter('departmentId', $departmentId)
                   ->setParameter('employeeTypeId', $employeeTypeId);
  
      return $qb->getQuery()->getResult();
    }
}

This did the trick, but it didn’t feel very clean because I’d have to run another query which I felt was unnecessary, especially if I’d already eagerly loaded the employee and could retrieve them with $department->getEmployees().

Doctrine Criteria

Following this I decided to try a different route and bumped into the Criteria class. This allows you filter a collection not at the Doctrine level but on the returned collection we get when calling $department–>getEmployees(). It also has basic DQL methods which allow you to powerfully filter your collection using various conditions. Here’s what I came up with:

// Entity class
class Department
{
    public function getEmployeesByEmployeeTypeId($employeeTypeId)
    {
        $criteria = Criteria::create();
        $criteria->where(Criteria::expr()->eq('employeeType', $employeeTypeId));
 
       return $this->employees->matching($criteria);
    }
}

This works really well, and the code in my controller feels nice and clean:

class DepartmentController extends Controller
{
    public function indexAction()
    {
        $department = $this->getDoctrine()
                           ->getRepository('MyBundle:Department')
                           ->find(1);
 
      $employees = $department->getEmployeesByEmployeeTypeId(5);
 
       //...etc....
    }
} 

The eagle-eyed among you will have also noticed that the $criteria–>where() clause is filtering on the employeeType field of my employee entity even though I’ve supplied it with an employee id! It magically works out the join column for you, presumably by cleverly examining the JoinColumn annotation.

class Employee
{
    /**
     * @ORMManyToOne(targetEntity="EmployeeType", inversedBy="employees")
     * @ORMJoinColumn(name="employeetype_id", referencedColumnName="id")
     */
    protected $employeeType;
 
   //...etc...
}

Conclusion

Both solutions have their pros and cons (performance for example) but I eventually chose the Criteria option. I suggest you try them out and, until we get a fix for the Doctrine association filter, choose the solution that best suits your needs.

At Box UK we have a strong team of bespoke software consultants with more than two decades of bespoke software development experience. If you’re interested in finding out more about how we can help you, contact us on +44 (0)20 7439 1900 or email info@boxuk.com.