Clash Royale CLAN TAG#URR8PPP

Flask-SQLAlchemy: How to query according to User's role and team?

I am able to list the users as reviewer with Role.id == 4, and can be selected by the current user:

Role.id == 4

def reviewer_choices():
return User.query.join(User.roles).filter(Role.id == 4)

form_extra_fields =
'reviewer1': sqla.fields.QuerySelectField(
label='Reviewer1',
query_factory=reviewer1_choices,
)


Now, how can I query the users are with Role.id == 4 and Team.id ==
current_user.teams.id? (Restrict reviewer and current user are with same team)

Role.id == 4

Team.id ==
current_user.teams.id

I've tried below but in vain:

User.query.join(User.roles).join(User.teams).
filter(Role.id == 4).filter(Team.id = current_user.teams.id)


The classes are defined as below, thanks:

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

def __str__(self):
return self.name

class Team(db.Model, RoleMixin):
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(80), 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)
roles = db.relationship('Role', secondary=roles_users,
backref=db.backref('users', lazy='dynamic'))
teams = db.relationship('Team', secondary=teams_users,uselist=False,
backref=db.backref('users', lazy='dynamic'))
email = db.Column(db.String(255), unique=True)

def __str__(self):
return self.email

class Project(db.Model):

id = db.Column(db.Integer, primary_key=True)
reviewer = db.Column(db.Unicode(128))

def __unicode__(self):
return self.name


1 Answer
1

UPDATE:

You should define your User model as follows:

# -> 1. many to one

class User(db.Model):
# ...
team_id = db.Column(db.Integer, db.ForeignKey("team.id"))
team = db.relationship("Team", foreign_keys=team_id)
# ...

User.query.filter(Role.id == 4, Team.id == current_user.team_id).all()

# -> 2. one to one

class User(db.Model):
# ...
team_id = db.Column(db.Integer, db.ForeignKey("team.id"))
team = db.relationship("Team", foreign_keys=team_id, uselist=False)
# ...

User.query.filter(Role.id == 4, Team.id == current_user.team_id).all()

# -> 3. many to many

users_teams = db.Table(db.Column("user_id", db.Integer(), db.ForeignKey("user.id"),
db.Column("role_id", db.Integer(), db.ForeignKey("team.id"))

class User(db.Model):
# ...
teams = db.relationship("Team", secondary=users_teams)
# ...

User.query.filter(Role.id == 4, User.teams.any(Team.id.in_([team.id for team in current_user.teams]))).all()


uselist is used to define a one to one relationship.

uselist

secondary is used to define many to many relationship.

secondary

TypeError: 'Team' object is not iterable

User.query.filter(User.roles.any(Role.id.in_([1,2]))).all()

uselist=False

Team

User

Team

User

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.