Drizzle Team•15mo ago

Similar building of where clause to Prisma

I'm trying to migrate my prisma queries to drizzle but one thing I'm trying to do is convert the following. I'm lost on how best to 'build' a where clause similar to what prisma has depending on what properties I have passed into my function:

 if (name) {
    whereClause.name = {
      contains: name,
      mode: 'insensitive',
    };
  }
  if (address) {
    whereClause.address = {
      contains: address,
      mode: 'insensitive',
    };
  }
  if (birthday) {
    whereClause.birthday = birthday;
  }
  if (phone) {
    whereClause = {
      ...whereClause,
      OR: [
        {
          phone,
        },
        { alternatePhone: phone },
      ],
    };
  }
  const person = await prisma.person
    .findMany({
      where: whereClause,
    })

 if (name) {
    whereClause.name = {
      contains: name,
      mode: 'insensitive',
    };
  }
  if (address) {
    whereClause.address = {
      contains: address,
      mode: 'insensitive',
    };
  }
  if (birthday) {
    whereClause.birthday = birthday;
  }
  if (phone) {
    whereClause = {
      ...whereClause,
      OR: [
        {
          phone,
        },
        { alternatePhone: phone },
      ],
    };
  }
  const person = await prisma.person
    .findMany({
      where: whereClause,
    })

2 Replies

rphlmr ⚡•15mo ago

You can try that:

const where: SQL[] = []

if (name) {
  where.push(ilike(person.name, `%${name}%`))
}

if (address) {
  where.push(ilike(person.address, `%${address}%`))
}

if (birthday) {
   where.push(eq(person.birthday, birthday))
}

if (phone) {
    where.push(or(eq(person.phone, phone), eq(person.alternatePhone, phone)))
  }

  await db.select().from(person).where(and(...where))

const where: SQL[] = []

if (name) {
  where.push(ilike(person.name, `%${name}%`))
}

if (address) {
  where.push(ilike(person.address, `%${address}%`))
}

if (birthday) {
   where.push(eq(person.birthday, birthday))
}

if (phone) {
    where.push(or(eq(person.phone, phone), eq(person.alternatePhone, phone)))
  }

  await db.select().from(person).where(and(...where))

imobyOP•15mo ago

Thanks this helped a lot!! 🙏

Gaming

Programming

Similar building of where clause to Prisma