在同一个多对多表上使用多个“IN”的SQL查询

I've got m2m relationship like this:

我有这样的m2m关系:

#main table
CREATE TABLE products_product (
    id integer NOT NULL,
    company_id integer,
    user_id integer,
    type_id integer NOT NULL,
    name character varying(100) NOT NULL,
    description character varying(200) NOT NULL,
    tags character varying(255) NOT NULL,
    image character varying(200) NOT NULL
);
#intermediate table
CREATE TABLE products_ingridientbound (
    id integer NOT NULL,
    ingridient_id integer NOT NULL,
    company_id integer NOT NULL,
    price double precision NOT NULL,
    active boolean NOT NULL,
    "asTopping" boolean NOT NULL
);
#final m2m table
CREATE TABLE products_ingridientproductbound (
    id integer NOT NULL,
    product_id integer NOT NULL,
    ingridient_id integer NOT NULL,
    "optionValue" integer NOT NULL,
    CONSTRAINT "products_ingridientproductbound_optionValue_check" CHECK (("optionValue" >= 0))
);

All I want to do is to get products, which has 2 (in this example) ingridient groups, one with ID in range (16, 17, 18, 19), and another in range (43, 44, 45). I want ingridient ID to be in both groups simultaneously.

我想做的就是获得产品,其中有2个(在这个例子中)为ingridient组,一个ID在范围内(16,17,18,19),另一个在范围内(43,44,45)。我希望ingridient ID同时在两个组中。

My query looks like this (it's actually generated by django orm):

我的查询看起来像这样(它实际上是由django orm生成的):

SELECT "products_product"."id","products_product"."name",
FROM "products_product"
INNER JOIN "products_ingridientproductbound" 
ON ("products_product"."id" = "products_ingridientproductbound"."product_id")
WHERE ("products_ingridientproductbound"."ingridient_id" IN (16, 17, 18, 19)
AND "products_ingridientproductbound"."ingridient_id" IN (43, 44, 45)) LIMIT 21

It gives me 0 results, but if I run query with only one group of IN queries than it works!

它给了我0结果,但如果我只用一组IN查询运行查询而不是它的工作!

Here is data in my "products_ingridientproductbound" table. I thought that my query could return product 3, as it is in both groups (16 and 45), but now I'm confused a bit. Screenshot of phpPgAdmin

这是我的“products_ingridientproductbound”表中的数据。我认为我的查询可以返回产品3,因为它在两个组(16和45)中,但现在我有点困惑。 phpPgAdmin的屏幕截图

4 个解决方案

#1

If I understand what you are after, doing an INNER JOIN is not what you are looking for here, I would use a subquery instead.

如果我理解你所追求的是什么,那么做INNER JOIN并不是你想要的,我会使用子查询。

You want the product id and name that have both ingredients, correct?

您想要具有这两种成分的产品ID和名称,对吗?

I didn't test this statement but it should be something like this:

我没有测试这个语句,但它应该是这样的:

SELECT "products_product"."id","products_product"."name",
FROM "products_product"
WHERE (
    EXISTS (SELECT 1 FROM "products_ingridientproductbound" WHERE "products_product"."id" = "products_ingridientproductbound"."product_id" AND "products_ingridientproductbound"."ingridient_id" IN (16, 17, 18, 19))
        AND
    EXISTS (SELECT 1 FROM "products_ingridientproductbound" WHERE "products_product"."id" = "products_ingridientproductbound"."product_id" AND "products_ingridientproductbound"."ingridient_id" IN (43, 44, 45))
)
LIMIT 21

Edit: remove silly remark about combining, thanks jvanderh.

编辑:删除关于组合的愚蠢评论,谢谢jvanderh。

#2

You ask in your two IN clauses that the same field is in two sets without common elements. Therefore you will always get a false in one of the clauses, hence your AND will be false.

你在两个IN条款中询问同一个字段是两个没有共同元素的集合。因此,您将始终在其中一个条款中获得错误,因此您的AND将是错误的。

#3

txwikinger is right. If you want to filter product that have two related ingridientbounds you need to have two JOINs in the query like this:

txwikinger是对的。如果要筛选具有两个相关ingridientbounds的产品,则需要在查询中包含两个JOIN,如下所示:

SELECT "products_product"."id","products_product"."name",
FROM "products_product"
INNER JOIN "products_ingridientproductbound" ig1
ON ("products_product"."id" = ig1."product_id")
INNER JOIN "products_ingridientproductbound" ig2
ON ("products_product"."id" = ig2."product_id")
WHERE (ig1."ingridient_id" IN (16, 17, 18, 19)
AND ig2."ingridient_id" IN (43, 44, 45)) 
LIMIT 21

#4

In SQL Server you can use INTERSECT like this

在SQL Server中,您可以像这样使用INTERSECT

SELECT "products_product"."id","products_product"."name",
FROM "products_product"
INNER JOIN "products_ingridientproductbound" 
ON ("products_product"."id" = "products_ingridientproductbound"."product_id")
WHERE ("products_ingridientproductbound"."ingridient_id" IN (16, 17, 18, 19)

INTERSECT 

SELECT "products_product"."id","products_product"."name",
FROM "products_product"
INNER JOIN "products_ingridientproductbound" 
ON ("products_product"."id" = "products_ingridientproductbound"."product_id")
WHERE ("products_ingridientproductbound"."ingridient_id" IN (43, 44, 45)

Meaning return rows that are in both queries. Check if your DB has that option.

含义返回两个查询中的行。检查您的数据库是否具有该选项。

#1