为什么这个nn关系中的查询什么都不返回？(Why does this query in this n

为什么这个nn关系中的查询什么都不返回？(Why does this query in this n-n relation return nothing?)

我正在尝试执行以下查询但没有任何事情发生。 我没有结果。

SELECT * FROM "product" LEFT OUTER JOIN ( "product_filter" INNER JOIN "filter_value" ON "filter_value"."filterValueId" = "product_filter"."filterValueId") ON "product"."productId" = "product_filter"."productId" WHERE "product"."isAdult" = false AND ("filter_value"."filterValueId" = '4' AND "filter_value"."filterValueId" = '8');

我知道问题来自filterValueId ，它必须等于4和8.如果我删除其中一个，我得到结果。 但我需要两者的结果。 filter_value是nn关系的关系表。

查询对我来说是正确的，但没有任何结果。 更糟糕的是， 当我执行LEFT OUTER JOIN查询时，我得到了结果 ：

"product_filter" INNER JOIN "filter_value" ON "filter_value"."filterValueId" = "product_filter"."filterValueId"

我用sequelize建立了以下关系：

db.product.belongsToMany( db.filterValue, { through: db.productFilter, as: 'productFilters', foreignKey: 'productId' }); db.filterValue.belongsToMany( db.product, { through: db.productFilter, as: 'productFilters', foreignKey: 'filterValueId' });

我已经多次检查表格内容，一切都是正确的。 我真的不明白为什么这个查询什么也没有返回？

I'm trying to do the following query but nothing happends. I get no results.

SELECT * FROM "product" LEFT OUTER JOIN ( "product_filter" INNER JOIN "filter_value" ON "filter_value"."filterValueId" = "product_filter"."filterValueId") ON "product"."productId" = "product_filter"."productId" WHERE "product"."isAdult" = false AND ("filter_value"."filterValueId" = '4' AND "filter_value"."filterValueId" = '8');

I know that the problem comes from the filterValueId that has to be equal to 4 and 8. If I remove one of them, I get the result. But I need the result of both. filter_value is a relationnal table for a n-n relation.

The query looks correct to me but nothing come out. What is worse is that when I execute the LEFT OUTER JOIN query, I get results:

"product_filter" INNER JOIN "filter_value" ON "filter_value"."filterValueId" = "product_filter"."filterValueId"

I use sequelize to built the following relation:

db.product.belongsToMany( db.filterValue, { through: db.productFilter, as: 'productFilters', foreignKey: 'productId' }); db.filterValue.belongsToMany( db.product, { through: db.productFilter, as: 'productFilters', foreignKey: 'filterValueId' });

I've check tables content multiple times and everything is correct. I really don't understand why this query returns nothing?

最满意答案

你似乎明白为什么它什么都不返回。 过滤器值不能同时为4和8。

我想你想要这样的东西：

select p.* from "product" p where p.ProductId in (select pf.ProductId from product_filter pf where pf.filterValueId in (4, 8) group by pf.ProductId having count(distinct pf.filterValueId = 2) );

注意：您根本不需要过滤表，因为过滤器值在product_filter 。 （这假设某些类型的过滤不需要它。）

您可能会发现更快地使用两个exists子查询：

select p.* from "product" p where exists (select pf.ProductId from product_filter pf where pf.ProductId = p.ProductId and pf.filterValueId = 4 ) and exists (select pf.ProductId from product_filter pf where pf.ProductId = p.ProductId and pf.filterValueId = 8 );

You seem to understand why it returns nothing. The filter value cannot be both 4 and 8 at the same time.

I think you want something like this:

select p.* from "product" p where p.ProductId in (select pf.ProductId from product_filter pf where pf.filterValueId in (4, 8) group by pf.ProductId having count(distinct pf.filterValueId = 2) );

Note: You do not need the filter table at all, because the filter value is in product_filter. (This assumes it is not needed for some type of filtering.)

You might find the use of two exists subqueries faster:

select p.* from "product" p where exists (select pf.ProductId from product_filter pf where pf.ProductId = p.ProductId and pf.filterValueId = 4 ) and exists (select pf.ProductId from product_filter pf where pf.ProductId = p.ProductId and pf.filterValueId = 8 );