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()

Answer

Your code seems to work as expected. Clearly, without the filter by menu_id there might be more than 1 row returned, and according to the documentation on sqlalchemy.orm.Query.one, the MultipleResultsFound exception is expected to be raised.

---

It looks to me that you could re-use the code of the query without (yet) executing it.

user_base_query = 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)  # NOTE that .one() is removed

when you need to actually use it, you could just amend the query and execute as below

user_menu = user_base_query.filter(BaseMenuAuthority.menu_id == 15).one()

In fact, you could even make a helper function:

def get_user_menu(user_id: int, menu_id: int):
    res = 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).filter(BaseMenuAuthority.menu_id == menu_id).one()