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