blahblah blahblah - 3 months ago 19
MySQL Question

Doctrine Mysql: multiple joins in query builder

I have entities:

Tag
,
User
and
Service
.
Tag
has many-to-many relationship with both
User
and
Service
.

class Service{
/**
* @ORM\ManyToMany(targetEntity="Tag", mappedBy="serviceList")
*/
private $tagList;
}

class User{
/**
* @ORM\ManyToMany(targetEntity="Tag", mappedBy="userList")
*/
private $tagList;
}

class Tag{
/**
* @ORM\ManyToMany(targetEntity="User", inversedBy="tagList")
* @ORM\JoinTable(name="tags_users")
*/
private $userList;

/**
* @ORM\ManyToMany(targetEntity="Service", inversedBy="tagList")
* @ORM\JoinTable(name="tags_services")
*/
private $serviceList;
}


With query builder how can I return all services that has at least 1 common tag with given
User
, i.e services related to the user

This is a query I have written, but it's doesn't work

$query = $em->getRepository('AppBundle:Service')
->createQueryBuilder('service')
->join('service.tagList', 'serviceTag')
->join('AppBundle:BasicUser', 'user')
->join('user.tagList', 'userTag')
->where('user.id = :id')
->andWhere('serviceTag.id = userTag.id')
->setParameter('id', $user->getId())
;


Error:

[Syntax Error] line 0, col 104: Error: Expected Literal, got "JOIN" (500 Internal Server Error)

Answer

Your query is wrong. I think the easiest solution would be:

 $query = $em->getRepository('AppBundle:Service')
             ->createQueryBuilder('service')
             ->innerJoin('service.tagList', 'tag')
             ->andWhere(':user MEMBER OF tag.userList')
             ->setParameter('user',$user)
             ;