Close

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.

About the author

Jon Morgan

Jon Morgan

Jon has been a software developer for almost 20 years. Working at Box UK he is focused on web and mobile technologies, combining this technical knowledge with an understanding of client requirements to deliver complex projects such as faceted search solutions, API integrations and data aggregation systems.

Comments

Shprink

Jan 30th, 2014

Thanks, your post made my day ;)

marco z.

Feb 17th, 2014

Very concise, nicely written, and useful.

Alex

Jun 23rd, 2014

Thanks I was looking for this, so much you save my night, unfortunately I lost my day.

Bart

Jul 30th, 2014

Nice tutorial, but I was wondering, why filtering the association? Why not just do something like this: When on a department page, you probably have the name or the id, … So you can just get users from a certain department by this: $users = $em->getRepository(‘MyProject\Domain\User’)->findBy(array(‘dept_id’ => 20));

Luca

Aug 3rd, 2014

why do i have this error? Matching objects by id is not compatible with matching on an in-memory collection, which compares objects by reference. I have the same your situation with users-events many-to-many.

Tomáš Votruba

Aug 14th, 2014

Thanks for nice example!

Joachim

Feb 12th, 2015

Thank you that was very helpful!

Steve

Feb 25th, 2015

Good reading! Keep in mind, though, using Criteria will execute a new query every time you call the method. Using this approach might not be suitable in all cases due to it’s impact on performance.

Gilson

Mar 1st, 2015

Thank you. It’s a simple and good solution!

Jan 1st, 0001

Jan 1st, 0001

Jan 1st, 0001

issam

May 1st, 2015

Very helpful, many thanks.

Add Your Comment

Related content

We're hiring. Let's talk. View available roles