"Invalid input syntax for integer" using strapi

Question

I'm trying to make a custom enpoint using strapi the problem is that every time I try to run it, on insomnia I have an InternalServerError and in vs code I have the next error:

[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

I tried to delete the DDBB, change the authorization code and changing the method's name just in case it creates a conflict with autogenerated endpoints of strapi.

I use this file as route:

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

This is the full controller function:

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;
    }
}

Answer 1

there not sure the rest of the code but there are obvious issues:

  filters: {
    status: "Pending",
    author: userProfileEntry.id
  }

should be:

  filters: {
    status: "Pending",
    author: { 
      id: userProfileEntry.id 
    }
  }

then this part:

const { id } = await strapi.plugins["users-permissions"].services.jwt.getToken(ctx)

should be:

const { user } = ctx.state; 

this should have an authorised user, no need to get user from getToken

---

The best way to debug this, is to comment out from

let confirmedEntries = ...

to

return entriesReduced;

and then uncomment function by function

the error says it expects integer but received something else, undefined likely