Drizzle Team•12mo ago

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

UsmanOP•12mo ago

  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)

Here's the query/code I fixed it. For people who wonder how, this is is my query now

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

Gaming

Programming

Multiple records with same columns and values - using joins