createQueryBuilder和leftJoin如何工作? [英] how works createQueryBuilder and leftJoin?
问题描述
我不知道如何使它工作。
I dont get how to make it work.
我有:
- 表
partner
,其中字段id
和name
- 表
partner_address
,其中包含两个字段:id_partner
和id_address
- 表
address
的字段id
和引用town(id)
的外键 - a表
town
,其中的字段为id
,名称
和postal_code
id_town
- 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
我想选择城镇中所有特定 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';
现在我要按照文档。
所以我做了自定义存储库:
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);
}
我收到此错误:
警告:缺少Doctrine\ORM\QueryBuilder :: leftJoin()的参数2,在
中调用了
/blabla/Repository/PartenaireRepository.php $ b第18行中的$ b,并在
中定义/bblabla/symfony/vendor/doctrine/orm/lib/Doctrine/ORM/QueryBuilder.php
第767行
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.
在 join()
或 leftJoin()
或 xxxJoin()$ c的第一个参数$ c>传递与所选对象相关的属性名称,第二个-表示已连接实体的别名。
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.
尝试与此类似:
$q = $this->em()->createQueryBuilder();
$q->select(['item', 'itemContact'])
->from('ModuleAdmin\Entity\CustomerEntity', 'item')
->leftJoin('item.contacts', 'itemContact')
->andWhere($q->expr()->like('item.name', ':customerNameStart'));
当然, CustomerEntity
包含<$ 联系人
字段中的c $ c> OneToMany 关系。
Of course, the CustomerEntity
contains OneToMany
relation in field contacts
.
记住,在选择中语句,您必须选择根实体(在我的示例中, CustomerEntity
别名为 item
)
Remember, that in select statement you have to select the root entity (in my example CustomerEntity
aliased as item
).
由Olivier Pons编辑,以添加我找到解决方案的方式,并将此答案标记为有效,因为它让我走上正确的轨道,谢谢亚当!
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!
在文件 PartenaireRepository.php
中,我使用了 createQueryBuilder('p')
正确。以下是使用 createQueryBuilder()
连续进行两个联接的方法:
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
}
}
这篇关于createQueryBuilder和leftJoin如何工作?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!