Undefined binding(s) detected when compiling WHERE. Undefined column(s

System Information
  • Strapi Version 4.2
  • MacOs
  • sqlite
  • Node Version:
  • NPM Version:
  • Yarn Version:

Hi,
I’ve created a custom resolver for my app, based on this tutorial
https://www.theitsolutions.io/blog/how-to-add-custom-graphql-query-to-strapi-v4

I try to fetch data from a .findMany() method, then agregate it, and send it back.

fetching and modify the datas works fine, but when I try graphql playground, I have a message :

"message": "Undefined binding(s) detected when compiling WHERE. Undefined column(s): [t0.id] query: where t0.id = ?",

Here is the full resolver

module.exports =
  (strapi) =>
  ({ nexus }) => {
    // console.log(nexus);
    return {
      types: [
        nexus.extendType({
          type: "Query", // extending Query
          definition(t) {
            t.field("popularity", {
              // with this field
              type: "PopularityResponse", // which has this custom type (defined below)
              args: {
                id: nexus.nonNull("ID"), // and accepts a product id as argument
                startDate: nexus.nonNull("String"),
                endDate: nexus.nonNull("String"),
                branding: nexus.nonNull("String"),
              },
              resolve: async (parent, args, context) => {
                // console.log(context)
                return {
                  // and resolves to the 3rd party popularity service logic
                  googleid: args.id,
                  startDate: args.startDate,
                  endDate: args.endDate,
                  branding: args.branding,
                };
              },
            });
          },
        }),
        nexus.objectType({
          name: "PopularityResponse", // this is our custom object type
          definition(t) {
            // t.nonNull.int("stars"); // with a result of the 3rd party popularity service response

            t.field("myData", {
              // and if requested, a field to query the product content type
              // type: "ImpressionEntityResponse",
              type: "ImpressionEntityResponse",
              resolve: async (parent, args) => {
                const query = await strapi.db
                  .query("api::impression.impression")
                  .findMany({
                    where: {
                      googleid: {
                        id: {
                          $eq: parent.googleid,
                        },
                      },
                      date_debut: {
                        $gte: parent.startDate,
                      },
                      date_fin: {
                        $lte: parent.endDate,
                      },
                    },
                    // populate: { googleid: true },
                    args,
                  });
                let aggregate = query.reduce(
                  (acc, key) => {
                    // vérifie si la campagne est dans la liste
                    if (
                      [parent.branding].some((elem) => {
                        let reg = new RegExp(elem);
                        return reg.test(key.campaignName);
                      })
                    ) {
                      let brandingIndex = acc.branding.findIndex(
                        (el) => el.date_debut == key.date_debut
                      );
                      if (brandingIndex !== -1) {
                        // si on a un élément
                        acc.branding[brandingIndex].search_impression_share +=
                          parseInt(key.search_impression_share);
                      } else {
                        acc.branding.push({
                          search_impression_share: parseInt(
                            key.search_impression_share
                          ),
                          date_debut: key.date_debut,
                        });
                      }
                    } else {
                      let nobrandingIndex = acc.nobranding.findIndex(
                        (el) => el.date_debut == key.date_debut
                      );
                      if (nobrandingIndex !== -1) {
                        // si on a un élément
                        acc.nobranding[
                          nobrandingIndex
                        ].search_impression_share += parseInt(
                          key.search_impression_share
                        );
                      } else {
                        acc.nobranding.push({
                          search_impression_share: parseInt(
                            key.search_impression_share
                          ),
                          date_debut: key.date_debut,
                        });
                      }
                    }

                    return acc;
                  },
                  { branding: [], nobranding: [] }
                );
                console.log(query.value);
                console.log("\n\n==> agregate", aggregate);
                return { value: query };
              }, // but relations, components and dynamic zones of the Product will be handled by built-in resolvers
            });
          },
        }),
      ],
      resolversConfig: {
        "Query.popularity": {
          auth: {
            scope: [
              "api::impression.impression.findOne",
              "api::impression.impression.find",
            ], // we give permission to use the new query for those who has findOne permission of Product content type
          },
        },
      },
    };
  };

I’m searching for two days, and I can’t find any answer.

Here is my gql query

query GetPopularity {
  popularity(id: "37", startDate:"2022-06-13",endDate:"2022-07-15",branding:"brand") {
    myData {
      data {
        attributes {
          googleid {
            data {
              attributes {
                g_customer_id
              }
            }
          }
        }
      }
    }
  }
}

When I console.log “agregate”, I get my computed datas as I want it. But within graphql playground I get the undefined bindings error, and the datas is null.

"data": {
    "popularity": {
      "myData": {
        "data": {
          "attributes": {
            "googleid": null
          }
        }
      }
    }
  }

If you have any suggestion, it will be of good help.

Thanks you all

Fabien

I’ve change the way I do it (based on the same tutorial)
I’m using the grapql way, and I dont have the undefined bindings error.

I’m also using the “toEntityResponseCollection” methods to send the datas.

But, when I send it back, I get a null result on graphql playground

{
  "data": {
    "popularity": {
      "id": {
        "data": [
          {
            "attributes": {
              "search_impression_share": null,
              "search_top_impression_share": null
            }
          }
        ]
      }
    }
  }
}

Here is the modified code

"use strict";

module.exports =
  (strapi, toEntityResponseCollection, toEntityResponse) =>
  ({ nexus }) => ({
    typeDefs: `
    type PopularityResponse {      
      id: ImpressionEntityResponseCollection
      startDate: String
      endDate: String
      branding: String
    }

    extend type Query {
      popularity(id: ID!, startDate: String, endDate: String, branding: String): PopularityResponse
    }
  `,
    resolvers: {
      Query: {
        popularity: {
          resolve: async (parent, args, context) => ({
            id: args.id,
            startDate: args.startDate,
            endDate: args.endDate,
            branding: args.branding,
          }),
        },
      },
      PopularityResponse: {
        id: {
          resolve: async (parent, args) => {
            // console.log(parent);
            // const { toEntityResponseCollection, toEntityResponse } = strapi
            //   .plugin("graphql")
            //   .service("format").returnTypes;
            // console.log(strapi)
            let query = {
              value: await strapi.entityService.findMany(
                "api::impression.impression",
                {
                  filters: {
                    googleid: {
                      id: {
                        $eq: parent.id,
                      },
                    },
                    date_debut: {
                      $gte: parent.startDate,
                    },
                    date_fin: {
                      $lte: parent.endDate,
                    },
                  },
                },
                args
              ),
            };
            console.log(query.value);
            console.log(parent);

            let aggregate = query.value.reduce(
              (acc, key) => {
                // vérifie si la campagne est dans la liste
                if (
                  [parent.branding].some((elem) => {
                    let reg = new RegExp(elem);
                    return reg.test(key.campaignName);
                  })
                ) {
                  let brandingIndex = acc.branding.findIndex(
                    (el) => el.date_debut == key.date_debut
                  );
                  if (brandingIndex !== -1) {
                    // si on a un élément
                    acc.branding[brandingIndex].search_impression_share +=
                      parseInt(key.search_impression_share);
                  } else {
                    acc.branding.push({
                      search_impression_share: parseInt(
                        key.search_impression_share
                      ),
                      date_debut: key.date_debut,
                    });
                  }
                } else {
                  let nobrandingIndex = acc.nobranding.findIndex(
                    (el) => el.date_debut == key.date_debut
                  );
                  if (nobrandingIndex !== -1) {
                    // si on a un élément
                    acc.nobranding[nobrandingIndex].search_impression_share +=
                      parseInt(key.search_impression_share);
                  } else {
                    acc.nobranding.push({
                      search_impression_share: parseInt(
                        key.search_impression_share
                      ),
                      date_debut: key.date_debut,
                    });
                  }
                }

                return acc;
              },
              { branding: [], nobranding: [] }
            );

            console.log("==========>>>>",aggregate);

            let y = [query.value[0]];

            return toEntityResponseCollection([aggregate]);
          },         
        },
      },
    },
    resolversConfig: {
      "Query.popularity": {
        auth: {
          scope: [
            "api::impression.impression.findOne",
            "api::impression.impression.find",
          ],
        },
      },
    },
  });

I’m not export of graphql. Is it possible to send custom datas back, instead of existing fields ?

Thanks

Fabien

I found how to solve my issue. I’ve created a specific type which aggregate the datas.
Now I’m able to get my computed elements

"use strict";

module.exports =
  (strapi, toEntityResponseCollection, toEntityResponse) =>
  ({ nexus }) => ({
    typeDefs: `
    type PopularityResponse {      
      id: ImpressionEntityResponseCollection
      startDate: String
      endDate: String
      branding: String
      aggregated: aggregateInput
    }

    type aggregateInput {
      brand: [singleAggregate]
      nobrand: [singleAggregate]
    }

    type singleAggregate {
      date_debut: String
      search_impression_share: Int
    }

    extend type Query {
      popularity(id: ID!, startDate: String, endDate: String, branding: String): PopularityResponse
    }
  `,
    resolvers: {
      Query: {
        popularity: {
          resolve: async (parent, args, context) => ({
            id: args.id,
            startDate: args.startDate,
            endDate: args.endDate,
            branding: args.branding,
          }),
        },
      },
      PopularityResponse: {
        aggregated: {
          resolve: async (parent, args, ctx) => {
            let compile = await strapi.entityService.findMany(
              "api::impression.impression",
              {
                filters: {
                  googleid: {
                    id: {
                      $eq: parent.id,
                    },
                  },
                  date_debut: {
                    $gte: parent.startDate,
                  },
                  date_fin: {
                    $lte: parent.endDate,
                  },
                },
              },
              args
            );

            // console.log(compile);

            let aggregate = compile.reduce(
              (acc, key) => {
                // vérifie si la campagne est dans la liste
                if (
                  [parent.branding].some((elem) => {
                    let reg = new RegExp(elem);
                    return reg.test(key.campaignName);
                  })
                ) {
                  let brandingIndex = acc.branding.findIndex(
                    (el) => el.date_debut == key.date_debut
                  );
                  if (brandingIndex !== -1) {
                    // si on a un élément
                    acc.branding[brandingIndex].search_impression_share +=
                      parseInt(key.search_impression_share);
                  } else {
                    acc.branding.push({
                      search_impression_share: parseInt(
                        key.search_impression_share
                      ),
                      date_debut: key.date_debut,
                    });
                  }
                } else {
                  let nobrandingIndex = acc.nobranding.findIndex(
                    (el) => el.date_debut == key.date_debut
                  );
                  if (nobrandingIndex !== -1) {
                    // si on a un élément
                    acc.nobranding[nobrandingIndex].search_impression_share +=
                      parseInt(key.search_impression_share);
                  } else {
                    acc.nobranding.push({
                      search_impression_share: parseInt(
                        key.search_impression_share
                      ),
                      date_debut: key.date_debut,
                    });
                  }
                }

                return acc;
              },
              { branding: [], nobranding: [] }
            );
            return {
              brand: aggregate.branding,
              nobrand: () => {
                return aggregate.nobranding;
              },
            };
          },
        },        
      },
    },
    resolversConfig: {
      "Query.popularity": {
        auth: {
          scope: [
            "api::impression.impression.findOne",
            "api::impression.impression.find",
          ],
        },
      },
    },
  });
1 Like