教义ManyToMany查询 [英] Doctrine ManyToMany Query

问题描述

我有一个产品实体与Taxon Entity具有ManyToMany关系。我想找到属于所有分类的所有产品。例如，我想查找属于ID 1和2的分类群的所有产品。

 产品
 {1， 2,3} 
 
 Taxons 
 {1,2,3,4,5} 
 
 ProductsToTaxons 
 {{p1，t1}，{p1 ，t2}，{p2，t2}} 
  

只有当查询产品时，我才能检索以下集合对于分类群1和2：

 产品
 {1} 
来自{{p1，t1 }，{p1，t2}} 
  

好的，所以这里是我试过的DQL但是它不起作用？

  SELECT p FROM SRCProductBundle：Product p 
 JOIN p.taxons t 
 WHERE t.id = 1 AND t.id = 2 
  

（PS我也会这样做QueryBuilder）

编辑

为了澄清，这里是SQL我想翻译成DQL / QueryBuilder。

 从产品中选择p.id 
 p 
存在的地方（select product_id 
f rom product_to_taxon 
其中taxon_id = 1 
和product_id = p.id）
并存在（从product_to_taxon 
中选择product_id 
其中taxon_id = 4 
和product_id = p.id）; 
  

解决方案

由于缺乏干净的方法， ，经过相当多的研究，我在Native SQL中这样做。 原则允许本机SQL通过EntityManager与createNativeQuery （）。

所以简而言之，我利用这个能力，将我的问题中包含的SQL查询构造成一个字符串，然后传递给createNativeQuery（）函数。 / p>

这似乎有一些缺点，因为它似乎我将无法使用KnpPaginatorBundle ...所以我可能最终只是过滤PHP中的结果，而不是SQL，我犹豫了，我认为有性能上的缺点。

I have a Product Entity which has a ManyToMany relationship with a Taxon Entity. I want to find all the products that belong to the intersection of all Taxons. For instance I want to find all products that belong to taxons with IDs 1 and 2.

Products
{1,2,3}

Taxons
{1,2,3,4,5}

ProductsToTaxons
{{p1,t1},{p1,t2}, {p2,t2}}

I want to retrieve the following set ONLY when querying products for taxons 1 and 2:

Product
{1} 
which is from {{p1,t1}, {p1,t2}}

Okay, So here is the DQL that i tried... but it doesn't work?

SELECT p FROM SRCProductBundle:Product p
JOIN p.taxons t 
WHERE t.id = 1 AND t.id = 2

(P.S. I would also do this with QueryBuilder with as well)

EDIT

To clarify, here is the SQL that I would like to translate into DQL/QueryBuilder.

select p.id 
from product p 
where exists (select product_id 
              from product_to_taxon 
              where taxon_id = 1 
              and product_id = p.id) 
and exists (select product_id 
            from product_to_taxon 
            where taxon_id = 4 
            and product_id = p.id);

解决方案

For lack of a clean way to do this with DQL, and after a considerable amount of research, I resorted to doing this in Native SQL. Doctrine allows Native SQL via the EntityManager with createNativeQuery().

So in short, I utilized this ability and constructed the SQL query included in my question as a string and then passed it to the createNativeQuery() function.

This does appear to have some drawbacks as it appears I will be unable to use the KnpPaginatorBundle with it... So I might end up just filtering the results in PHP rather than SQL, which I'm hesitant to do as I think there are performance drawbacks.

这篇关于教义ManyToMany查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！