如何在Objection.js中实现insertGraph的条件关联导入？

问题背景 #

开发Excel导入功能时，需通过Objection.js的insertGraph插入主数据（Boat）及关联数据，但要求仅当关联数据存在时才创建关联关系。原insertGraph写法会强制插入所有关联项（哪怕关联ID为空），尝试分步插入主数据再条件插入关联的方式也未正常工作。

原insertGraph代码 #

.insertGraph(
                  [
                      {
                          '#id': 'boat',
                          ...arrayList[i],
                          insertedBy: req.user.sub,
                          berthAssignments: [
                              {
                                  insertedBy: req.user.sub,
                                  from: relationsData[i].ownershipSince,
  
                                  boat: [
                                      {
                                          id: '#ref{boat.id}'
                                      }
                                  ],
                                  berth: [
                                      {
                                          id: berthId[0]?.id
                                      }
                                  ]
                              }
                          ],
                          electricMeterAssignments: [
                              {
                                  insertedBy: req.user.sub,
                                  from: relationsData[i].electricMeterSince,
  
                                  electricMeter: [
                                      {
                                          id: meterId[0]?.id
                                      }
                                  ],
                                  boat: [
                                      {
                                          id: '#ref{boat.id}'
                                      }
                                  ]
                              }
                          ],
                          winterStorageAssignments: [
                              {
                                  insertedBy: req.user.sub,
                                  from: relationsData[i].winterStorageSince,
  
                                  winterStorage: [
                                      {
                                          id: storageId[0]?.id
                                      }
                                  ],
                                  boat: [
                                      {
                                          id: '#ref{boat.id}'
                                      }
                                  ]
                              }
                          ]
  
                      }
                  ],
                  {
  
                      relate: true,
                      allowRefs: true
                  }

尝试的传统分步插入代码 #

.insert(arrayList[i])
        .then(async insertedBoat => {
            if (relationsData[i].berthHandle) {
                console.log('berthHandle')
                await BerthAssignment
                    .query(req.dbConnection)
                    .insert({
                        bcm_berths_id: berthId[0].id,
                        bcm_boats_id: insertedBoat.id,
                        from: relationsData[i].berthSince,
                        insertedBy: req.user.sub,
                    })
                    .catch((error) => { console.log(error); throw next(error); });
            } else if (relationsData[i].electricMeters) {
                console.log('electricMeter')

                await ElectricMeterBoat
                    .query(req.dbConnection)
                    .insert({
                        bcm_electric_meters_id: meterId[0].id,
                        bcm_boats_id: insertedBoat.id,
                        from: relationsData[i].electricMeterSince,
                        insertedBy: req.user.sub,
                    })
                    .catch((error) => { console.log(error); throw next(error); });
            } else if (relationsData[i].winterStoragehandle) {
                console.log('Hallo')

                await WinterStorageAssignment
                    .query(req.dbConnection)
                    .insert({
                        bcm_winter_storages_id: storageId[0].id,
                        bcm_boats_id: insertedBoat.id,
                        from: relationsData[i].winterStorageSince,
                        insertedBy: req.user.sub,
                    })
                    .catch((error) => { console.log(error); throw next(error); });
            } else if (owner !== null) {
                console.log('asdflksdafsdaf')
                await BoatOwner
                    .query(req.dbConnection)
                    .delete()
                    .where({
                        bcm_boats_id: insertedBoat.id
                    })
                    .catch(() => null);
                await BoatOwner
                    .query(req.dbConnection)
                    .insert({
                        bcm_boats_id: insertedBoat.id,
                        bcm_persons_id: personId[0]?.id,
                        // bcm_companies_id: companyId[0]?.id,
                        isOwnerSince: relationsData[i]?.ownershipSince,
                        insertedBy: req.user.sub
                    })
                    .catch((error) => { console.log(error); throw next(error); });
            }
        })

解决方案 #

方案一：动态构建insertGraph关联数据（推荐） #

核心思路是根据关联数据是否存在，动态决定是否在Graph中添加对应的关联数组，避免插入空关联项。

// 构建基础Boat数据
const boatGraph = {
  '#id': 'boat',
  ...arrayList[i],
  insertedBy: req.user.sub
};

// 仅当泊位ID存在时，添加berthAssignments关联
if (berthId[0]?.id) {
  boatGraph.berthAssignments = [{
    insertedBy: req.user.sub,
    from: relationsData[i].ownershipSince,
    boat: [{ id: '#ref{boat.id}' }],
    berth: [{ id: berthId[0].id }]
  }];
}

// 仅当电表ID存在时，添加electricMeterAssignments关联
if (meterId[0]?.id) {
  boatGraph.electricMeterAssignments = [{
    insertedBy: req.user.sub,
    from: relationsData[i].electricMeterSince,
    electricMeter: [{ id: meterId[0].id }],
    boat: [{ id: '#ref{boat.id}' }]
  }];
}

// 仅当冬季存储ID存在时，添加winterStorageAssignments关联
if (storageId[0]?.id) {
  boatGraph.winterStorageAssignments = [{
    insertedBy: req.user.sub,
    from: relationsData[i].winterStorageSince,
    winterStorage: [{ id: storageId[0].id }],
    boat: [{ id: '#ref{boat.id}' }]
  }];
}

// 执行插入
await Boat.query(req.dbConnection)
  .insertGraph([boatGraph], {
    relate: true,
    allowRefs: true
  });

方案二：修复分步插入的逻辑问题 #

原代码的核心问题是使用else if导致关联条件互斥执行，多个关联满足时只会执行第一个。改为独立if判断，并增加关联ID的有效性校验：

await Boat.query(req.dbConnection)
  .insert(arrayList[i])
  .then(async insertedBoat => {
    // 泊位关联：独立判断，不依赖else
    if (relationsData[i].berthHandle && berthId[0]?.id) {
      console.log('berthHandle')
      await BerthAssignment
        .query(req.dbConnection)
        .insert({
          bcm_berths_id: berthId[0].id,
          bcm_boats_id: insertedBoat.id,
          from: relationsData[i].berthSince,
          insertedBy: req.user.sub,
        })
        .catch((error) => { console.log(error); throw next(error); });
    }

    // 电表关联：独立判断
    if (relationsData[i].electricMeters && meterId[0]?.id) {
      console.log('electricMeter')
      await ElectricMeterBoat
        .query(req.dbConnection)
        .insert({
          bcm_electric_meters_id: meterId[0].id,
          bcm_boats_id: insertedBoat.id,
          from: relationsData[i].electricMeterSince,
          insertedBy: req.user.sub,
        })
        .catch((error) => { console.log(error); throw next(error); });
    }

    // 冬季存储关联：独立判断
    if (relationsData[i].winterStoragehandle && storageId[0]?.id) {
      console.log('Hallo')
      await WinterStorageAssignment
        .query(req.dbConnection)
        .insert({
          bcm_winter_storages_id: storageId[0].id,
          bcm_boats_id: insertedBoat.id,
          from: relationsData[i].winterStorageSince,
          insertedBy: req.user.sub,
        })
        .catch((error) => { console.log(error); throw next(error); });
    }

    // 船东关联：独立判断
    if (owner !== null && personId[0]?.id) {
      console.log('asdflksdafsdaf')
      await BoatOwner
        .query(req.dbConnection)
        .delete()
        .where({ bcm_boats_id: insertedBoat.id })
        .catch(() => null);
      await BoatOwner
        .query(req.dbConnection)
        .insert({
          bcm_boats_id: insertedBoat.id,
          bcm_persons_id: personId[0].id,
          isOwnerSince: relationsData[i]?.ownershipSince,
          insertedBy: req.user.sub
        })
        .catch((error) => { console.log(error); throw next(error); });
    }
  })

关键注意点 #

• 方案一中，Objection.js会自动忽略Graph中不存在的关联字段，仅插入已定义的有效关联。
• 方案二中，必须用独立if替代else if，确保多个关联条件满足时都能执行；同时要校验关联ID的存在性，避免插入无效外键数据。

内容的提问来源于stack exchange，提问作者skuettel