CODEWITHSUNDEEP
pythonsqlsqlalchemyflask-sqlalchemy
Jessi
Jessi

Reputation: 1468

How do I query an association table in SQLAlchemy?

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?

Upvotes: 26

Views: 29299

Answers (4)

dignitas123
dignitas123

Reputation: 101

It's also possible to use a select_from statement.

This is for when you have declared your association table as an object and are using flask_user:

from flask_user import UserMixin
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()


class User(db.Model, UserMixin):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)

    name = db.Column(db.String(100, collation='NOCASE'),
                       nullable=False, server_default='')
    password = db.Column(db.String(255), nullable=False, server_default='')

    roles = db.relationship('Role', secondary='user_roles')


class Role(db.Model):
    __tablename__ = 'roles'
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(50), unique=True)


# Associatin Table:
class UserRoles(db.Model):
    __tablename__ = 'user_roles'
    id = db.Column(db.Integer(), primary_key=True)
    user_id = db.Column(db.Integer(), db.ForeignKey('users.id',
                                                    ondelete='CASCADE'))
    role_id = db.Column(db.Integer(), db.ForeignKey('roles.id',
                                                    ondelete='CASCADE'))

You can then query the user roles like this:

from app.db_model import db, Broker, UserRoles
from flask_user import current_user

user_id = current_user.id

all_user_roles =\
    (db.session.query(Role.name)
     .select_from(UserRoles, Role)
     .join(UserRoles)
     .filter(UserRoles.user_id == user_id)
     .all())

EDIT: Ok, it's even simpler with current_user

# a list of all Role objects in roles
current_user.roles

# print out all names
print([role.name for role in current_user.roles])

Upvotes: 2

Kim Skovhus Andersen
Kim Skovhus Andersen

Reputation: 388

Very similar to the selected answer, but maybe helpful for some people with some more examples:

result = session.query(User.first_name, User.last_name, Role.name, roles_users).filter(
        roles_users.c.user_id == User.id).filter(roles_users.c.role_id == Role.id).all()

Upvotes: 9

Gera Zenobi
Gera Zenobi

Reputation: 1440

I think if your DAO codebase is ORM oriented then maybe you should try to rethink your scenario and take advantage of the sqlalchemy relationship you had defined given it will take care of necessary joins for you in order to query users' roles. According to your example, something like:

users = db.session.query(User).all()
for user in users:
  for role in user.roles:
    print((user.first_name, user.last_name, role.name))

Upvotes: 5

Jessi
Jessi

Reputation: 1468

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'

Upvotes: 41

Related Questions