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

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

Leave a Comment

tech