How to use `not` to avoid returning results where ID is in a specific join table?

Howdy! I have this SQL query:

SELECT * FROM drills WHERE organization_id = 1 AND id NOT IN (SELECT drill_id FROM practice_drills WHERE team_id = 2)

SELECT * FROM drills WHERE organization_id = 1 AND id NOT IN (SELECT drill_id FROM practice_drills WHERE team_id = 2)

I'm trying to figure out how to convert it to Drizzle and apply it to an another query as a subquery, but I'm having some problems. Here's what I came up with:

    const subQuery = db
      .select({ drillId: practiceDrills.drillId })
      .from(practiceDrills)
      .where(eq(practiceDrills.teamPracticeId, params.teamPracticeId));

    const data = await db.query.drills.findMany({
      where: and(eq(drills.organizationId, params.organizationId), not(eq(drills.id, subQuery))),
    });

    const subQuery = db
      .select({ drillId: practiceDrills.drillId })
      .from(practiceDrills)
      .where(eq(practiceDrills.teamPracticeId, params.teamPracticeId));

    const data = await db.query.drills.findMany({
      where: and(eq(drills.organizationId, params.organizationId), not(eq(drills.id, subQuery))),
    });

When I run this, I receive this error:

more than one row returned by a subquery used as an expression

more than one row returned by a subquery used as an expression

How can I resolve this?

2 Replies

Angelelz•10mo ago

drizzle has a helper function notInArray that looks like it's what you're looking for.

jay kOP•10mo ago

That did it! Also adding limit to my subquery worked as well.

Gaming

Programming

How to use `not` to avoid returning results where ID is in a specific join table?