DT
Drizzle TeamPradip Chaudhary

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}`;
Sillvva
Sillvva•18d 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 Chaudhary
Pradip Chaudhary•18d ago
Thank you so much Sillvva. I think this will definitely solve my long-standing issue.
Want results from more Discord servers?
Add your server
More Posts
Enum as primary key for roles (sqlite)I'm pretty new to Drizzle and database schemas in general so bear with me if this is stupid. I'm crHow can I get a better structured response when querying a table (many to many)?Hi, I am new to sql. This might be obvious, but I'm not sure how to go about this. Below is a simplisql where column md5Hey, how can I achieve the following statement in drizzle? ```sql -- mysql SELECT * FROM podcasts_Error on Query Filter: Types of property 'table' are incompatible.I'm currently trying to filter a query as [explained in on the docs](https://orm.drizzle.team/docs/rHow to do Computed Fields / Manipulate data at Query Level?I'm looking for something like this, on prisma we can validate fields when querying the model. I coupgEnum conversion not working as expectedI have the following pgEnum defined: ``` export const ApprovalRequestTypePgEnum = pgEnum('approval_