执行SQL查询以按manytomy关系上的配料ID获取鸡尾酒

Question

我需要帮助创建一个SQL查询，该查询将从数据库返回鸡尾酒，因为我已经提供了鸡尾酒中的所有成分

例如，我希望“Gin and Tonic”行仅在为Gin（id为1）和Tonic（id为2）提供了正确的id时返回。我只提供“补药”，我不应该再回到那排

我使用的是SQLAlchemy和Flask，但是我仍然不能完全理解查询的工作方式

这就是我的表结构

+-------------------+
| Tables_in_my_database |
+-------------------+
| cocktails         |
| ing_in_cocktails  |
| ingredients       |
+-------------------+

这是我的鸡尾酒桌

+----+----------------+-------+---------+
| id | name           | glass | finish  |
+----+----------------+-------+---------+
|  1 | white russisan | rocks | stirred |
|  2 | gin and tonic  | rocks | stirred |
+----+----------------+-------+---------+

这是我的配料表

+----+---------+----------+
| id | name    | ing_type |
+----+---------+----------+
|  1 | vodka   | fruit    |
|  2 | kahluha | fruit    |
|  3 | gin     | fruit    |
|  4 | tonic   | fruit    |
+----+---------+----------+

这是我的关系表

+----+-------------+--------+
| id | cocktail_id | ing_id |
+----+-------------+--------+
|  1 |           1 |      1 |
|  2 |           1 |      2 |
|  3 |           2 |      3 |
|  4 |           2 |      4 |
+----+-------------+--------+

下面是相应的SQLAlchemy模型

class Cocktail(db.Model):
    __tablename__ = 'cocktails'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)
    glass = db.Column(db.String(20), nullable=False)
    finish = db.Column(db.String(20), nullable=True)
    ingredients = db.relationship(
        'Ingredient',
        secondary=ing_in_cocktails,
        backref=db.backref('cocktails', lazy='dynamic')
    )

class Ingredient(db.Model):
    __tablename__ = 'ingredients'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), nullable=False)
    ing_type = db.Column(db.String(20), nullable=False)


ing_in_cocktails = db.Table(
    'ing_in_cocktails',
    db.Column('id', db.Integer, primary_key=True),
    db.Column('cocktail_id', db.Integer, db.ForeignKey('cocktails.id')),
    db.Column('ing_id', db.Integer, db.ForeignKey('ingredients.id'))
)

这个问题在很大程度上帮助了我，但这里的问题是，如果我提供鸡尾酒中的任何成分，它就会返回“杜松子酒和补品”

# problematic because this returns "Gin and Tonic," despite not passing
# all the ingredients
Cocktail.query.join(ing_in_cocktails).filter(ing_in_cocktails.columns.ing_id.in_([3]))

上面的查询转换成这个SQL

SELECT cocktails.id AS cocktails_id, cocktails.name AS cocktails_name, cocktails.glass AS cocktails_glass, cocktails.finish AS cocktails_finish
FROM cocktails INNER JOIN ing_in_cocktails ON cocktails.id = ing_in_cocktails.cocktail_id
WHERE ing_in_cocktails.ing_id IN (%(ing_id_1)s)

Answer 1

你要找的是一份没有任何缺失成分的鸡尾酒清单，也就是说没有任何缺失成分。缺少的成分是那些NOT IN你的可用成分列表。有了这些观察结果，满足您需求的查询可以这样写：

select c.* from Cocktails c
 where not exists (select 1 from ing_in_cocktails r
                    where r.cocktail_id = c.id
                      and r.ing_id not in (3,4));

如果提供的配料表是3，4，那么你可以做你想要的杜松子酒和补品，如果提供的配料表中缺少3或4，那么你不能

Answer 2

我不能告诉你，如何用python编写它，但是这里有sql，你需要它，我希望它会有所帮助

select
     ic.cocktail_id
from ing_in_cocktails ic
left join (
    -- here are you ingredients, that you have to pass as parameters.
    -- keep in mind, that they must be unique
    select 1 as ing_id
    union all select 2
    union all select 4
) ing_p on ic.ing_id = ing_p.ing_id
group by ic.cocktail_id
having count(*) = count(ing_p.ing_id) -- just need to check here, that amount of ingredients in cocktail is equal to ingredients, you passed as parameters

Answer 3

对于那些想用炼金术来做这件事的人来说

      query = db.session.query(Cocktail).filter(
          ~exists().where(
              and_(
                  CocktailIngredient.cocktail_id == Cocktail.id,
                  ~CocktailIngredient.ing_id.in_([15, 17])
              )
          )
      )