How can I tie bespoke doctrine repositories to mapped entities in symfony?

Question

I have two entities. Tasks and Department.

One department can have many tasks.

/**
 * @ORM\Entity
 * @ORM\Table(name="departments")
 */
class Department
{
    /**
     * @ORM\OneToMany(targetEntity="Task", mappedBy="department")
     */
    public $tasks;

    /**
     * @return ArrayCollection|Task[]
     */
    public function getTasks()
    {
        return $this->tasks;
    }
}

.

/**
 * @ORM\Entity
 * @ORM\Table(name="tasks")
 */
class Task
{
    /**
     * @ORM\ManyToOne(targetEntity="Department", inversedBy="tasks")
     */
    public $department;

    /**
     * @return Department
     */
    public function getDepartment()
    {
        return $this->department;
    }
}

I also have a view that shows all departments and their related tasks.

{% for department in departments %}
    {% for task in department.tasks %}
        ...
    {% endfor %}
{% endfor %}

This then gives me a few columns each filled with tasks.

I would now like to change this so that each column only shows the most imminent tasks (due within the week) ordered by due date.

To this end I have created the TaskRepository class with a custom query:

class TaskRepository extends EntityRepository
{
    /**
     * @param Department $department
     * @return Task[]
     */
    public function findAllRecentTasksForGenus(Department $department)
    {
        return $this->createQueryBuilder('task')
            ->andWhere('task.department = :department')
            ->setParameter('department', $department)
            ->andWhere('task.dueDate> :dueDate')
            ->setParameter('dueDate', new \DateTime('-1 week'))
            ->orderBy('task.dueDate', 'DESC')
            ->getQuery()
            ->execute();
    }
}

Now, if this was a page with just one department I would simply call the custom query in my controller, and pull that into the view.

However the fact that I am showing all departments with all their imminent tasks makes that a bit untidy in the controller, as it would force me to build up an array of departments and their tasks.

This feels ugly considering that currently I just needed to loop over department.tasks in twig and it knows to pull tasks related to the department entity.

I also know that I shouldn't (and can't easily) call the custom query in the repository in my department entity.

So what I am trying to find out is - can I somehow bind a bespoke query like described to the related entity so that doctrine knows that when I ask for tasks on a department it must go and not get all but the defined subset?

Ideally I'd like to be able to bypass end up with a twig template like below by calling on something like tasksDueSoon without having to add loads of extra logic to the controller (or elsewhere for that matter.

{% for department in departments %}
    {% for task in department.tasksDueSoon %}
        ...
    {% endfor %}
{% endfor %}

If that's not possible and I have to go down the route of a custom array via the controller that's fine of course but it feels wrong so was hoping there's a nicer way to do this in Syomfony and tell the Department entity to use the task repository or something.

Answer 1

I always hesitate to post an answer without testing the actual code but here goes. The trick is to load the recent tasks at the same time you load the departments.

class DepartmentRepository
{
    findDepartmentsAlongWithRecentTasks()
    {
        $qb = $this->createQueryBuilder('dept');
        $qb->select('dept','task');
        $qb->leftJoin('dept.tasks','task');

        $qb->andWhere('task.dueDate > :dueDate')
           ->setParameter('dueDate', new \DateTime('-1 week'))
           ->orderBy('task.dueDate', 'DESC');

        return $qb->getQuery()->execute();

Answer 2

There is an @OrderBy-annotation on your tasks property in the Department class, but as far as I know you can't make this a more complex query.

What I usually do is create another getter that uses Collection Criteria to filter out the children I want to have.

Granted this has a some overhead as it operates on all tasks fetched from the repository, but I find that in most cases the performance is still perfectly fine for the scale I'm usually operating in. When this becomes slow I usually fall back to your current solution you dislike or even further and use a NativeQuery and/or partial result if necessary.

Answer 3

I would just create a method in the Department entity, getTasksOrderedByDueDate() or something. Might have slightly less performance than an SQL query, but it seems cleaner to me.

public function getTasksOrderedByDueDate(): array
{
    $tasks = $this->getTasks();
    usort($tasks, function (Task $a, Task $b) {
        return $a->getDueDate()->getTimestamp() - $b->getDueDate()->getTimestamp();
    });

    return $tasks;
}

And simply use this in the Twig template:

{% for department in departments %}
    {% for task in department.tasksOrderedByDueDate %}
        ...
    {% endfor %}
{% endfor %}

Now unless it becomes a real issue, don't worry about performance.