doctrine2 - Adding in an innerJoin a where clause on the many to many joinning table -

i have existing query join:

  public function findclientsandtheirusers($user_id, $search) {     $query = $this->getentitymanager()->createquerybuilder()       ->select('c.client_id, c.title, u.user_id, u.email')       ->from('application\entity\user', 'u')       ->innerjoin('u.clients', 'c')       ->innerjoin('c.users', 'cu')       ->andwhere("cu.user_id = {$user_id}")       ->groupby('c.client_id')       ->orderby('c.title, u.email', 'asc');     return $query->getquery()->getresult(\doctrine\orm\query::hydrate_array);   } 

resting on these tables:

client

| client_id   | int(10) unsigned | no   | pri | null    | auto_increment | | title       | tinytext         | no   |     | null    |                | 

user

| user_id        | int(10) unsigned     | no   | pri | null    | auto_increment | | email          | varchar(255)         | no   | uni | null    |                | 

user_has_client

| user_id   | int(10) unsigned | no   | pri | null    |       | | client_id | int(10) unsigned | no   | pri | null    |       | 

with these entities:

client

/**  * @orm\manytomany(targetentity="user", inversedby="clients")  * @orm\jointable(name="user_has_client",  *     joincolumns={@orm\joincolumn(name="client_id", referencedcolumnname="client_id")},  *     inversejoincolumns={@orm\joincolumn(name="user_id", referencedcolumnname="user_id")}  * )  */ protected $users; 

user

/**  * @orm\manytomany(targetentity="client", mappedby="users")  */ protected $clients; 

userhasclient

/**  * @orm\id  * @orm\column(type="integer", options={"unsigned"=true})  */ protected $user_id;  /**  * @orm\id  * @orm\column(type="integer", options={"unsigned"=true})  */ protected $client_id;  /**  * @orm\manytoone(targetentity="user", inversedby="userhasclients")  * @orm\joincolumn(name="user_id", referencedcolumnname="user_id", nullable=false)  */ protected $user;  /**  * @orm\manytoone(targetentity="client", inversedby="userhasclients")  * @orm\joincolumn(name="client_id", referencedcolumnname="client_id", nullable=false)  */ protected $client; 

now, added roles column user_has_client table:

| user_id   | int(10) unsigned | no   | pri | null    |       | | client_id | int(10) unsigned | no   | pri | null    |       | | roles     | tinytext         | yes  |     | null    |       | 

and update query returns users with, say, admin roles.

try this, hope helpfull-

public function findclientsandtheirusers($user_id, $search) {     $query = $this->getentitymanager()->createquerybuilder()             ->select('c.client_id, c.title, u.user_id, u.email')             ->from('application\entity\user', 'u')             ->innerjoin('u.userhasclients', 'uhc')             ->innerjoin('uhc.client', 'c')             ->where("u.user_id = {$user_id}")             ->andwhere("uhc.roles = :role")->setparameter('role', $role)             ->groupby('c.client_id')             ->orderby('c.title, u.email', 'asc');     return $query->getquery()->getresult(\doctrine\orm\query::hydrate_array); } 

let me know if still problem.