Drizzle Team•7mo ago

Multiple Where Clauses

I need to filter my data based on various filters but only if they are not null. How can I do this, so far I have tried the following ways. I have also tried to use $dynamic() in the query.

const query = db
    .select({
      id: emails.id,
      from: emails.from,
      subject: emails.subject,
      sentAt: emails.createdAt,
      status: emailRecipients.status,
      to: emailRecipients.recepientEmail,
    })
    .from(emails)
    .innerJoin(emailRecipients, eq(emails.id, emailRecipients.emailId))
    .where(eq(emails.userId, user.id));
if (timeFilter) {
  query.where(gte(emails.createdAt, timeFilter));
}
if (statusFilter) {
   query.where(eq(emailRecipient.status, statusFilter));
}

const query = db
    .select({
      id: emails.id,
      from: emails.from,
      subject: emails.subject,
      sentAt: emails.createdAt,
      status: emailRecipients.status,
      to: emailRecipients.recepientEmail,
    })
    .from(emails)
    .innerJoin(emailRecipients, eq(emails.id, emailRecipients.emailId))
    .where(eq(emails.userId, user.id));
if (timeFilter) {
  query.where(gte(emails.createdAt, timeFilter));
}
if (statusFilter) {
   query.where(eq(emailRecipient.status, statusFilter));
}

const query = sql`SELECT ${emails.id} AS id, ${emails.from} AS from, ${emails.subject} AS subject, ${emails.createdAt} AS sentAt, ${emailRecipients.status} AS status, ${emailRecipients.recepientEmail} AS to
    FROM ${emails} INNER JOIN ${emailRecipients} ON ${emails.id} = ${emailRecipients.emailId}
    WHERE ${emails.userId} = ${user.id} ${timeFilter ? and(gte(emails.createdAt, timeFilter)) : " "} ${apiKeyFilter ? and(eq(emails.apiKeyId, apiKeyFilter)) : " "} ${statusFilter ? and(eq(emailRecipients.status, statusFilter)) : " "} ${searchTerm ? and(or(ilike(emailRecipients.recepientEmail, searchTerm), ilike(emails.subject, searchTerm))) : " "}
    ORDER BY ${emails.createdAt} DESC, ${emails.id}`;

const query = sql`SELECT ${emails.id} AS id, ${emails.from} AS from, ${emails.subject} AS subject, ${emails.createdAt} AS sentAt, ${emailRecipients.status} AS status, ${emailRecipients.recepientEmail} AS to
    FROM ${emails} INNER JOIN ${emailRecipients} ON ${emails.id} = ${emailRecipients.emailId}
    WHERE ${emails.userId} = ${user.id} ${timeFilter ? and(gte(emails.createdAt, timeFilter)) : " "} ${apiKeyFilter ? and(eq(emails.apiKeyId, apiKeyFilter)) : " "} ${statusFilter ? and(eq(emailRecipients.status, statusFilter)) : " "} ${searchTerm ? and(or(ilike(emailRecipients.recepientEmail, searchTerm), ilike(emails.subject, searchTerm))) : " "}
    ORDER BY ${emails.createdAt} DESC, ${emails.id}`;

2 Replies

Sillvva•7mo ago

https://orm.drizzle.team/learn/guides/conditional-filters-in-query

Drizzle ORM - Conditional filters in query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Pradip ChaudharyOP•7mo ago

Thank you so much Sillvva. I think this will definitely solve my long-standing issue.

Gaming

Programming

Multiple Where Clauses