php - 使用 doctrine 2 查询生成器加入 3 个表

Question

大家好，我有三张 table

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

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 '))