Symfony2 - How to query a left-join with a condition in doctrine 2

Question

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?

what is $qb ?? $qb = $em->getRepository("AppBundle:Personal") ?? — dieuvn3b, Dec 16 '15 at 07:05
Have a look at http://stackoverflow.com/questions/23454217/doctrine2-association-mapping-with-conditions This is solved using the criteria API there — DerStoffel, Dec 16 '15 at 07:43

score 5 · Accepted Answer · answered Dec 16 '15 at 15:22

5

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

answered Dec 16 '15 at 15:22

Cerad

48,157
8
90
92

1

The where condition takes care of it. Only schools that are linked to the given user will be selected. – Cerad Dec 17 '15 at 18:05
Okay, you are right. I guess we can avoid having [this discussion](http://stackoverflow.com/a/1018825/1697459) here. My answer was correct too, I wonder why @Slowwie accepted yours since I was first... – Wilt Dec 17 '15 at 18:10

Wilt · Answer 2 · 2015-12-16T08:17:49.253

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

Symfony2 - How to query a left-join with a condition in doctrine 2

2 Answers2