brum17 Posts - Answer Overflow

brum17

•Created by brum17 on 6/9/2023 in #help

using in nest.js

What is the proper way to use in nest.js (or express). Should I create one single instance of db that should be exported (singleton instance), or the db should be created each time when needed? Is there somewhere a sample code when using with nest ?

3 replies

DTDrizzle Team

•Created by brum17 on 6/9/2023 in #help

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));

6 replies

Gaming

Programming