Drizzle Team•4mo ago

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;

1 Reply

Angelelz•4mo ago

I think you're problem is how you're using the template literal The relevant part:

ops.or(
 ops.like(customers.displayName, input.query),
 ops.sql`${customers.firstName} + " " + ${customers.lastName} LIKE ${"%" + input.query + "%"}`

ops.or(
 ops.like(customers.displayName, input.query),
 ops.sql`${customers.firstName} + " " + ${customers.lastName} LIKE ${"%" + input.query + "%"}`

Gaming

Programming

Need help with ops.sql in the where clause