Doctrine using join table to return results not working Symfony2

Question

I'm having difficulties using join table values to fetch results via Doctrine. I'm currently programming a CRM for a client, which implements projects with tasks and users.

My structure is as follows:

user table - contains all users. tasks table - contains a list of tasks in a project user_task table - the join table that dictates which users can perform which tasks.

I have created a join table in my entity like so:

 /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\ManyToMany(targetEntity="AppBundle\Entity\Task", inversedBy="user")
     * @ORM\JoinTable(name="user_task",
     *   joinColumns={
     *     @ORM\JoinColumn(name="user_id", referencedColumnName="id")
     *   },
     *   inverseJoinColumns={
     *     @ORM\JoinColumn(name="task_id", referencedColumnName="id")
     *   }
     * )
     */
    protected $task;

And in my task entity:

/**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\ManyToMany(targetEntity="AppBundle\Entity\User", mappedBy="task")
     * @ORM\JoinTable(name="user_task")
     */
    private $user;

My problem is, that on the front end of the CRM, the admin will change a task from the drop down which then should poll the database to grab the list of users that are able to perform that task. But because the user_stage table is not an entity in its own right, I do not know how to incorporate it in my doctrine queryBuilder.

I tried using a native query, where $taskId is passed in from the Controller:

$qb = $this->createNativeQuery('SELECT user.name FROM user JOIN user_task ON user_task.user_id = user.id WHERE user_task.task_id = '.$taskId);
$result = $qb->getResult();

But I just get the following error:

Undefined method 'createNativeQuery'. The method name must start with either findBy or findOneBy!

I rarely use Native Queries so I am not sure what I'm doing wrong here. Is there any way of being able to perform the query I need without using NativeQuery, and just somehow accessing the join table by referencing it from both the User and Task entities?

Thank you Michael

Paweł Mikołajczuk Paweł Mikołajczuk · Accepted Answer · 2016-02-18T13:09:18

Don't use createNativeQuery where under $this you have Repository object.

Create method in repository class and call it there.

Doctrine using join table to return results not working Symfony2

1 Answers