Drizzle Team•12mo ago

As Column Alias

Hello, I have this drizzle + sqlite (better-sqlite-3) + typescript function, however, it's failing on the part where I use a subquery in the main query.

function requireAccessToList(
  listID: string,
  accessingUserID: string,
) {
  const shareListIdSubquery = db
    .select({ listID: share.listID })
    .from(share)
    .where(eq(share.userID, accessingUserID))
    .as('shareListIdSubquery')

  const listRowStatementQuery = db
    .select({ count: sql<number>`count(*)` })
    .from(list)
    .where(
      and(
        eq(list.id, listID),
        or(
          eq(list.ownerID, accessingUserID),
          inArray(list.id, shareListIdSubquery),
        ),
      ),
    )
    .prepare()

  const numberOfRows = listRowStatementQuery.all()

  if (numberOfRows.length === 0) {
    throw new Error("Authorization error, can't access list")
  }
}

function requireAccessToList(
  listID: string,
  accessingUserID: string,
) {
  const shareListIdSubquery = db
    .select({ listID: share.listID })
    .from(share)
    .where(eq(share.userID, accessingUserID))
    .as('shareListIdSubquery')

  const listRowStatementQuery = db
    .select({ count: sql<number>`count(*)` })
    .from(list)
    .where(
      and(
        eq(list.id, listID),
        or(
          eq(list.ownerID, accessingUserID),
          inArray(list.id, shareListIdSubquery),
        ),
      ),
    )
    .prepare()

  const numberOfRows = listRowStatementQuery.all()

  if (numberOfRows.length === 0) {
    throw new Error("Authorization error, can't access list")
  }
}

This is the part that makes the query fail, and works otherwise if I comment it out

inArray(list.id, shareListIdSubquery),

inArray(list.id, shareListIdSubquery),

My editor intellisense shows that the shareListIdSubquery type is SubqueryWithSelection What may be the reason that the subquery is breaking?

2 Replies

Angelelz•12mo ago

I think that if you use the subquery in a clause other than from you don't need to alias it So this subquery should just be:

  const shareListIdSubquery = db
    .select({ listID: share.listID })
    .from(share)
    .where(eq(share.userID, accessingUserID))

  const shareListIdSubquery = db
    .select({ listID: share.listID })
    .from(share)
    .where(eq(share.userID, accessingUserID))

christradingOP•12mo ago

You're right, removing the .as() worked. Thank you!

Gaming

Programming

As Column Alias