Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SqlAlchemy and Flask, how to query many-to-many relationship

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

like image 556
cancerballs Avatar asked Sep 26 '12 01:09

cancerballs


People also ask

How do you make a many to many relationship in Flask?

You add a tags class variable to the Post model. You use the db. relationship() method, passing it the name of the tags model ( Tag in this case). You pass the post_tag association table to the secondary parameter to establish a many-to-many relationship between posts and tags.

How do you query a one to many relationship in Flask?

The comments class attribute defines a One-to-Many relationship between the Post model and the Comment model. You use the db. relationship() method, passing it the name of the comments model ( Comment in this case). You use the backref parameter to add a back reference that behaves like a column to the Comment model.

How do you create a one to many relationship in Flask-SQLAlchemy?

Flask Flask-SQLAlchemy Relationships: One to Many In order to achieve that we place a Foreign key on the child referencing the parent that is from our example we place a foreign key on Post class to reference the User class. We then use relationship() on the parent which we access via our SQLAlchemy object db .


1 Answers

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             )     ) 
like image 86
sayap Avatar answered Sep 20 '22 22:09

sayap