如何在Yii2迁移中高效将关联数组插入prices表？

现有数据库包含4张表：months、types、tonnages、prices。其中prices表字段为month_id、tonnage_id、type_id、price，其余三张表已填充数据。需要将如下结构的关联数组导入prices表：

'prices'=> [
    'soy' => [
        'january' => [
            50 => 145,
            75 => 136,
            100 => 138
        ],
        'february' => [
            50 => 100,
            75 => 125,
            100 => 108
        ],
        // ... 更多月份数据
    ],
    'hay' => [
        'january' => [
            50 => 105,
            75 => 116,
            100 => 118
        ],
        'february' => [
            50 => 100,
            75 => 125,
            100 => 108
        ],
        // ... 更多月份数据
    ],
    // ... 更多类型数据
]

我已经写出了可运行的代码，但逻辑繁琐，想简化：

public function safeUp()
{
   $months = $this->getDb()->createCommand('SELECT * FROM months')->queryAll();
   $tonnages = $this->getDb()->createCommand('SELECT * FROM tonnages')->queryAll();
   $types = $this->getDb()->createCommand('SELECT * FROM raw_types')->queryAll();
   $columns = ['price', 'month_id', 'tonnage_id', 'type_id'];
   $rows =  MigrationHelper::toRecords(Yii::$app->params['prices'], $months, $tonnages, $types);
   $this->batchInsert('prices', $columns, $rows);
}

public static function toRecords(array $arr, array $months, array $tonnages, array $types): array
{
  $result = [];
  foreach ($arr as $type => $typeValues) {
     $typeId = self::findIdByField($types, 'name', $type);
     foreach ($typesValues as $month => $monthValue) { // 原代码变量名错误，应为$typeValues
        $monthId = self::findIdByField($types, 'name', $month); // 原代码数组错误，应为$months
         foreach ($monthValues as $tonnage => $price) { // 原代码变量名错误，应为$monthValue
           $tonnageId = self::findIdByField($types, 'value', $tonnag); // 原代码数组错误、变量名错误，应为$tonnages、$tonnage
           $result[] = [
              'price' = $price, // 原代码赋值符号错误，应为=>
              'month_id' => $monthId,
              'tonnage_id' => $tonnageId,
              'type_id' => $typeId
          ];
         }
      }
  }
  return $result;
}

private static function findIdByField(array $arr, string $fieldName, int | string $value) : int 
{
  foreach ($arr as $item) {
   if ($item[$fieldName] === $value) {
      return $item['id'];
   }
  }
}

核心思路是提前将关联数据转换成键值对映射表，避免每次循环都遍历整个数组查找ID，同时修正原代码的错误并简化逻辑：

1. 重构迁移类的safeUp方法 #

public function safeUp()
{
    // 一次性构建各表的名称/值到ID的映射，后续直接通过键名获取ID
    $monthMap = array_column($this->getDb()->createCommand('SELECT id, name FROM months')->queryAll(), 'id', 'name');
    $tonnageMap = array_column($this->getDb()->createCommand('SELECT id, value FROM tonnages')->queryAll(), 'id', 'value');
    $typeMap = array_column($this->getDb()->createCommand('SELECT id, name FROM raw_types')->queryAll(), 'id', 'name');

    $columns = ['price', 'month_id', 'tonnage_id', 'type_id'];
    $rows = $this->generatePriceRows(Yii::$app->params['prices'], $monthMap, $tonnageMap, $typeMap);
    
    $this->batchInsert('prices', $columns, $rows);
}

2. 简化生成数据行的逻辑 #

private function generatePriceRows(array $priceData, array $monthMap, array $tonnageMap, array $typeMap): array
{
    $rows = [];
    
    foreach ($priceData as $typeName => $monthData) {
        // 校验类型是否存在，避免静默错误
        $typeId = $typeMap[$typeName] ?? throw new \InvalidArgumentException("不存在对应类型：{$typeName}");
        
        foreach ($monthData as $monthName => $tonnageData) {
            $monthId = $monthMap[$monthName] ?? throw new \InvalidArgumentException("不存在对应月份：{$monthName}");
            
            foreach ($tonnageData as $tonnageValue => $price) {
                $tonnageId = $tonnageMap[$tonnageValue] ?? throw new \InvalidArgumentException("不存在对应吨位：{$tonnageValue}");
                
                $rows[] = [
                    'price' => $price,
                    'month_id' => $monthId,
                    'tonnage_id' => $tonnageId,
                    'type_id' => $typeId
                ];
            }
        }
    }
    
    return $rows;
}

优化点说明 #

• 提升查询效率：用array_column把数据库查询结果转换成[名称/值 => ID]的映射数组，后续查找ID的时间复杂度从O(n)降到O(1)
• 简化逻辑结构：移除冗余的Helper类方法，把生成行的逻辑直接整合到迁移类中（如需复用可再抽离）
• 增加错误校验：通过??运算符判断映射是否存在，抛出异常提示缺失数据，方便调试
• 修正原代码错误：修复了原代码中的变量名拼写、数组使用错误等问题

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