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?

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.... — Wojciech Jasiński, Feb 04 '12 at 20:36
Wojciech, that is not it, the QueryBuilder has a method named ``join()`` that is basically an alias of ``innerJoin()``. — Quentin, Feb 05 '12 at 22:35

Lighthart · Answer 1 · 2015-06-04T22:38:04.473

3

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

edited Jun 04 '15 at 22:38

answered Feb 06 '15 at 20:58

Lighthart

3,648
6
28
47

score 0 · Answer 2 · answered Apr 29 '13 at 18:10

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;
        }
    }

score 0 · Answer 3 · answered Dec 08 '18 at 11:40

0

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

answered Dec 08 '18 at 11:40

Siva

1,481
1
18
29

score -1 · Answer 4 · answered Nov 26 '12 at 17:53

-1

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

answered Nov 26 '12 at 17:53

versh23

149
2
12

score -1 · Answer 5 · answered Jul 13 '13 at 05:45

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

Doctrine2 Multiple Join works with createQuery but not with queryBuilder

5 Answers5