Peter Carlsson
Replicate ORM results
Hi guys,
I wanted this result:
findPersonsWithPets: [
{
"id": 1,
"first_name": "Peter",
"last_name": "Carlsson",
"age": 57,
"created_at": "2025-02-08T08:09:35.320Z",
"updated_at": null,
"pets": [
{
"id": 1,
"name": "Juni"
},
{
"id": 5,
"name": "Juni 2"
}
]
},
......
and did this and it's working but i wonder if there is a better way to do it?
export async function findPersonsWithPets() {
const rows = await db
.selectFrom('person')
.leftJoin('pet', 'pet.owner_id', 'person.id')
.select([
'person.id',
'person.first_name',
'person.last_name',
'person.age',
'person.created_at',
'person.updated_at',
'pet.id as petId',
'pet.name as petName'
])
.orderBy('person.id') // Ensure results are grouped by person
.execute();
// Transform result into structured array
const personsMap = new Map<
number,
{
id: number;
first_name: string;
last_name: string;
age: number;
created_at: Date;
updated_at: Date | null;
pets: Array<{ id: number; name: string }>;
}
>();
for (const row of rows) {
if (!personsMap.has(row.id)) {
personsMap.set(row.id, {
id: row.id,
first_name: row.first_name,
last_name: row.last_name,
age: row.age,
created_at: row.created_at,
updated_at: row.updated_at,
pets: []
});
}
if (row.petId) {
personsMap.get(row.id)!.pets.push({ id: row.petId, name: row.petName ?? '' });
}
}
return Array.from(personsMap.values());
}4 replies