Doctrine2 Multiple Join works with createQuery but not with queryBuilder

Question

If I'm using querying without queryBuilder with this dql

$query = $this->_em
  ->createQuery("SELECT p, g, c
            FROM LikeYeah\GoBundle\Entity\Product p
            JOIN p.garments g
            LEFT JOIN g.colours c
            ORDER BY p.id DESC
          ");

everything is fine, but if I use the (what I belive is the same) query trough query builder like this

 $qb->select('p, g, c')
    ->from('LikeYeah\GoBundle\Entity\Product', 'p')
    ->join('p.garments', 'g')
    ->leftJoin('g.colours', 'c')
    ->orderBy('p.id', 'desc');

I get the following error:

"Semantical Error] line 0, col 66 near '.colours c, LikeYeah\GoBundle\Entity\Product': Error: Identification Variable g used in join path expression but was not defined before."

What am I missing?

Menjivar

I can't see 'join' in documentation, there is 'leftJoin' and 'innerJoin' only. Maybe the problem is that your 'join' is actually a leftJoin and for some rows it doesn't define 'g' cause there is nothing to join.... – Sherfield

Wojciech, that is not it, the QueryBuilder has a method named join() that is basically an alias of innerJoin(). – Bravery

Answer 1

Try this: using addSelect after your joins:

 $qb->select('p')
    ->join('p.garments', 'g')
    ->addSelect('g')
    ->from('LikeYeah\GoBundle\Entity\Product', 'p')
    ->join('p.garments', 'g')
    ->leftJoin('g.colours', 'c')
    ->addSelect('c')
    ->orderBy('p.id', 'desc');

Decoration

Answer 2

You can help from this method

 public function findSampleClothingTypeGender($gender) {
        $query = $this->getEntityManager()
                        ->createQuery('
            SELECT p FROM Acme:Product p 
            JOIN p.clothing_type ct
            WHERE p.gender = :gender'
                        )->setParameter('gender', $gender);

        try {
            return $query->getResult();
        } catch (\Doctrine\ORM\NoResultException $e) {
            return null;
        }
    }

Billon

Answer 3

Try with below one

 $qb->select('p','g','c')
      ->from(array('LikeYeah\GoBundle\Entity\Product','p'))
      ->join(array('p.garments','g'))   
      ->join(array('g.colours','c'),'LEFT')
      ->order_by('p.id','DESC'); 

Youngs

Answer 4

may be try

  $qb->select('p, g, c')
     ->from('GoBundle:Product', 'p')
     ->join('p.garments', 'g')
     ->leftJoin('g.colours', 'c')
     ->orderBy('p.id', 'desc');

show your $qb init and DQL result

Tintinnabulation

Answer 5

It works for me.

$this->_em->createQueryBuilder()
         ->select('fu,e,t')
         ->from('\xxxx\AdminBundle\Entity\FrontUser','fu')
         ->join('fu.read_essays','e')
         ->leftJoin('e.tags','t')
         ->getQuery()->execute();

I think you should create a new QueryBuilder object.

You can use follow code to see Dql of that QueryBuilder

$qb->getDQL();

Fiord