我正在尝试创建和返回给定PrivateUser的所有Variantset的操作。这是一个多对多关系,所以中间有一个连接表。
这是我要运行的查询
$variants = $repository->createQuery('SELECT variants
FROM AppBundle:PrivUser user
JOIN AppBundle:PrivUserVariantset uv
JOIN AppBundle:Variantset variants
WHERE user.iduser=:user_id')
->setParameter('user_id', $userid)
->getResult();
但是它返回:
[Syntax Error] line 0, col 146: Error: Expected =, <, <=, <>, >, >=, !=, got 'variants'
如果我尝试使用单个连接进行相同的查询,例如从PrivUserVariantset表中获取variantset的数量,它确实有效。这看起来像一个语法错误,但我没有设法从我收集的例子中找到它。
这是执行此查询的正确方法吗?
我认为更好的方法是:
$variantsRepository = $this->getDoctrine()->getRepository('AppBundle:Variantset');
$qb = $variantsRepository->createQueryBuilder('variants'); //'variants' is just alias, it can be whatever you want
$qb->join('variants.privUsers', 'user')
->where('user.id = :user_id')->setParameter('user_id', $userid)
->getQuery()
->getResult();
编辑:
/** @Entity **/
class Variantset
{
// ...
/**
* @ORM'ManyToMany(targetEntity="PrivUser")
* @ORM'JoinTable(name="Variantset_privUser",
* joinColumns={@ORM'JoinColumn(name="iduser", referencedColumnName="id")},
* inverseJoinColumns={@ORM'JoinColumn(name="idvariantset", referencedColumnName="id")}
* )
**/
private $privUsers;
// ...
public function __construct() {
$this->privUsers = new 'Doctrine'Common'Collections'ArrayCollection();
}
}
最后我通过改变JoinTable注释中详细的列来工作:variantid应该在第一个joinColumns上,而priv_userid必须在反向join上
Variantset类:
/**
* @var ArrayCollection
*
* @ORM'ManyToMany(targetEntity="PrivUser")
* @ORM'JoinTable(name="priv_user_variantset",
* joinColumns={@ORM'JoinColumn(name="variantset_varid", referencedColumnName="varid")},
* inverseJoinColumns={@ORM'JoinColumn(name="priv_user_priv_userid", referencedColumnName="priv_userid")}
* )
**/
private $privusers;
// ...
public function __construct() {
$this->privUsers = new 'Doctrine'Common'Collections'ArrayCollection();
}
控制器:
$variantsRepository = $this->getDoctrine()->getRepository('AppBundle:Variantset');
$em = $variantsRepository->createQueryBuilder('variants'); //'variants' is just alias, it can be whatever you want
$variants = $em->join('variants.privusers', 'user')
->where('user.privUserid = :user_id')->setParameter('user_id', $userid)
->getQuery()->getResult();
我只有两个实体,Varianset和PrivUser,而DB有三个表,每个实体一个加上priv_user_variantset,它通过manymany关系连接两者。
它现在为我的API的目的工作,但如果你有任何评论,他们将是非常欢迎的。