CODEWITHSUNDEEP
symfonydoctrine-orm
C.Astraea
C.Astraea

Reputation: 185

Using left join with query builder doctrine

Having some problems retrieving ManytoOne relationships when using left join.

Before was using this query to query for conferences

$qb = $this->createQueryBuilder('u')
->select('u.id,u.comment,
IDENTITY(u.place) AS place_id,
IDENTITY(u.sponsor) AS sponsor_id,
IDENTITY(u.tour) AS tour_id,
u.startat
');

Now I'm trying to left join with diffusion which is tied to the diffusion in a many to many relationship.

  $qbt = $this->createQueryBuilder('u')
        ->select('u','c')
        ->from('AppBundle:Conference', 'p')
        ->leftJoin('p.diffusion', 'c');

However this query doesn't return the u.place, u.sponsor and u.tour which are ManyToOne relationships.

Upvotes: 0

Views: 7210

Answers (2)

C.Astraea
C.Astraea

Reputation: 185

Found the issue , I had to add ->setHint(\Doctrine\ORM\Query::HINT_INCLUDE_META_COLUMNS, true) to the getQuery , because getArrayResults by default doesn't return foreign keys( the place, sponsor and tour respectivly).

Here is my final query in the conference repository

        $qbt = $this->_em->createQueryBuilder();
        $qbt->select('conference','diffusion')
        ->from('AppBundle:Conference', 'conference')
        ->leftJoin('conference.diffusion', 'diffusion');

    return $qbt
        ->getQuery()
        ->setHint(\Doctrine\ORM\Query::HINT_INCLUDE_META_COLUMNS, true)
        ->useQueryCache(true)
        ->useResultCache(true,3600)
        ->getArrayResult();

Upvotes: 0

Lex Hartman
Lex Hartman

Reputation: 178

leftJoin must be followed by 'WITH'. So for example:

->leftJoin('p.diffusion', 'p', 'WITH', 'p.user=u.id', 'u.id');

But i think it's better to post both your entities so i can give you the exact answer.

Upvotes: 2

Related Questions