Austin Greer Posts - Answer Overflow

Austin Greer

•Created by Austin Greer on 8/27/2024 in #help

Need help with ops.sql in the where clause

I'm trying to query my database for customers WHERE firstName / lastName is like a given string and my current solution is not working Still not exactly sure on how to use the ops.sql function in the case of my where clause, I tried looking at the docs but their isn't too much documented for this Help or pointing me to the right direction would be appreciated, Thanks

const customers = await ctx.db.query.customers.findMany({
    where: (customers, ops) =>
        ops.and(
            ops.eq(customers.organizationId, ctx.session.user.organization.id),
                input?.query
                    ? ops.or(
                        ops.like(customers.displayName, input.query),
                        ops.sql`${customers.firstName} + " " + ${customers.lastName} LIKE '%${input.query}%'`
                    )
                    : undefined,
                    input?.categoryId
                        ? ops.eq(customers.categoryId, input.categoryId)
                        : undefined
                    ),
});

return customers;

const customers = await ctx.db.query.customers.findMany({
    where: (customers, ops) =>
        ops.and(
            ops.eq(customers.organizationId, ctx.session.user.organization.id),
                input?.query
                    ? ops.or(
                        ops.like(customers.displayName, input.query),
                        ops.sql`${customers.firstName} + " " + ${customers.lastName} LIKE '%${input.query}%'`
                    )
                    : undefined,
                    input?.categoryId
                        ? ops.eq(customers.categoryId, input.categoryId)
                        : undefined
                    ),
});

return customers;

3 replies

Gaming

Programming