使用doctrine 2查询构建器加入3表

Question

Hey guys i have three table

1) expert_class
2) expert_location
3) expert

i want to fetch data form all three table i know query in mysql i.e

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

using this query i got all data whatever i want but the problem is i have to write this query using doctrine query bulider i tried like this

$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(); 

when i try to run this query i got this 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

i also looked up this link Doctrine query builder using inner join with conditions but its not helped

Answer 1

Have you defined relations in your entities? E.g.:

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

If you did, then you have to use those connections in your join, e.g.:

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

You you didn't, then you should add an ON condition:

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