How to query a many to many relationship with SQLAlchemy?


November 2018


293 time


In my Flask app I have the following database structure. Every user can have multiple spaces. User can also share a space.

space_access = db.Table('space_access',
    db.Column('userid', db.Integer, db.ForeignKey('')),
    db.Column('spaceid', db.Integer, db.ForeignKey('')))

class User(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(15), unique=True)
    email = db.Column(db.String(50), unique=True)
    password = db.Column(db.String(80))

    spaces = db.relationship('Space', secondary=space_access, backref=db.backref('user'))

class Space(UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), unique=True)
    type = db.Column(db.String(50), unique=True)

Now I would like to get all the spaces of the user with the id=1 and display them on my HTML page. I did it like in the following but nothing happens.


quer = Space.query.filter(Space.user.any(id=1)).all()
return render_template('test.html', spaces=quer)


{% for Space in spaces %}
    {% if %}{{ }}{% endif %}
{% endfor %}

However doing it like this doesn't work. Can you help me? Thanks.

0 answers