如何在查询中向SQLAlchemy中已定义的关系添加约束或谓词？

Question

我在Menu模型中定义了一个关系child_menus，所以当我查询一个Menu时，我会得到它的所有child_menus，对于每个子菜单，它的子菜单等等。到目前为止还不错。你知道吗

现在，我必须添加一个约束/谓词，即在查询时，子菜单中只应包括在例如[11, 12]中具有id的菜单。当然，id将是动态的，并且在查询时对我可用。你知道吗

class Menu(BaseModel):

    __tablename__ = 'menu'

    name = db.Column(db.String(256), nullable=False, unique=True)
    parent_id = db.Column(db.BigInteger, db.ForeignKey(
    'menu.id', name='menu_parent_id_fkey', ondelete='CASCADE'))
    is_active = db.Column(db.Boolean, default=True)
        sequence = db.Column(db.Integer)

    def __repr__(self):
        return '<Menu row_id=%r, name=%r>' % (
            self.row_id, self.name)

    child_menus = db.relationship(
        'Menu', backref=db.backref(
            'parent_menu', remote_side='Menu.row_id', uselist=True
            ), passive_deletes=True)

class MenuSchema(ma.ModelSchema):

    class Meta:
        model = Menu

    # relationships
    child_menus = ma.List(ma.Nested(
        'menu.schemas.MenuSchema',
        dump_only=True))

menu_id = 1
child_menu_ids = [11, 12]
menu = Menu.query.get(menu_id)
# I need to add the additional constraint/predicate in the above query
result = MenuSchema().dump(menu)

当前结果：

{
    "row_id": 1,
    "name": "Webinar",
    "parent_id": null,
    "is_active": true,
    "sequence": 1,
    "child_menus": [
        {
            "row_id": 11,
            "parent_id": 1,
            "child_menus": [
                {
                    "row_id": 12,
                    "parent_id": 11,
                    "child_menus": [],
                    "name": "Webinar 1.1.1",
                    "is_active": true,
                },
                {
                    "row_id": 13,
                    "parent_id": 11,
                    "child_menus": [],
                    "name": "Webinar 1.1.2",
                    "is_active": true,
                }
            ],
            "name": "Webinar 1.1",
            "sequence": null,
        },
        {
            "row_id": 14,
            "parent_id": 1,
            "child_menus": [],
            "name": "Webinar 1.2",
            "is_active": false,
            "sequence": null
        }
    ],
}

预期结果：

{
    "row_id": 1,
    "name": "Webinar",
    "parent_id": null,
    "is_active": true,
    "sequence": 1,
    "child_menus": [
        {
            "row_id": 11,
            "parent_id": 1,
            "child_menus": [
                {
                    "row_id": 12,
                    "parent_id": 11,
                    "child_menus": [],
                    "name": "Webinar 1.1.1",
                    "is_active": true,
                }
            ],
            "name": "Webinar 1.1",
            "sequence": null,
        }
    ],
}