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');
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);
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.
4 Replies
Angelelz
Angelelz4w ago
If you put this in drizzle.run it's easier to take a look and make quick changes
Anthony
AnthonyOP4w ago
Anthony
AnthonyOP4w ago
Oh ur right I will copy over my example in full
Want results from more Discord servers?
Add your server