Drizzle Team•16mo ago

How to use a composite primary key in WHERE?

I have a table dv360UserProfilesTable which uses a composite key of userId and assignedUserProfileId. If I want to update this table, I'm struggling with how to use the composite key to batch update profiles. The below code throws the error: db error: ERROR: argument of WHERE must be type boolean, not type record The docs also do not show an example of this https://orm.drizzle.team/docs/indexes-constraints#composite-primary-key

const updatedProfilesResponse = await db
    .update(dv360UserProfilesTable)
    .set({
      status: status,
      lastAuditedAt: new Date(),
      lastAuditedBy: session.user.id.toString(),
    })
    .where(
      or(
        profiles.map((profile: Profile) => {
          return and(
            eq(dv360UserProfilesTable.userId, profile.userId),
            eq(
              dv360UserProfilesTable.assignedUserRoleId,
              profile.assignedUserRoleId
            )
          );
        })
      )
    )
    .returning();

const updatedProfilesResponse = await db
    .update(dv360UserProfilesTable)
    .set({
      status: status,
      lastAuditedAt: new Date(),
      lastAuditedBy: session.user.id.toString(),
    })
    .where(
      or(
        profiles.map((profile: Profile) => {
          return and(
            eq(dv360UserProfilesTable.userId, profile.userId),
            eq(
              dv360UserProfilesTable.assignedUserRoleId,
              profile.assignedUserRoleId
            )
          );
        })
      )
    )
    .returning();

Indexes & Constraints – DrizzleORM

Drizzle ORM | %s

1 Reply

Angelelz•16mo ago

It seems like you need to destructure profiles.map.... I don't think the or function accepts an array Also, I'd suggest to pull the creation of the and array out of the SQL statement for better readability and debugging The fact that you have a composite primary key doesn't affect your select statement, except in the way the query planner uses the indexes

Gaming

Programming

How to use a composite primary key in WHERE?