how works createQueryBuilder and leftJoin?

2019-06-04 01:46发布

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 EntityRepository
{
    /**
     * Get all active partners from a given postal code.
     */
    public function findAllActiveByCp($cp)
    {
        return $this->createQueryBuilder('p')
            ->where('p.dateVFin IS NULL')
            ->andWhere('p.cp=:cp')
            ->addOrderBy('p.cp', 'DESC')
            ->setParameter('cp', $cp);
    }
}

Nota: the query in the code is not the right one but this code works in another custom repository I've made, so I'm trying to start from this code.

I'm trying something like this but it doesn't work:

public function findAllActiveByCp($cp)
{
    $qb = $this->createQueryBuilder('p');
    return $qb
        ->leftJoin('partner_address pa ON pa.id_partner=p.id')
        ->leftJoin('address a ON pa.id_address = a.id')
        ->leftJoin('town t ON a.id_ville=t.id')
        ->where('p.dateVFin IS NULL')
        ->andWhere('t.cp=:cp')
        ->addOrderBy('t.cp', 'DESC')
        ->setParameter('cp', $cp);
}

I get this error:

Warning: Missing argument 2 for Doctrine\ORM\QueryBuilder::leftJoin(), called in /blabla/Repository/PartenaireRepository.php on line 18 and defined in /blabla/symfony/vendor/doctrine/orm/lib/Doctrine/ORM/QueryBuilder.php line 767

2条回答
聊天终结者
2楼-- · 2019-06-04 02:32

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 simmilar 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
{

    /** 
     * Récupération de tous les partenaires donnés pour un
     * code postal donné.
     */
    public function findAllActiveByCp($cp)
    {   
        return $this->createQueryBuilder('p')
            ->leftJoin('p.adresses', 'a')
            ->leftJoin('a.ville', 'v')
            ->where('v.cp=:cp')
            ->setParameter('cp', $cp);
    ... blabla
    }
}
查看更多
做自己的国王
3楼-- · 2019-06-04 02:35

I believe for what you're doing, you will need to provide four arguments to the leftJoin method.

->leftJoin('partner_address', 'pa', 'ON', 'pa.id_partner = p.id')

So your query builder chain should look like this

public function findAllActiveByCp($cp)
{
    $qb = $this->createQueryBuilder('p');
    return $qb
        ->leftJoin('partner_address', 'pa', 'ON', 'pa.id_partner = p.id')
        ->leftJoin('address', 'a', 'ON', 'pa.id_address = a.id')
        ->leftJoin('town', 't', 'ON', 'a.id_ville = t.id')
        ->where('p.dateVFin IS NULL')
        ->andWhere('t.cp=:cp')
        ->addOrderBy('t.cp', 'DESC')
        ->setParameter('cp', $cp)
    ;
}
查看更多
登录 后发表回答