Multiple records with same columns and values - using joins
I am using
innerJoins and this is the result I get. Sorry for the bad formatting.
But except the fieldd (which is different in all results) I get everything else the same
[
{
id: "abcd",
// same fields
fieldd: {
"id": 4315,
"module_d_id": 8969,
"field_t_id": 28,
"field_value": "9"
}
},
{
id: "abcd",
// same fields
fieldd: {
"id": 4316,
"module_d_id": 8969,
"field_t_id": 29,
"field_value": "75"
}
},
{
id: "abcd",
// same fields
fieldd: {
"id": 4317,
"module_d_id": 8969,
"field_t_id": 30,
"field_value": "Roof Repair Needed"
}
}
]
[
{
id: "abcd",
// same fields
fieldd: {
"id": 4315,
"module_d_id": 8969,
"field_t_id": 28,
"field_value": "9"
}
},
{
id: "abcd",
// same fields
fieldd: {
"id": 4316,
"module_d_id": 8969,
"field_t_id": 29,
"field_value": "75"
}
},
{
id: "abcd",
// same fields
fieldd: {
"id": 4317,
"module_d_id": 8969,
"field_t_id": 30,
"field_value": "Roof Repair Needed"
}
}
]
1 Reply
const result = await db
.select()
.from(inspectionBookings)
.innerJoin(users, eq(users.id, inspectionBookings.assignedDroneOperator))
.innerJoin(
organizations,
eq(organizations.id, inspectionBookings.organization)
)
.innerJoin(droneForms, eq(droneForms.booking, inspectionBookings.id))
.innerJoin(formd, eq(formd.droneFormId, droneForms.id))
.innerJoin(categoryd, eq(categoryd.formDId, formd.id))
.innerJoin(moduled, eq(moduled.categoryDId, categoryd.id))
.innerJoin(fieldd, eq(fieldd.moduleDId, moduled.id))
.orderBy(desc(inspectionBookings.createdAt))
.where(eq(categoryd.categoryTId, 26))
.offset(offset)
.limit(10)
const result = await db
.select()
.from(inspectionBookings)
.innerJoin(users, eq(users.id, inspectionBookings.assignedDroneOperator))
.innerJoin(
organizations,
eq(organizations.id, inspectionBookings.organization)
)
.innerJoin(droneForms, eq(droneForms.booking, inspectionBookings.id))
.innerJoin(formd, eq(formd.droneFormId, droneForms.id))
.innerJoin(categoryd, eq(categoryd.formDId, formd.id))
.innerJoin(moduled, eq(moduled.categoryDId, categoryd.id))
.innerJoin(fieldd, eq(fieldd.moduleDId, moduled.id))
.orderBy(desc(inspectionBookings.createdAt))
.where(eq(categoryd.categoryTId, 26))
.offset(offset)
.limit(10)
const fd1 = db
.select({ moduleDId: fieldd.moduleDId, fieldValue: fieldd.fieldValue })
.from(fieldd)
.where(eq(fieldd.fieldTId, 28))
.as("fd1");
const fd2 = db
.select({ moduleDId: fieldd.moduleDId, fieldValue: fieldd.fieldValue })
.from(fieldd)
.where(eq(fieldd.fieldTId, 29))
.as("fd2");
const fd3 = db
.select({ moduleDId: fieldd.moduleDId, fieldValue: fieldd.fieldValue })
.from(fieldd)
.where(eq(fieldd.fieldTId, 30))
.as("fd3");
const cd = db
.select({ id: categoryd.id, formDId: categoryd.formDId })
.from(categoryd)
.where(eq(categoryd.categoryTId, 26))
.as("cd");
const result = await tx
.select({
// ...
})
.from(inspectionBookings)
.innerJoin(users, eq(users.id, inspectionBookings.assignedDroneOperator))
.innerJoin(
organizations,
eq(organizations.id, inspectionBookings.organization),
)
.innerJoin(droneForms, eq(droneForms.booking, inspectionBookings.id))
.innerJoin(formd, eq(formd.droneFormId, droneForms.id))
.innerJoin(cd, eq(cd.formDId, formd.id))
.innerJoin(moduled, eq(moduled.categoryDId, cd.id))
.innerJoin(fd1, eq(fd1.moduleDId, moduled.id))
.innerJoin(fd2, eq(fd2.moduleDId, moduled.id))
.innerJoin(fd3, eq(fd3.moduleDId, moduled.id))
.orderBy(desc(inspectionBookings.createdAt))
.offset(offset)
.limit(10);
const fd1 = db
.select({ moduleDId: fieldd.moduleDId, fieldValue: fieldd.fieldValue })
.from(fieldd)
.where(eq(fieldd.fieldTId, 28))
.as("fd1");
const fd2 = db
.select({ moduleDId: fieldd.moduleDId, fieldValue: fieldd.fieldValue })
.from(fieldd)
.where(eq(fieldd.fieldTId, 29))
.as("fd2");
const fd3 = db
.select({ moduleDId: fieldd.moduleDId, fieldValue: fieldd.fieldValue })
.from(fieldd)
.where(eq(fieldd.fieldTId, 30))
.as("fd3");
const cd = db
.select({ id: categoryd.id, formDId: categoryd.formDId })
.from(categoryd)
.where(eq(categoryd.categoryTId, 26))
.as("cd");
const result = await tx
.select({
// ...
})
.from(inspectionBookings)
.innerJoin(users, eq(users.id, inspectionBookings.assignedDroneOperator))
.innerJoin(
organizations,
eq(organizations.id, inspectionBookings.organization),
)
.innerJoin(droneForms, eq(droneForms.booking, inspectionBookings.id))
.innerJoin(formd, eq(formd.droneFormId, droneForms.id))
.innerJoin(cd, eq(cd.formDId, formd.id))
.innerJoin(moduled, eq(moduled.categoryDId, cd.id))
.innerJoin(fd1, eq(fd1.moduleDId, moduled.id))
.innerJoin(fd2, eq(fd2.moduleDId, moduled.id))
.innerJoin(fd3, eq(fd3.moduleDId, moduled.id))
.orderBy(desc(inspectionBookings.createdAt))
.offset(offset)
.limit(10);