Symfony2 - How to query a left-join with a condition in doctrine 2
16,525
Solution 1
You almost had it with your second attempt:
$query = $qb
->select('school')
->from('AppBundle:School', 'school')
->leftJoin('school.personal', 'personal')
->leftJoin('personal.user', 'user')
->where('user.id = :userId')
->setParameters(array(':userId' => $user->getId()))
->getQuery();
Solution 2
You said that this:
$query = $qb
->select('school')
->from('AppBundle:School', 'school')
->leftJoin('school.personal', 'p', 'WITH', 'p.user = :user')
->setParameters(array(':user' => $user))
->getQuery();
gives you back all schools. That is exactly what a left-join
is supposed to do.
If you want only schools with personal user (from your question I guess that is what you want to achieve) the you should use a inner-join
:
$query = $qb
->select('school')
->from('AppBundle:School', 'school')
->innerJoin('school.personal', 'p', 'WITH', 'p.user = :user')
->setParameters(array(':user' => $user))
->getQuery();
![Slowwie](https://i.stack.imgur.com/QiXrJ.jpg?s=256&g=1)
Author by
Slowwie
Updated on June 04, 2022Comments
-
Slowwie about 2 years
My database structure looks like this:
user <-> personal <-> school
so the personal object holds information about the user and the school like this:
class Personal { /** * @var integer * * @ORM\Column(name="id", type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */ private $id; /** * @var string * * @ORM\ManyToOne(targetEntity="user", inversedBy="schools", fetch="EAGER") */ private $user; /** * @var string * * @ORM\ManyToOne(targetEntity="school", inversedBy="personal", fetch="EAGER") */ private $school; }
So I want to fetch the schools of an user:
$query = $qb ->select('school') ->from('AppBundle:School', 'school') ->leftJoin('school.personal', 'p', 'WITH', 'p.user = :user') ->setParameters(array(':user' => $user)) ->getQuery();
I also tried the following:
$query = $qb ->select('school') ->from('AppBundle:School', 'school') ->leftJoin('u.personal', 'personal') ->leftJoin('personal.user', 'pu') ->where('pu = :user') ->setParameters(array(':user' => $user)) ->getQuery();
But nothing works. The first gives me back all Schools :S. The second query gives no school back! :(
How can I get all schools for user?
-
Cerad over 8 yearsThe where condition takes care of it. Only schools that are linked to the given user will be selected.
-
Wilt over 8 yearsOkay, you are right. I guess we can avoid having this discussion here. My answer was correct too, I wonder why @Slowwie accepted yours since I was first...