3 replies

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

shareListIdSubquery

type is

SubqueryWithSelection

SubqueryWithSelection

What may be the reason that the subquery is breaking?

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

shareListIdSubquery

type is

SubqueryWithSelection

SubqueryWithSelection

What may be the reason that the subquery is breaking?

As Column Alias

Similar Threads

As Column Alias

Similar Threads

Similar Threads

Similar Threads