自定义Sql(树结构上级名称搜索)

• 接口调用：
  /bo/api/v1/bo-menu/page/res/manager?pageIndex=1&pageSize=15&parentName=参数值&nodejs=common-data
  

• Columns配置
  注意：isSearch，isData, filter、”method”: “custom”、sqlCustom
  

"parentName": {
      "fieldSort": 9999,
      "tableId": 1,
      "dataType": "varchar",
      "title": "上级栏目",
      "remark": "用于搜索",
      "guidMethod": "None",
      "isRequired": true,
      "isSearch": true,
      "isData": false,
      "editPropertie": {
        "showMethod": "text"
      },
      "pagePropertie": {
        "showMethod": "text"
      },
      "filter": {
        "method": "custom",
        "isMust": false,
        //"sql": " and parent_id in (select menu_id from bo_sys_menu where menu_name like CONCAT('%',@parentName,'%'))",
        //如果 sqlCustom 没找到,使用 sql     sqlserver   mysql  oracle sqlite  SqlCustom
        "sqlCustom": {
          "mysql": " and parent_id in (select menu_id from bo_sys_menu where menu_name like CONCAT('%',@parentName,'%'))",
          "oracle": " and parent_id in (select menu_id from bo_sys_menu where menu_name like '%'||@parentName||'%')",
          "sqlserver": " and parent_id in (select menu_id from bo_sys_menu where menu_name like '%'+@parentName+'%')"
        }
      },
      "field": "parentName",
      "name": "parent_name",
      "alias": "parent_name",
      "isDate": false,
      "isPrimary": false,
      "isIdentity": false,
      "maxLength": 50
    },

• responseField配置
  注意：字段进行占位null parent_name
  

"manager": {
      "show": "null parent_name ",
      "bridgeColumn": "parent_names",
      "extend": "basis",
      //"where": "{where_sql} ",
      "auth": true
      }
    },

• 组合完成后sql语句（mysql版本）
  select null rownum,null parent_name,meta, from bo_sys_menu where parent_id in (select menu_id from bo_sys_menu where menu_name like CONCAT(‘%’,@parentName,’%’)) order by sort_id limit @limtStartIndex,@pageSize