Prisma•4mo ago

Search text with Prisma

Hello, guys! I am trying to create a search functionality with prisma and want to ask which is the better way: making it with the search prop or making it with contains prop. Now I am using search and it looks like this

function formatSearchQuery(query: string | null): string | undefined {
  if (!query) return undefined;
  return query.split(' ').join(' &');
}

export async function GET(req: NextRequest) {
  const searchParams = req.nextUrl.searchParams;
  let searchInput: string | null = searchParams.get('search');
  const formattedQuery = formatSearchQuery(searchInput);

  try {
    const posts = await PostRepo.findMany({
      where: {
        title: {
          search: formattedQuery,
        },
      },
      include: {
        author: true,
        tags: true,
        comments: {
          include: {
            author: true,
          },
        },
      },
    });

function formatSearchQuery(query: string | null): string | undefined {
  if (!query) return undefined;
  return query.split(' ').join(' &');
}

export async function GET(req: NextRequest) {
  const searchParams = req.nextUrl.searchParams;
  let searchInput: string | null = searchParams.get('search');
  const formattedQuery = formatSearchQuery(searchInput);

  try {
    const posts = await PostRepo.findMany({
      where: {
        title: {
          search: formattedQuery,
        },
      },
      include: {
        author: true,
        tags: true,
        comments: {
          include: {
            author: true,
          },
        },
      },
    });

2 Replies

RaphaelEtim•4mo ago

Hi @bockster6669 If your search requirements are simple and you are only looking for substring matches, contains prop might be sufficient. For more complex search needs, especially if you are using PostgreSQL or MySQL, sticking with search prop is advisable. search prop allows for more advanced full-text search capabilities, including the use of query operators. See this section of the documentation.

Full-text search (Preview) | Prisma Documentation

This page explains how to search for text within a field.

bockster6669OP•4mo ago

hi, thank you, but can you help for this now? I am having strange problem, why when formattedQuery is undefined it doesn't finds any posts, but if I refer directly undefined to search then it finds all posts?

  const formattedQuery = formatSearchQuery(searchInputParam);

  try {
      const posts = await PostRepo.findMany({
        where: {
          title: {
            search: formattedQuery,
          },
        },

  const formattedQuery = formatSearchQuery(searchInputParam);

  try {
      const posts = await PostRepo.findMany({
        where: {
          title: {
            search: formattedQuery,
          },
        },

For example* I can use

orderBy: {
 createdAt: undefined,
}

orderBy: {
 createdAt: undefined,
}

and this will work find, the posts will not be sorted by any mean but I can not use

const variable = undefined
orderBy: {
 createdAt: variable ,
}

const variable = undefined
orderBy: {
 createdAt: variable ,
}

This will not work! I am getting error:

Error occurred while fetching posts: PrismaClientValidationError: 
Invalid `prisma.post.findMany()` invocation:

{
  where: {
    title: {
      search: "undefined"
    }
  },
  include: {
    author: true,
    tags: true,
    comments: {
      include: {
        author: true
      }
    }
  },
  orderBy: {
    createdAt: "undefined"
               ~~~~~~~~~~~
  }
}

Error occurred while fetching posts: PrismaClientValidationError: 
Invalid `prisma.post.findMany()` invocation:

{
  where: {
    title: {
      search: "undefined"
    }
  },
  include: {
    author: true,
    tags: true,
    comments: {
      include: {
        author: true
      }
    }
  },
  orderBy: {
    createdAt: "undefined"
               ~~~~~~~~~~~
  }
}

Gaming

Programming

Search text with Prisma