How to handle the same table self-join subquery

[githsheng]

I have a model as shown below:

class SysMenu(Model):
    id = fields.IntField(pk=True)
    pid = fields.IntField()
    name = fields.CharField(max_length=200)
    url = fields.CharField(max_length=200, null=True)
    permissions = fields.CharField(max_length=500, null=True)
    menu_type = fields.SmallIntField()
    icon = fields.CharField(max_length=50, null=True)
    sort = fields.IntField()

Question: I want to implement the following sql query. I currently use raw sql to solve, but do not know how to use orm to solve?

select t1.*, (select name from sys_menu t2 where t2.id=t1.pid) as parentName from sys_menu t1 where t1.id = #{menu_id}

Does anyone know?

[vlakius]

if i undestand correctly you could create a classmethod to obtain the parentName as parent_name property

class SysMenu(Model):
    id = fields.IntField(pk=True)
    pid = fields.IntField()
    name = fields.CharField(max_length=200)
    url = fields.CharField(max_length=200, null=True)
    permissions = fields.CharField(max_length=500, null=True)
    menu_type = fields.SmallIntField()
    icon = fields.CharField(max_length=50, null=True)
    sort = fields.IntField()

    @property
    def parent_name(self):
        return getattr(self, "_parent_name", None)

    @parent_name.setter
    def parent_name(self, value):
        self._parent_name = value

    @classmethod
    async def get_menu_with_parent_name(cls, menu_id):
        menu = await cls.filter(id=menu_id).first()

        if menu:
            parent_menu = await cls.filter(id=menu.pid).first()
            parent_name = parent_menu.name if parent_menu else None
            menu.parent_name = parent_name

        return menu

and then

await SysMenu.create(
        id=1,
        pid=1,
        name="System",
        url="",
        permissions="",
        menu_type=1,
        icon="",
        sort=1,
    )
menu = await SysMenu.get_menu_with_parent_name(1)
print(menu.parent_name)

# Output: System