SqlAlchemy和Flask，如何查询多对多的关系 [英] SqlAlchemy and Flask, how to query many-to-many relationship

问题描述

我需要帮助创建SqlAlchemy查询。

我正在做一个Flask项目，使用SqlAlchemy。我在我的models.py文件中创建了3个表格：Restaurant，Dish和restaurant_dish。

  restaurant_dish = db.Table（'restaurant_dish '，
 db.Column（'dish_id'，db.Integer，db.ForeignKey（'dish.id'）），
 db.Column（'restaurant_id'，db.Integer，db.ForeignKey 'restaurant.id'））
）
 
 class Restaurant（db.Model）：
 id = db.Column（db.Integer，primary_key = True）
 name = db.Column（db.String（64），index = True）
 
 restaurant_dish = db.relationship（'Dish'，secondary = restaurant_dish，
 backref = db.backref（' （db.Model）：
 id = db.Column（db.Integer，primary_key = True）
 
 $ b $ $ b name = db.Column（db.String（64），index = True）
 info = db.Column（db.String（256），index = True）
  

我已经将数据添加到了restaurant_dish表中，并且它应该正常工作。我需要帮助的是了解如何正确使用餐厅的菜。原始SQL应该是这样的：

  SELECT dish_id FROM restaurant_dish WHERE restaurant_id == id 
  
  $ b 
 
 code> x = Restaurant.query.filter_by（Restaurant.restaurant_dish.contains（name））。all（）
  
感谢您的帮助，我也很感激教程，可以指向正确的方向（官方文档覆盖了我的头）。
 
解决方案
关系的语义看起来不正确。我认为它应该是这样的：
 
 $ p $ 类Restaurant（db.Model）：
 ... 
 
 dishes = db.relationship（'Dish'，secondary = restaurant_dish，
 backref = db.backref（'restaurants'））
   
 
 
 <$ c $所有（）
  
这应该产生一个查询，如： 
 
 
  SELECT dish。* 
 FROM dish 
 WHERE 
 EXISTS（
 SELECT 1 
 FROM restaurant_dish 
 WHERE 
 dish.id = restaurant_dish.dish_id 
 AND EXISTS（
 SELECT 1 
 FROM餐馆
 WHERE 
 restaurant_dish.restaurant_id = restaurant.id 
 AND restaurant.name =：name 
）
）
  
 
I need help creating SqlAlchemy query.


I'm doing a Flask project where I'm using SqlAlchemy. I have created 3 tables: Restaurant, Dish and restaurant_dish in my models.py file.
restaurant_dish = db.Table('restaurant_dish',
    db.Column('dish_id', db.Integer, db.ForeignKey('dish.id')),
    db.Column('restaurant_id', db.Integer, db.ForeignKey('restaurant.id'))
)

class Restaurant(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(64), index = True)

    restaurant_dish = db.relationship('Dish', secondary=restaurant_dish,
        backref=db.backref('dishes', lazy='dynamic'))


class Dish(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(64), index = True)
    info = db.Column(db.String(256), index = True)
I have added data to the restaurant_dish table and it should be working correctly. Where I need help is understanding how to correctly get a Dish using Restaurant. Raw SQL would be something like this: 
SELECT dish_id FROM restaurant_dish WHERE restaurant_id == id
What I have managed to get done but not working:
x = Restaurant.query.filter_by(Restaurant.restaurant_dish.contains(name)).all()
Thanks for help and I also appreciate tutorials that can point me in the right direction(the official documentation goes over my head).
解决方案
The semantic of the relationship doesn't look right. I think it should be something like:
class Restaurant(db.Model):
    ...

    dishes = db.relationship('Dish', secondary=restaurant_dish,
        backref=db.backref('restaurants'))
Then, to retrieve all the dishes for a restaurant, you can do:
x = Dish.query.filter(Dish.restaurants.any(name=name)).all()
This should generate a query like:
SELECT dish.*
FROM dish
WHERE
    EXISTS (
        SELECT 1
        FROM restaurant_dish
        WHERE
            dish.id = restaurant_dish.dish_id
            AND EXISTS (
                SELECT 1
                FROM restaurant
                WHERE
                    restaurant_dish.restaurant_id = restaurant.id
                    AND restaurant.name = :name
            )
    )


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