时间戳

首页 > 解决方案 > 执行 SQL 查询以通过多对多关系上的成分 ID 获取鸡尾酒

python - 执行 SQL 查询以通过多对多关系上的成分 ID 获取鸡尾酒

问题描述

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

例如，我希望“Gin and Tonic”行只有在我为 Gin（id 为 1）和 Tonic（id 为 2）提供了正确的 id 时才返回。II只供应“补品”，我不应该回到行列

我正在使用 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'))
)

这个查询让我了解了大部分情况，但这里的问题是，如果我提供鸡尾酒中的任何成分，它会返回“Gin and Tonic”

# 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)

标签： pythonsqlflasksqlalchemy

解决方案

您正在寻找的是一份没有任何缺失成分的鸡尾酒列表，即有NOT EXIST任何缺失的成分。缺少的成分是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，那么您可以制作所需的 Gin 和 Tonic，如果提供的列表中缺少成分 3 或 4，那么您就不能。

推荐阅读