Wednesday, 7 April 2021

User permission sqlalchemy.orm.exc.MultipleResultsFound: Multiple rows were found for one()

I can use below code to control the authority of menu 15,the role_id's is_edit is 1, the use can change the data in that menu, or user doesn't have access to change/edit data,but the code is ugly, and I only can control one menu.

user_base = db.session.query(
    UserBase.id,
    UserBase.username,
    UserBase.position_id,
    UserBasePosition.role_id,
    UserBasePosition.dep_id,
    UserBasePosition.positional_title_id,
    UserBasePosition.position_status,
    UserBasePosition.view_range,
    BaseMenuAuthority.menu_id,
    BaseMenuAuthority.is_edit
).join(
    UserBasePosition,
    UserBase.position_id == UserBasePosition.id
).join(
    BaseMenuAuthority,
    UserBasePosition.role_id == BaseMenuAuthority.role_id
).filter(UserBase.id == user_id, BaseMenuAuthority.menu_id == 15).one()

@role_accepted(1)
def put(self):
    # change something on Menu_15

How to control all menu? Maybe they look like this?

@role_accepted(1,1)
def put(self):
    # change something on Menu_1

@role_accepted(2,1)
def put(self):
    # change something on Menu_2

@role_accepted(3,1)
def put(self):
    # change something on Menu_3

@role_accepted(4,1)
def put(self):
    # change something on Menu_4

I tried to write code as below, but if I didn't filter BaseMenuAuthority.menu_id, I will got the error of sqlalchemy.orm.exc.MultipleResultsFound: Multiple rows were found for one()

user_base = db.session.query(
    UserBase.id,
    UserBase.username,
    UserBase.position_id,
    UserBasePosition.role_id,
    UserBasePosition.dep_id,
    UserBasePosition.positional_title_id,
    UserBasePosition.position_status,
    UserBasePosition.view_range,
    BaseMenuAuthority.menu_id,
    BaseMenuAuthority.is_edit
).join(
    UserBasePosition,
    UserBase.position_id == UserBasePosition.id
).join(
    BaseMenuAuthority,
    UserBasePosition.role_id == BaseMenuAuthority.role_id
).filter(UserBase.id == user_id).one()


from User permission sqlalchemy.orm.exc.MultipleResultsFound: Multiple rows were found for one()

No comments:

Post a Comment