Join 3表与doctrine 2查询生成器

Question

嘿，伙计们，我有三张桌子

1) expert_class
2) expert_location
3) expert

我想从我所知道的mysql查询的所有三个表中获取数据

select * from expert_class class 
join expert_location loc 
on loc.expert_id = class.expert_id 
join expert e 
on class.expert_id = e.id 
where e.is_delete=0

使用这个查询，我得到了我想要的所有数据，但问题是我必须使用doctrine查询构造器来编写这个查询，我尝试了这样

$classes = $this->qb
                    ->add('select', 'exp_cls,exp_loc.id as location_id')
                    ->from('Entity\expert_class','exp_cls')
                    ->join('Entity\expert_location', 'exp_loc')
                    ->join('Entity\expert', 'exp')
                    ->where('exp_cls.expert_id = exp.id')
                    ->AndWhere('exp_cls.expert_id = exp_loc.expert_id')
                    ->AndWhere('exp.is_delete = 0')
                    ->getQuery()
                    ->getArrayResult(); 

当我尝试运行这个查询时，我得到了这个致命的错误

Fatal error: Uncaught exception 'Doctrine\ORM\Query\QueryException' with message 'SELECT exp_cls,exp_loc.id as location_id FROM Entity\expert_class exp_cls INNER JOIN Entity\expert_location exp_loc INNER JOIN Entity\expert exp WHERE exp_cls.expert_id = exp.id AND exp_cls.expert_id = exp_loc.expert_id AND exp.is_delete = 0' in C:\xampp\htdocs\projectname\application\libraries\Doctrine\ORM\Query\QueryException.php:39 Stack trace: #0 C:\xampp\htdocs\projectname\application\libraries\Doctrine\ORM\Query\Parser.php(396): Doctrine\ORM\Query\QueryException::dqlError('SELECT exp_cls,...') #1 C:\xampp\htdocs\projectname\application\libraries\Doctrine\ORM\Query\Parser.php(2363): Doctrine\ORM\Query\Parser->syntaxError('Literal') #2 C:\xampp\htdocs\projectname\application\libraries\Doctrine\ORM\Query\Parser.php(2550): Doctrine\ORM\Query\Parser->Literal() #3 C:\xampp\htdocs\projectname\application\libraries\Doctrine\ORM\Query\Parser.php(2485): Doctrine\ORM\Query\Parser-> in C:\xampp\htdocs\projectname\application\libraries\Doctrine\ORM\Query\QueryException.php on line 44

我也查了这个链接Doctrine query builder using inner join with conditions，但没有帮助

Answer 1

你在你的实体中定义关系了吗？例如：

/**
 * @var ArrayCollection
 *
 * @ORM\OneToMany(targetEntity="expert_location", mappedBy="locations")
 */
private $location;

如果你这样做了，那么你必须在你的连接中使用这些连接，例如：

->join('exp_cls.locations', 'exp_loc')  // This joins the expert_location entity

如果你没有，那么你应该添加一个ON条件：

use Doctrine\ORM\Query\Expr\Join;

...

->join('Entity\expert_location', 'exp_loc', Join::ON, $qb->expr()->eq('exp_loc.expert_id', 'exp_cls.expert_id '))