Subquery in select

Hi, is it possible to use subquery in a select? I'd like to generate something like this

select "user"."name", "userAddress"."address", (select count(*) from "userLike" ul where ul."likerId" = "user".id) as "likeCount" from "user" 
left join "userAddress" on "userAddress"."sender_user_id" = "user"."id" 
where "user"."id" < 50

select "user"."name", "userAddress"."address", (select count(*) from "userLike" ul where ul."likerId" = "user".id) as "likeCount" from "user" 
left join "userAddress" on "userAddress"."sender_user_id" = "user"."id" 
where "user"."id" < 50

Schema:

export const user = pgTable('user', {
    id: serial('id').primaryKey(),
    name: text('name'),
});

export const userAddress = pgTable('userAddress', {
    id: serial('id').primaryKey(),
    userId: integer('sender_user_id').references(() => user.id),
    address: text('address'),
});

export const userLike = pgTable('userLike', {
    id: serial('id').primaryKey(),
    likedId: integer('likerId').references(() => user.id),
    userId: integer('userId').references(() => user.id),
});

export const user = pgTable('user', {
    id: serial('id').primaryKey(),
    name: text('name'),
});

export const userAddress = pgTable('userAddress', {
    id: serial('id').primaryKey(),
    userId: integer('sender_user_id').references(() => user.id),
    address: text('address'),
});

export const userLike = pgTable('userLike', {
    id: serial('id').primaryKey(),
    likedId: integer('likerId').references(() => user.id),
    userId: integer('userId').references(() => user.id),
});

Attempt:

const result2 = await db.select({
    kokos: user.name,
    address: userAddress.address,
    likeCount: //dunno
})
.from(user)
.leftJoin(userAddress, eq(userAddress.userId, user.id))
.where(lt(user.id, 42));

const result2 = await db.select({
    kokos: user.name,
    address: userAddress.address,
    likeCount: //dunno
})
.from(user)
.leftJoin(userAddress, eq(userAddress.userId, user.id))
.where(lt(user.id, 42));

3 Replies

Dan•2y ago

Not yet - https://github.com/drizzle-team/drizzle-orm/issues/361

GitHub

Allow using subqueries in SelectFields with proper type inference...

brum17OP•2y ago

I see. Is there some estimation when this might become available? Also is there some workaround one may use to achieve this? (e.g. raw SQL or something like that)

Dan•2y ago

as for the workaround, you can try something like this:

.select({
  likeCount: sql<number>`(${db.select({count: sql`count(*)::integer`}).from(userLike).where(...)})`.as('likeCount')
})

.select({
  likeCount: sql<number>`(${db.select({count: sql`count(*)::integer`}).from(userLike).where(...)})`.as('likeCount')
})

alternatively, you can just use left join instead of selecting the subquery

select "user"."name", "userAddress"."address", count("userLike"."id") as "likeCount" from "user" 
left join "userLike" ul where ul."likerId" = "user".id
left join "userAddress" on "userAddress"."sender_user_id" = "user"."id"

select "user"."name", "userAddress"."address", count("userLike"."id") as "likeCount" from "user" 
left join "userLike" ul where ul."likerId" = "user".id
left join "userAddress" on "userAddress"."sender_user_id" = "user"."id"

(not a full query, will probably need GROUP BY clause, so just a reference)

Gaming

Programming

Subquery in select