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))
);
PostgresError: invalid reference to FROM-clause entry for table "company_users"
PostgresError: invalid reference to FROM-clause entry for table "company_users"
1 Reply
SELECT company_users.*, campaign_involved_in.campaign_id as campaign_involved_in
FROM company_users
LEFT JOIN (
SELECT company_user_campaign_mappings.campaign_id as campaign_id,
campaigns.status as campaign_status
FROM company_user_campaign_mappings
INNER JOIN campaigns
ON campaigns.id = company_user_campaign_mappings.campaign_id
WHERE company_user_campaign_mappings.company_user_id = company_users.id
ORDER BY company_users.created_at DESC
LIMIT 1
) as campaign_involved_in ON
campaign_involved_in.campaign_status NOT IN (1, 2, 8)
WHERE company_users.company_id = 1
AND company_users.is_archived = false
SELECT company_users.*, campaign_involved_in.campaign_id as campaign_involved_in
FROM company_users
LEFT JOIN (
SELECT company_user_campaign_mappings.campaign_id as campaign_id,
campaigns.status as campaign_status
FROM company_user_campaign_mappings
INNER JOIN campaigns
ON campaigns.id = company_user_campaign_mappings.campaign_id
WHERE company_user_campaign_mappings.company_user_id = company_users.id
ORDER BY company_users.created_at DESC
LIMIT 1
) as campaign_involved_in ON
campaign_involved_in.campaign_status NOT IN (1, 2, 8)
WHERE company_users.company_id = 1
AND company_users.is_archived = false