Lateral joins
Hey I wrote this query in drizzle ORM:
I got this error:
So to verify if the corresponding SQL query would also cause issues I tried to run this SQL raw query:
const campaignInvolvedInQuery = postgresDb
.select({
id: companyUserCampaignMappings.campaignId,
campaignStatus: campaigns.status,
})
.from(companyUserCampaignMappings)
.innerJoin(
campaigns,
eq(campaigns.id, companyUserCampaignMappings.campaignId)
)
.where(eq(companyUserCampaignMappings.companyUserId, companyUsers.id))
.orderBy(desc(companyUserCampaignMappings.createdAt))
.limit(1)
.as("campaignInvolvedIn");
const userQuery = postgresDb
.select({
id: companyUsers.id,
name: companyUsers.name,
whatsappNumber: companyUserWhatsappChannelDetails.whatsappNumber,
campaignInvolvedIn: campaignInvolvedInQuery.id,
})
.from(companyUsers)
.innerJoin(
companyUserWhatsappChannelDetails,
eq(companyUsers.id, companyUserWhatsappChannelDetails.companyUserId)
)
.leftJoin(
campaignInvolvedInQuery,
notInArray(campaignInvolvedInQuery.campaignStatus, [
CampaignStatus.ENDED,
CampaignStatus.APPROVED,
CampaignStatus.PENDING_APPROVAL,
])
)
.leftJoin(
companyUserAssignedTags,
eq(companyUserAssignedTags.companyUserId, companyUsers.id)
)
.where(
and(eq(companyUsers.companyId, 1n), eq(companyUsers.isArchived, false))
);const campaignInvolvedInQuery = postgresDb
.select({
id: companyUserCampaignMappings.campaignId,
campaignStatus: campaigns.status,
})
.from(companyUserCampaignMappings)
.innerJoin(
campaigns,
eq(campaigns.id, companyUserCampaignMappings.campaignId)
)
.where(eq(companyUserCampaignMappings.companyUserId, companyUsers.id))
.orderBy(desc(companyUserCampaignMappings.createdAt))
.limit(1)
.as("campaignInvolvedIn");
const userQuery = postgresDb
.select({
id: companyUsers.id,
name: companyUsers.name,
whatsappNumber: companyUserWhatsappChannelDetails.whatsappNumber,
campaignInvolvedIn: campaignInvolvedInQuery.id,
})
.from(companyUsers)
.innerJoin(
companyUserWhatsappChannelDetails,
eq(companyUsers.id, companyUserWhatsappChannelDetails.companyUserId)
)
.leftJoin(
campaignInvolvedInQuery,
notInArray(campaignInvolvedInQuery.campaignStatus, [
CampaignStatus.ENDED,
CampaignStatus.APPROVED,
CampaignStatus.PENDING_APPROVAL,
])
)
.leftJoin(
companyUserAssignedTags,
eq(companyUserAssignedTags.companyUserId, companyUsers.id)
)
.where(
and(eq(companyUsers.companyId, 1n), eq(companyUsers.isArchived, false))
);I got this error:
PostgresError: invalid reference to FROM-clause entry for table "company_users"PostgresError: invalid reference to FROM-clause entry for table "company_users"So to verify if the corresponding SQL query would also cause issues I tried to run this SQL raw query:
