Postgresql Join select columns with case
const res = await db.select({
...getTableColumns(table1),
items: table2
}).from(table1).leftJoin(table2, eq(table1.id, table2.mainId))
const res = await db.select({
...getTableColumns(table1),
items: table2
}).from(table1).leftJoin(table2, eq(table1.id, table2.mainId))
db.select({
...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`,
}).from(table2)
db.select({
...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`,
}).from(table2)
6 Replies
@Angelelz
This should work right? What is the issue?
i want to get like this
items must be an array but im getting only 1 object
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`}
})
You have to aggregate the result yourself in JS https://orm.drizzle.team/docs/joins#aggregating-results
Joins [SQL] - Drizzle ORM
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
Or use the RQB https://orm.drizzle.team/docs/rqb#querying
Drizzle Queries - Drizzle ORM
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
is any way to simplify this code?
i want to simplify this code to 1 query
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,
};
});
}
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));
}