2 replies

Advanced search and filtering with Prisma?

How do you all do advanced filtering/searching of your DB? For example my app has tasks about programming, and I want to show those tasks to the user. On that page I have a text search with many dropdown options like: taskType, difficulty, language etc.

I solved this by sending an array of IDs for each of these dropdowns, how I saved them in my DB, to my tRPC endpoint.

For example:

ctx.prisma.tasks.findMany({
  select: {
    ...
  },
  where: {
    difficulties: {
      in: input.difficulties
    }
  }
})

ctx.prisma.tasks.findMany({
  select: {
    ...
  },
  where: {
    difficulties: {
      in: input.difficulties
    }
  }
})

However, if I don't pass anything (that's the case when it should display tasks of all difficulties - so before any filters are applied) then I don't get any tasks because my difficulties array is empty... Furthermore, I have multiple of such filters which makes things even harder. And as far as I've tried there is no way to do conditional filtering.

Also, $queryRaw is not a good option either. I have this code here which works normally with other packages like pg-promise, which execute the query string itself, while Prisma tries to do some magic and fails...

SELECT name, ...
FROM tasks
WHERE
  deletedAt is null
  ${input.difficulties?.length === 0 ? "" : ` AND difficulty = any(ARRAY[${input.difficulties}]) `}

SELECT name, ...
FROM tasks
WHERE
  deletedAt is null
  ${input.difficulties?.length === 0 ? "" : ` AND difficulty = any(ARRAY[${input.difficulties}]) `}

What Prisma does is it tries to match whatever is inside the template string ${} as an argument so the final query string looks like so:

SELECT name, ...
FROM tasks
WHERE
  deletedAt is null
  $1

SELECT name, ...
FROM tasks
WHERE
  deletedAt is null
  $1

Because, like I said, it tries to make everything into an argument and does not execute the condition first. I know Prisma is not omnipotent, which is why I decided to just use pg-promise for now, although I don't get inferred types...

Which brings me again to my question, did anyone encounter similar problems and how did you solve them? What do you use for advanced DB search and filtering?

TTC

Theo's Typesafe Cult•4y ago•

2 replies

JulieCezar

Advanced search and filtering with Prisma?

ctx.prisma.tasks.findMany({
  select: {
    ...
  },
  where: {
    difficulties: {
      in: input.difficulties
    }
  }
})

ctx.prisma.tasks.findMany({
  select: {
    ...
  },
  where: {
    difficulties: {
      in: input.difficulties
    }
  }
})

SELECT name, ...
FROM tasks
WHERE
  deletedAt is null
  ${input.difficulties?.length === 0 ? "" : ` AND difficulty = any(ARRAY[${input.difficulties}]) `}

SELECT name, ...
FROM tasks
WHERE
  deletedAt is null
  ${input.difficulties?.length === 0 ? "" : ` AND difficulty = any(ARRAY[${input.difficulties}]) `}

What Prisma does is it tries to match whatever is inside the template string ${} as an argument so the final query string looks like so:

SELECT name, ...
FROM tasks
WHERE
  deletedAt is null
  $1

SELECT name, ...
FROM tasks
WHERE
  deletedAt is null
  $1

Advanced search and filtering with Prisma?

Advanced search and filtering with Prisma?

Similar Threads

Similar Threads

Similar Threads