Theo's Typesafe Cult•2y ago

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?

1 Reply

FluX•2y ago

I was experimenting with dynamic queries and filtering yesterday and came up with this. Note: I haven't really tested it yet

// Get many users
    async getMany(opts?: {
        where?: object,
        select?: object,
        include?: object,
        orderBy?: object,
        skip?: number,
        take?: number,
        cursor?: object
    }) {
        const users = await db.user.findMany({
            ...opts
        })
        return users
    },

// Get many users
    async getMany(opts?: {
        where?: object,
        select?: object,
        include?: object,
        orderBy?: object,
        skip?: number,
        take?: number,
        cursor?: object
    }) {
        const users = await db.user.findMany({
            ...opts
        })
        return users
    },

// Get many users
    getMany: adminProcedure
        .input(
            z.object({
                where: z.object({}).catchall(z.any()).optional(),
                select: z.object({}).catchall(z.any()).optional(),
                include: z.object({}).catchall(z.any()).optional(),
                orderBy: z.object({}).catchall(z.any()).optional(),
                skip: z.number().optional(),
                take: z.number().optional(),
                cursor: z.object({}).catchall(z.any()).optional(),
            }).optional()
        )
        .query(async ({input}) => {
            const users = await user.getMany(input)
            return users
        }),

// Get many users
    getMany: adminProcedure
        .input(
            z.object({
                where: z.object({}).catchall(z.any()).optional(),
                select: z.object({}).catchall(z.any()).optional(),
                include: z.object({}).catchall(z.any()).optional(),
                orderBy: z.object({}).catchall(z.any()).optional(),
                skip: z.number().optional(),
                take: z.number().optional(),
                cursor: z.object({}).catchall(z.any()).optional(),
            }).optional()
        )
        .query(async ({input}) => {
            const users = await user.getMany(input)
            return users
        }),

This is obviously not very type safe as I'm no Typescript wizard, but this approach should be no issue if you enter all the params correctly

Gaming

Programming

Advanced search and filtering with Prisma?