How to perform a join query using Symfony and Doctrine Query Builder

Question

I have two entities which are connected through a 1:1 relationship, e.g: MyEntity.idRelatedEntity I want to create a Doctrine query where I can retrieve data from MyEntity depending on a value from a certain column in RelatedEntity. Something like this (it doesn't work of course):

$entity = $em
    ->getRepository('MyBundle:RelatedEntity')
    ->createQueryBuilder('e')
    ->leftJoin('MyBundle:RelatedEntity', 'r')
    ->where('r.foo = 1')
    ->getQuery()
    ->getResult();

Any help would be much appreciated :)

Where are you writing this query ? It would help me to answer your question AND improve your code. is that in the MyEntityRepository ? — sf_tristanb
– sf_tristanb, Commented Aug 21, 2013 at 12:23

Uriziel · Accepted Answer · 2013-08-21 12:46:36Z

26

$entity = $em
    ->getRepository('MyBundle:MyEntity')
    ->createQueryBuilder('e')
    ->join('e.idRelatedEntity', 'r')
    ->where('r.foo = 1')
    ->getQuery()
    ->getResult();

Also left join makes no sense here (because of where clause that will make it work like inner join)

edited Aug 21, 2013 at 12:46

answered Aug 21, 2013 at 12:24

Uriziel

7426 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

viarnes Over a year ago

Great! Thank you! Just a small correction, you missed the id part inside the join. It should read ->join('e.idRelatedEntity', 'r')

Zia Over a year ago

can you please tell from where this r come?

Uriziel Over a year ago

->join('e.idRelatedEntity', 'r'), here you define r as an alias, you can put whatever here

Bibhudatta Sahoo Over a year ago

idRelatedEntity is the name of the main table column which related to other table, Ex for product table related field is category so then the join will be ->join('e.category', 'r')

sf_tristanb · Accepted Answer · 2018-03-27 10:37:33Z

18

Note that you should write this query in your MyEntityRepository

public function getMyEntityWithRelatedEntity($parameter) 
{
    $query = $this->createQueryBuilder('e')
        ->addSelect('r') // to make Doctrine actually use the join
        ->leftJoin('e.relatedEntity', 'r')
        ->where('r.foo = :parameter')
        ->setParameter('parameter', $parameter)
        ->getQuery();

    return $query->getResult();
}

And then use it in your controller/service :

$manager = $this->getDoctrine()->getManager();
$results = $manager->getRepository(MyEntity::class)->getMyEntityWithRelatedEntity(1);

edited Mar 27, 2018 at 10:37

answered Aug 21, 2013 at 12:31

sf_tristanb

8,87518 gold badges81 silver badges120 bronze badges

1 Comment

viarnes Over a year ago

Nice one (and cleaner). Will take into account for the next time. For now I'm using @Uriziel answer :) Thank you!

Collectives™ on Stack Overflow

How to perform a join query using Symfony and Doctrine Query Builder

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related