Double 'where' clause in dynamic query overriding the original 'where' clause not combining

Hi I am using Drizzle to fetch some records:

    const query = db
        .select({
            // some fields
        })
        .from(leads)
        .innerJoin(participants, eq(participants.id, leads.participantId))
        .innerJoin(
            organizations,
            eq(organizations.id, participants.organizationId)
        )
        .innerJoin(campaigns, eq(campaigns.id, leads.campaignId))
        .leftJoin(agents, eq(agents.id, leads.agentId))
        .leftJoin(sites, eq(sites.id, leads.siteId))
        .leftJoin(studies, eq(studies.id, leads.studyId))
        .where((t) => {
            // ... a bunch of conditions
        })
        .orderBy(desc(leads.updatedAt))
        .$dynamic();

    const query = db
        .select({
            // some fields
        })
        .from(leads)
        .innerJoin(participants, eq(participants.id, leads.participantId))
        .innerJoin(
            organizations,
            eq(organizations.id, participants.organizationId)
        )
        .innerJoin(campaigns, eq(campaigns.id, leads.campaignId))
        .leftJoin(agents, eq(agents.id, leads.agentId))
        .leftJoin(sites, eq(sites.id, leads.siteId))
        .leftJoin(studies, eq(studies.id, leads.studyId))
        .where((t) => {
            // ... a bunch of conditions
        })
        .orderBy(desc(leads.updatedAt))
        .$dynamic();

and I want to do an additional level of filtering as well later in the function like so:

await query.where(inArray(participants.id, searchResults)))

await query.where(inArray(participants.id, searchResults)))

However these results now don't still have the original filters from the where clause in initial dynamic query? Is this intentional and how do I fix it?

1 Reply

Luxaritas•2mo ago

This is intentional - to compose the final query you’ll need to combine all applicable conditions into an array and pass it to where once

Gaming

Programming

Double 'where' clause in dynamic query overriding the original 'where' clause not combining