How do I query an association table in SQLAlchemy?

Question

I'm trying to convert an SQL query into an SQLAlchemy query to user inside get API. The problem is that I cannot query anything from the association table. (I'm sure I don't know the method).

ORM:

roles_users = db.Table(
    'roles_users',
    db.Column('user_id', db.Integer(), db.ForeignKey('user.id')),
    db.Column('role_id', db.Integer(), db.ForeignKey('role.id'))
     )

class Role(db.Model, RoleMixin):
   id = db.Column(db.Integer(), primary_key=True)
   name = db.Column(db.String(50), unique=True)
   description = db.Column(db.String(255))

   def __str__(self):
      return self.name

class User(db.Model, UserMixin):
   id = db.Column(db.Integer, primary_key=True)
   first_name = db.Column(db.String(50))
   last_name = db.Column(db.String(50))
   email = db.Column(db.String(50), unique=True)
   password = db.Column(db.String(255))
        .
        .
        .
   roles = db.relationship('Role', secondary=roles_users,
                        backref=db.backref('users', lazy='dynamic'))

   def __str__(self):
       return self.email

Working SQL query:

select first_name, last_name, role.name from user 
    join roles_users 
       join role on user.id = roles_users.user_id 
           and role.id = roles_users.role_id;

SQLAlchemy query that I'm having trouble with:

roles_users.query.join(Role).join(User)
   .filter(roles_users.user_id == User.id and 
       roles_users.role_id == Role.id).all()

Error that I'm getting using above SQLAlchemy query:

AttributeError: 'Table' object has no attribute 'query'

How do I perform the equivalent of my SQL query using SQLAlchemy?

Answer 1

Ok, so the key to querying association object in Flask-Sql alchemy is to make an external join to roles_users. Try to join all tables first and then filter afterwards. I'm posting the answer below.

query_user_role = User.query.join(roles_users).join(Role).
filter((roles_users.c.user_id == User.id) & (roles_users.c.role_id == Role.id)).all()

Do not forget to put 'c' when querying association table object. Without it, it won't work.

One more thing, do not forget to set backref lazy = 'joined'