균어
균어
Explore posts from servers
DTDrizzle Team
Created by Perny on 3/12/2024 in #help
How can I get nested data to be inside the parent object
when it was possibly to use alias in left join i won't be 3 query but i searched in drizzle types i cannot find alias props in left join method or you can use sql``
6 replies
DTDrizzle Team
Created by Perny on 3/12/2024 in #help
How can I get nested data to be inside the parent object
i think you can use like this
const homeTeam = this.db.$with('home_team').as(
this.db.select().from(teamTable).where(eq(
teamTable.id,
gameTable.homeTeamId
))
)

const awayTeam = this.db.$with('away_team').as(
this.db.select().from(teamTable).where(eq(
teamTable.id,
gameTable.awayTeamId
))
)

const result = await this.db.with(homeTeam, awayTeam).select({
id: gameTable.id,
homeTeam: this.db.select().from(homeTeam),
awayTeam: this.db.select().from(awayTeam),
}).from(gameTable)
const homeTeam = this.db.$with('home_team').as(
this.db.select().from(teamTable).where(eq(
teamTable.id,
gameTable.homeTeamId
))
)

const awayTeam = this.db.$with('away_team').as(
this.db.select().from(teamTable).where(eq(
teamTable.id,
gameTable.awayTeamId
))
)

const result = await this.db.with(homeTeam, awayTeam).select({
id: gameTable.id,
homeTeam: this.db.select().from(homeTeam),
awayTeam: this.db.select().from(awayTeam),
}).from(gameTable)
i have ways using sub query
6 replies
DTDrizzle Team
Created by 균어 on 3/5/2024 in #help
How to get findMany with count?
i think this is not the best solution but using .select() it's hard to control relation between tables
9 replies
DTDrizzle Team
Created by 균어 on 3/5/2024 in #help
How to get findMany with count?
i found solution using sub queries
const deleteAbleQuery = this.db
.select({
count: count(),
})
.from(serviceOrderItems)
.where(eq(serviceOrderItems.serviceOrderId, sql`"serviceOrders"."id"`));

const result = await this.db.query.serviceOrders.findMany({
where: and(...wheres),
extras: {
...(input.showIsDeleteAble
? {
isDeleteAble: sql<boolean>`not ${deleteAbleQuery} > 0`.as(
'is_delete_able',
),
}
: {}),
count: sql`count(*) over()`.mapWith(Number).as('count'),
},
with: {
vehicle: {
with: {
make: true,
model: true,
},
},
customer: true,
branch: {
columns: {
name: true,
},
},
},
orderBy: [desc(serviceOrders.startedDate)],
limit: input.size,
offset: input.size * (input.page - 1),
});
const deleteAbleQuery = this.db
.select({
count: count(),
})
.from(serviceOrderItems)
.where(eq(serviceOrderItems.serviceOrderId, sql`"serviceOrders"."id"`));

const result = await this.db.query.serviceOrders.findMany({
where: and(...wheres),
extras: {
...(input.showIsDeleteAble
? {
isDeleteAble: sql<boolean>`not ${deleteAbleQuery} > 0`.as(
'is_delete_able',
),
}
: {}),
count: sql`count(*) over()`.mapWith(Number).as('count'),
},
with: {
vehicle: {
with: {
make: true,
model: true,
},
},
customer: true,
branch: {
columns: {
name: true,
},
},
},
orderBy: [desc(serviceOrders.startedDate)],
limit: input.size,
offset: input.size * (input.page - 1),
});
9 replies
DTDrizzle Team
Created by 균어 on 3/5/2024 in #help
How to get findMany with count?
if i use select() function i need to manually manage calling relations
9 replies
DTDrizzle Team
Created by 균어 on 3/5/2024 in #help
How to get findMany with count?
i want to use findMany not select
9 replies
DTDrizzle Team
Created by 균어 on 2/9/2024 in #help
PgTable type how to get at least have id field?
i need to check type is have id field
2 replies
DTDrizzle Team
Created by jonpage on 12/1/2023 in #help
Update From
you can use just
.set({deletedAt: new Date() OR new Date().toString()})
.set({deletedAt: new Date() OR new Date().toString()})
5 replies
DTDrizzle Team
Created by Zamiel on 11/27/2023 in #help
What are the Drizzle conventions for giving names to Drizzle return types?
you can use where in query
const rows = await this.db.query.users.findFirst({
columns: {
id: usersTable.id,
username: usersTable.username,
passwordHash: usersTable.passwordHash,
},
where: eq(usersTable.username, username)
})
const rows = await this.db.query.users.findFirst({
columns: {
id: usersTable.id,
username: usersTable.username,
passwordHash: usersTable.passwordHash,
},
where: eq(usersTable.username, username)
})
40 replies
DTDrizzle Team
Created by Zamiel on 11/27/2023 in #help
What are the Drizzle conventions for giving names to Drizzle return types?
you can use
const rows = await db.query.usersTable.findFirst()
const rows = await db.query.usersTable.findFirst()
40 replies
DTDrizzle Team
Created by 균어 on 11/24/2023 in #help
Postgresql Join select columns with case
async findByServiceOrderId(serviceOrderId: number | number[]) {
const base = await this.db
.select({
...getTableColumns(serviceOrderItems),
serviceType: serviceTypes,
})
.from(serviceOrderItems)
.where(
typeof serviceOrderId === 'number'
? eq(serviceOrderItems.serviceOrderId, serviceOrderId)
: inArray(serviceOrderItems.serviceOrderId, serviceOrderId),
)
.leftJoin(
serviceTypes,
eq(serviceTypes.id, serviceOrderItems.serviceTypeId),
);

const inventoryItems =
await this.inventoryItemsService.findByServiceOrderIds(
base.map((i) => i.id),
);

return base.map((i) => ({
...i,
inventoryItem: inventoryItems.find((ii) => ii.id === i.inventoryItemId),
}));
}
async findByServiceOrderId(serviceOrderId: number | number[]) {
const base = await this.db
.select({
...getTableColumns(serviceOrderItems),
serviceType: serviceTypes,
})
.from(serviceOrderItems)
.where(
typeof serviceOrderId === 'number'
? eq(serviceOrderItems.serviceOrderId, serviceOrderId)
: inArray(serviceOrderItems.serviceOrderId, serviceOrderId),
)
.leftJoin(
serviceTypes,
eq(serviceTypes.id, serviceOrderItems.serviceTypeId),
);

const inventoryItems =
await this.inventoryItemsService.findByServiceOrderIds(
base.map((i) => i.id),
);

return base.map((i) => ({
...i,
inventoryItem: inventoryItems.find((ii) => ii.id === i.inventoryItemId),
}));
}
async findByServiceOrderIds(serviceOrderIds: number[]) {
const sq = this.db
.select({
id: serviceOrderItems.inventoryItemId,
})
.from(serviceOrderItems)
.where(inArray(serviceOrderItems.id, serviceOrderIds));

return this.db
.select(this.selectBase)
.from(inventoryItems)
.leftJoin(products, eq(products.id, inventoryItems.productId))
.leftJoin(
serviceOrderItems,
eq(serviceOrderItems.inventoryItemId, inventoryItems.id),
)
.where(inArray(inventoryItems.id, sq));
}
async findByServiceOrderIds(serviceOrderIds: number[]) {
const sq = this.db
.select({
id: serviceOrderItems.inventoryItemId,
})
.from(serviceOrderItems)
.where(inArray(serviceOrderItems.id, serviceOrderIds));

return this.db
.select(this.selectBase)
.from(inventoryItems)
.leftJoin(products, eq(products.id, inventoryItems.productId))
.leftJoin(
serviceOrderItems,
eq(serviceOrderItems.inventoryItemId, inventoryItems.id),
)
.where(inArray(inventoryItems.id, sq));
}
i want to simplify this code to 1 query
8 replies
DTDrizzle Team
Created by 균어 on 11/24/2023 in #help
Postgresql Join select columns with case
is any way to simplify this code?
async findByVehicle(vehicleId: number, query: ServiceOrderFindAllInput) {
let base = this.db
.select({
...getTableColumns(serviceOrders),
itemsCount:
sql<number>`count(distinct ${serviceOrderItems.id})::int`.as(
'items_count',
),
branchName: branches.name,
})
.from(serviceOrders)
.leftJoin(
serviceOrderItems,
eq(serviceOrderItems.serviceOrderId, serviceOrders.id),
)
.leftJoin(branches, eq(branches.id, serviceOrders.branchId))
.groupBy(serviceOrders.id, branches.name, serviceOrderItems.id)
.where(eq(serviceOrders.vehicleId, vehicleId));

if (query.haveItems) {
base = base.having(({ itemsCount }) => gt(itemsCount, 0));
}

const exec = await base;

if (exec.length === 0) {
return exec;
}

const items = await this.serviceOrderItemsService.findByServiceOrderId(
exec.map((i) => i.id),
);

return exec.map((b) => {
const i = items.filter((i) => i.serviceOrderId === b.id);
const total = i.reduce(
(a, b) => a + (b.price * b.quantity - (b.discount || 0)),
0,
);

return {
...b,
items: i.map((j) => ({
...j,
productName: j.inventoryItem?.name,
})),
total,
};
});
}
async findByVehicle(vehicleId: number, query: ServiceOrderFindAllInput) {
let base = this.db
.select({
...getTableColumns(serviceOrders),
itemsCount:
sql<number>`count(distinct ${serviceOrderItems.id})::int`.as(
'items_count',
),
branchName: branches.name,
})
.from(serviceOrders)
.leftJoin(
serviceOrderItems,
eq(serviceOrderItems.serviceOrderId, serviceOrders.id),
)
.leftJoin(branches, eq(branches.id, serviceOrders.branchId))
.groupBy(serviceOrders.id, branches.name, serviceOrderItems.id)
.where(eq(serviceOrders.vehicleId, vehicleId));

if (query.haveItems) {
base = base.having(({ itemsCount }) => gt(itemsCount, 0));
}

const exec = await base;

if (exec.length === 0) {
return exec;
}

const items = await this.serviceOrderItemsService.findByServiceOrderId(
exec.map((i) => i.id),
);

return exec.map((b) => {
const i = items.filter((i) => i.serviceOrderId === b.id);
const total = i.reduce(
(a, b) => a + (b.price * b.quantity - (b.discount || 0)),
0,
);

return {
...b,
items: i.map((j) => ({
...j,
productName: j.inventoryItem?.name,
})),
total,
};
});
}
8 replies
DTDrizzle Team
Created by 균어 on 11/24/2023 in #help
Postgresql Join select columns with case
i want to get like this
const res = await this.db.select({
...getTableColumns(table1),
items: {...getTableColumns(table2), partNumber: sql<string>`case when ${products.id} is not null then ${products.partNumber} else ${inventoryItems.partNumber} end`,
name: sql<string>`case when ${products.id} is not null then ${products.nameEn} else ${inventoryItems.name} end`}
})
const res = await this.db.select({
...getTableColumns(table1),
items: {...getTableColumns(table2), partNumber: sql<string>`case when ${products.id} is not null then ${products.partNumber} else ${inventoryItems.partNumber} end`,
name: sql<string>`case when ${products.id} is not null then ${products.nameEn} else ${inventoryItems.name} end`}
})
items must be an array but im getting only 1 object
8 replies
DTDrizzle Team
Created by 균어 on 11/24/2023 in #help
Postgresql Join select columns with case
@Angelelz
8 replies
DTDrizzle Team
Created by Timo Martinson on 11/24/2023 in #help
Where to get the types from?
you can use
typeof tableName.$inferSelect
typeof tableName.$inferSelect
2 replies
DTDrizzle Team
Created by FelixNg on 11/24/2023 in #help
createInsertSchema & createSelectSchema difference
in delete you only need an id to delete update you can use createInsertSchema(table).partial()
4 replies
DTDrizzle Team
Created by rykuno on 7/2/2023 in #help
PGEnum -> Typescript Enum
im doing like this too
5 replies
TTCTheo's Typesafe Cult
Created by 균어 on 6/18/2023 in #questions
How to get singular type on trpc query
getting error typescript: Type '(ServiceOrder & { vehicle: Vehicle & { model: { name: string; }; }; })[] | undefined' has no matching index signature for type 'number'. [2537]
9 replies