Drizzle TeamDT
Drizzle Team•14mo ago
Anthony

Struggling with simple subquery statement in Drizzle

Hi friends, I have a complex query like so that I've made into a subquery:
const query = db
        .select({
            ...
        })
        .from(leads)
        .innerJoin(participants, eq(participants.id, leads.participantId))
        .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) => {
            const conditions = [eq(campaigns.organizationId, organizationId)];

            ...

            return and(...conditions);
        })
        .as('query');


and I want to do some additional filtering on the query and fetch the count as well like so: 

const [{ count: total }] = await db.select({ count: count() }).from(query);

    const basicLeads = await db
        .select()
        .from(query)
        .where((t) =>
            participantIds
                ? inArray(t.participantId, participantIds)
                : undefined
        )
        .limit(perPage)
        .offset(Math.max(0, page - 1) * perPage);

However I am getting the following error: Error: Your "lead->id" field references a column "leads"."id", but the table "leads" is not part of the query! Did you forget to join it? any ideas why this doesn't work? Docs + GPT have been no help.
Was this page helpful?