Strapi自定义端点报错：整数类型输入语法无效

我在Strapi中创建自定义端点，每次请求时Insomnia返回InternalServerError，VS Code控制台输出以下错误：

[2024-03-28 15:21:41.180] error: select "t0".* from "public"."reservations" as "t0" where ("t0"."id" = $1) limit $2 - syntax input is not valid for integer type: "getReservationList"
error: select "t0".* from "public"."reservations" as "t0" where ("t0"."id" = $1) limit $2 - input syntax is not valid for type integer: «getReservationList»
    at Parser.parseErrorMessage (C:\Users\Usuario\Desktop\Trabajo\reservant\reservant-be\node_modules\pg-protocol\dist\parser.js:287:98)
    at Parser.handlePacket (C:\Users\Usuario\Desktop\Trabajo\reservant\reservant-be\node_modules\pg-protocol\dist\parser.js:126:29)
    at Parser.parse (C:\Users\Usuario\Desktop\Trabajo\reservant\reservant-be\node_modules\pg-protocol\dist\parser.js:39:38)
    at Socket.<anonymous> (C:\Users\Usuario\Desktop\Trabajo\reservant\reservant-be\node_modules\pg-protocol\dist\index.js:11:42)
    at Socket.emit (node:events:517:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
[2024-03-28 15:21:41.183] http: GET /api/reservations/getReservationList (60 ms) 500

我尝试过删除数据库、修改授权代码、更改方法名避免与Strapi自动生成的端点冲突，但问题仍未解决。

当前路由文件：

export default {
    routes: [
        { // Path defined with a URL parameter
            method: 'GET',
            path: "/reservation/getReservationList",
            handler: "reservation.getReservationList"
        },
    ],
}

完整控制器函数：

async getReservationList(ctx, next) {
    try {

        console.log("enters to the endpoint")

        //Get username from JWT token
        const { id } = await strapi.plugins["users-permissions"].services.jwt.getToken(ctx)
        ctx.state.user = await strapi.plugins["users-permissions"].services.user.fetchAuthenticatedUser(id);
        let username = ctx.state.user.username;

        //fetch user-profile id from username
        const userProfileEntry = await strapi.db.query("api::user-profile.user-profile").findOne({
            select: ["id"],
            where: { nickname: username },
            populate: false,
        });

        let confirmedEntries = await strapi.entityService.findMany(
            "api::reservation.reservation",
            {
                fields: ["full_name", "phone_number", "date", "time", "no_of_people", "status"],
                populate: {
                    author: {
                        fields: ["nickname"]
                    },
                    restaurant: {
                        fields: ["name"]
                    }
                },

                sort: { date: 'desc' },
                filters: {
                    status: "Confirmed",
                    author: userProfileEntry.id
                }
            }
        )


        let pendingEntries = await strapi.entityService.findMany(
            "api::reservation.reservation",
            {
                fields: ["full_name", "phone_number", "date", "time", "no_of_people", "status"],
                populate: {
                    author: {
                        fields: ["nickname"]
                    },
                    restaurant: {
                        fields: ["name"]
                    }
                },

                sort: { date: 'desc' },
                filters: {
                    status: "Pending",
                    author: userProfileEntry.id
                }
            }
        )

        let canceledEntries = await strapi.entityService.findMany(
            "api::reservation.reservation",
            {
                fields: ["full_name", "phone_number", "date", "time", "no_of_people", "status"],
                populate: {
                    author: {
                        fields: ["nickname"]
                    },
                    restaurant: {
                        fields: ["name"]
                    }
                },

                sort: { date: 'desc' },
                filters: {
                    status: "Canceled",
                    author: userProfileEntry.id
                }
            }
        )

        const entries = confirmedEntries.concat(pendingEntries, canceledEntries)

        let entriesReduced;
        if (entries && Array.isArray(entries)) {
            entriesReduced = entries.reduce((acc, item) => {
                acc = acc || [];
                acc.push({
                    id: item?.id,
                    full_name: item?.full_name || "",
                    phone_number: item?.phone_number || "",
                    date: new Date(item.date).toDateString() || "",
                    time: item?.time || "",
                    no_of_people: item?.no_of_people || "",
                    status: item?.status || "",
                    author: item.author?.nickname || "",
                    restaurant: item.restaurant?.name || "",
                });
                return acc;
            }, []);
        }

        // Return the reduced data
        return entriesReduced;
    } catch (err) {
        return err;
    }
}

问题根源 #

错误提示里的input syntax is not valid for type integer: «getReservationList»说明Strapi把getReservationList当成了reservation的ID参数去查询——Strapi自动生成的单条数据查询路由是/api/reservation/:id，你的自定义路由/reservation/getReservationList会被Strapi解析为“查询ID为getReservationList的reservation记录”，而这个字符串无法转换为整数，导致数据库抛出类型错误。

修复方案 #

1. 修改路由路径，避免与默认路由冲突
   给自定义端点添加独特前缀，确保路径不会被Strapi的默认路由规则匹配。例如修改为/reservation/user-list：

   export default {
       routes: [
           {
               method: 'GET',
               path: "/reservation/user-list",
               handler: "reservation.getReservationList"
           },
       ],
   }
   

2. 优化控制器错误处理
   不要直接返回错误对象，改用ctx.throw()让Strapi正确处理错误响应：

   catch (err) {
       ctx.throw(500, err.message);
   }
   

3. 可选：合并数据库查询提升性能
   原代码中三次findMany可以合并为一次，通过$in操作符匹配多种状态，减少数据库请求：

   const entries = await strapi.entityService.findMany(
       "api::reservation.reservation",
       {
           fields: ["full_name", "phone_number", "date", "time", "no_of_people", "status"],
           populate: {
               author: { fields: ["nickname"] },
               restaurant: { fields: ["name"] }
           },
           sort: { date: 'desc' },
           filters: {
               status: { $in: ["Confirmed", "Pending", "Canceled"] },
               author: userProfileEntry.id
           }
       }
   )
   

   这样无需手动拼接三个数组，代码更简洁高效。

内容的提问来源于stack exchange，提问作者Jon Alberdi