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}`;