how works createQueryBuilder and leftJoin?

Question

I dont get how to make it work. I have: - a table `partner` with fields `id` and `name` - a table `partner_address` with two fields: `id_partner` and `id_address` - a table `address` with fields `id` and external key `id_town` which references `town(id)` - a table `town` with fields `id`, a `name`, and `postal_code` I want to select all partners that are in towns with specific `postal_code` This query works: ``` SELECT p.nom, v.nom FROM partner p JOIN partner_address pa ON pa.id_partner=p.id JOIN address a ON pa.id_address = a.id JOIN town t ON a.id_town=t.id WHERE t.postal_code='13480'; ``` Now I want to "translate" it into Doctrine 2 full syntax, following the documentation. So I've made a custom repository: `src/Society/Bundle/MyProjectBundle/Repository/PartnerRepository.php` In this repository, I'm trying to create the corresponding function: ``` <?php namespace HQF\Bundle\PizzasBundle\Repository; use Doctrine\ORM\EntityRepository; class PartenaireRepository extends EntityReposito…

Accepted Answer

You have to join only properties, that the selected entity have.

In first parameter of `join()` or `leftJoin()` or `xxxJoin()` you pass the attribute name related to selected object, and in the second - alias for joined entity.

Try similar to this:

```
$q = $this->em()->createQueryBuilder();

$q->select(['item', 'itemContact'])
  ->from('ModuleAdmin\Entity\CustomerEntity', 'item')
  ->leftJoin('item.contacts', 'itemContact')
  ->andWhere($q->expr()->like('item.name', ':customerNameStart'));
```

Of course, the `CustomerEntity` contains `OneToMany` relation in field `contacts`.

Remember, that in select statement you have to select the root entity (in my example `CustomerEntity` aliased as `item`).

Edit by Olivier Pons to add how I found out the solution, and to mark this answer as valid, because it put me on the right track, thank you Adam!

In the file `PartenaireRepository.php` I've used the `createQueryBuilder('p')` properly. Here's how to make two joins in a row, using `createQueryBuilder()`:

```
class PartenaireRepository extends EntityRepository
{

/** 
     * Retrieval of all partners given for a given postal code.
     */
    public function findAllActiveByCp($cp)
    {   
        return $this->createQueryBuilder('p')
            ->leftJoin('p.adresses', 'a')
            ->leftJoin('a.ville', 'v')
            ->where('v.cp=:cp')
            ->setParameter('cp', $cp);
    ... blabla
    }
}
```

how works createQueryBuilder and leftJoin?

Solution

Problem