Subquery in select
Hi, is it possible to use subquery in a select?
I'd like to generate something like this
Schema:
Attempt:
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" < 50select "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" < 50Schema:
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));