在我的Symfony项目中,我有两个实体Product.php
和Configuration.php
。
这是一种ManyToMany
关系。
这是Product.php
的关系:
/**
* @var DoctrineCommonCollectionsCollection
*
* @ORMManyToMany(targetEntity="Configuration", inversedBy="products")
* @ORMJoinTable(name="product_configuration",
* joinColumns={@ORMJoinColumn(name="product_id", referencedColumnName="product_id", nullable=true)},
* inverseJoinColumns={@ORMJoinColumn(name="configuration_id", referencedColumnName="configuration_id")}
* )
**/
protected $configurations;
这是Configuration.php
:
/**
* Describes the products using $this configuration
*
* @var DoctrineCommonCollectionsCollection
*
* @ORMManyToMany(targetEntity="Product", mappedBy="configurations")
**/
protected $products;
在一个页面中,我需要恢复没有某些配置的产品,例如,FK配置为空。
所以我在Productrepository.php
中创建这个 queryBuilder()/dql :
public function getProductForCurrentWorkType($slug)
{
// First we get all the product even with configurations
$selectProduct = $this->getEntityManager()->createQueryBuilder();
$selectProduct
->select('p, wt, c')
->from('MyBundle:Product', 'p')
->join('p.workType', 'wt', 'WITH', 'wt.id = p.workType')
->join('p.configurations', 'c')
->where('wt.slug = :slug')
->setParameters(array('slug' => $slug));
// Then we apply a filter where configurations is null for product.
// We get all products where product.id are not in ids of the first request
$nullConfiguration = $this->getEntityManager()->createQueryBuilder();
$nullConfiguration
->select('pr.id')
->from('MyBundle:Product', 'pr')
->where($nullConfiguration->expr()->notIn('pr.id', $selectProduct->getDQL()));
return $nullConfiguration->getQuery()->getResult();
}
如果我只返回第一个查询 e-g $selectProduct
,则第一个步骤效果很好。它仅返回具有$slug
配置的产品。但是如果我返回第二个查询(下面的代码),则会发生错误:
2/2 [语法错误] 第 0 行,第 69 行:错误:预期Doctrine\ORM\Query\Lexer::T_FROM, got ','
1/2 查询异常:从我的捆绑包中选择 PR:产品 PR pr.id 不是IN(SELECT p, wt, c FROM MyBundle:Product p INNER JOIN p.workTypewt with wt.id = p.workType INNER JOIN p.configuration c WHERE wt.slug= :蛞蝓)
如何修复此错误?
编辑
我尝试过的另一个解决方案,在我的ProductRepository.php
:
$selectProduct = $this->getEntityManager()->createQueryBuilder();
$selectProduct
->select('p, wt, c')
->from('MyBundle:Product', 'p')
->join('p.workType', 'wt', 'WITH', 'wt.id = p.workType')
->join('p.configurations', 'c')
->having('COUNT(c.id) = 0') // check if the collection is empty
->where('wt.slug = :slug')
->setParameters(array('slug' => $slug));
return $selectProduct
->getQuery()
->getResult();
现在我的控制器,如果它可以帮助你:
$em = $this->getDoctrine()->getManager();
$arrayProduct = $this->getDoctrine()->getRepository('MyBundle:Product')->getProductForCurrentWorkType($slug);
if (!$arrayProduct) {
throw $this->createNotFoundException('Unable to find a Product !');
}
//return statement
下面的代码返回我找不到产品!,如果我删除带有抛出$this->createNotFoundException
的 if 条件,页面显示正确但没有结果。
试试这个:
$qb->select('p')
->from('Product','p')
->leftJoin('p.configurations','c')
->having('COUNT(c.id) = 0') // check if the collection is empty
->groupBy('p.id');