35

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

1 Answer 1

68

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
            )
    )
5
  • 6
    After searching for hours, Dish.restaurant.any was exactly what I was looking for! +1 to you!
    – Matthew
    Commented Dec 21, 2013 at 9:30
  • 2
    this answer is better than any documentation.
    – user455318
    Commented Feb 20, 2015 at 0:41
  • 1
    What if I need to query all the restaurants that uses a given dish? AKA, the reverse retrieval.
    – Devy
    Commented Jul 7, 2015 at 20:13
  • 2
    Is there any way of doing Dish.restaurants.any(thisrestaurant) when thisrestaurant is a Restaurant intance? I could to Dish.restaurants.any(id=thisrestaurant.id) but it sounds a little tricky Commented Nov 2, 2017 at 19:15
  • 1
    is there any documentation about 'query.any' can't find ... ?
    – mpgn
    Commented Feb 13, 2018 at 18:27

Not the answer you're looking for? Browse other questions tagged or ask your own question.