"has_many :through" construct in sqlalchemy

Question

In rails we can simply define relationships with the has_many :through syntax in order to access 2nd, 3rd .. nth degree relations.

In SQLAlchemy however, this seems to be more difficult. I'm trying to avoid going down the route of writing joins, as I find them to be anti-patterns in trying to keep a clean code base.

My tables look like following:

class Message(db.Model):
    __tablename__ = 'message'
    id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.String())
    user_id = db.Column(db.ForeignKey("user.id"))
    user = db.relationship('User', backref="messages")

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String())

class Level(db.Model):
    __tablename__ = 'level'
    number = db.Column(db.Integer, nullable=False, primary_key=True)
    name = db.Column(db.String(), nullable=False, primary_key=True)
    users = db.relationship(
        "User",
        secondary="user_level",
        backref="levels")

class UserLevel(db.Model):
    __tablename__ = 'user_level'
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
    number = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(), primary_key=True)

    __table_args__ = (
        db.ForeignKeyConstraint(
            ['number', 'name'],
            ['level.number', 'level.name']
        ),
    )

The idea is that a user can have multiple authorisation levels (e.g. a user can be at level 1, 3 and 6 at the same time). As the data I have does not contain unique sequence numbers for available levels, I had to resort to the use of composite keys to keep the data consistent with future updates.

To get all messages for a level I can currently do something like this:

users = Level.query[0].users
for user in users:
    results.append(user.messages)
return results

This gives me all users on a level. But in order to get all messages for a certain level, I have to loop through these users and append them to a results list.

What I'd like to do is:

return Level.query[0].users.messages

This is more like the syntax I am used to from rails. How would one accomplish this in flask-SQLAlchemy?

"has_many :through" construct in sqlalchemy

0 Answers