tusharVaswani Posts - Answer Overflow

tusharVaswani

Explore posts from servers

KPCKevin Powell - Community DTDrizzle Team

•Created by tusharVaswani on 5/6/2024 in #help

Lateral joins

Hey I wrote this query in drizzle ORM:

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:

3 replies

Gaming

Programming