I dont get how to make it work.
I have:
- a table
partner
with fieldsid
andname
- a table
partner_address
with two fields:id_partner
andid_address
- a table
address
with fieldsid
and external keyid_town
which referencestown(id)
- a table
town
with fieldsid
, aname
, andpostal_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
You have to join only properties, that the selected entity have.
In first parameter of
join()
orleftJoin()
orxxxJoin()
you pass the attribute name related to selected object, and in the second - alias for joined entity.Try simmilar to this:
Of course, the
CustomerEntity
containsOneToMany
relation in fieldcontacts
.Remember, that in select statement you have to select the root entity (in my example
CustomerEntity
aliased asitem
).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 thecreateQueryBuilder('p')
properly. Here's how to make two joins in a row, usingcreateQueryBuilder()
:I believe for what you're doing, you will need to provide four arguments to the
leftJoin
method.So your query builder chain should look like this