Prisma•5mo ago

No index found for fulltext search over relation (Planetscale)

I'm trying to find "favorited companions" combined with the ability to search through them. As you can see in my code, I search inside a relation query. But I get an error that no index could be found. Is this not possible with Planetscale? Searching throug companions directly works. The search index on name + description exists. But I don't know what index I could add to the favorites model to make this work.

const results = await prismadb.favorite.findMany({
  where: {
    userId: user.id,
    ...(query && {
      OR: [
        {
          companion: {
            name: {
              search: `${query}*`,
            },
          },
        },
        {
          companion: {
            description: {
              search: `${query}*`,
            },
          },
        },
      ],
    }),
  },
  include: {
    companion: {
      include: {
        favorites: true,
      },
    },
  },
});

const results = await prismadb.favorite.findMany({
  where: {
    userId: user.id,
    ...(query && {
      OR: [
        {
          companion: {
            name: {
              search: `${query}*`,
            },
          },
        },
        {
          companion: {
            description: {
              search: `${query}*`,
            },
          },
        },
      ],
    }),
  },
  include: {
    companion: {
      include: {
        favorites: true,
      },
    },
  },
});

2 Replies

Florian•5mo ago

Ok turns out I had to write the query like this

const results = await prismadb.favorite.findMany({
  where: {
    userId: user.id,
    ...(query && {
      companion: {
        OR: [
          {
            name: {
              search: `${query}*`,
            },
          },
          {
            description: {
              search: `${query}*`,
            },
          },
        ],
      },
    }),
  },
  [...]
});

const results = await prismadb.favorite.findMany({
  where: {
    userId: user.id,
    ...(query && {
      companion: {
        OR: [
          {
            name: {
              search: `${query}*`,
            },
          },
          {
            description: {
              search: `${query}*`,
            },
          },
        ],
      },
    }),
  },
  [...]
});

Nurul•5mo ago

I am glad to hear that you were able to resolve the issue 🙌

Gaming

Programming

No index found for fulltext search over relation (Planetscale)