Laravel Excel导入两个关联模型时item_id为空的解决方法

问题描述 #

我尝试用Laravel Excel库将Excel文件导入到两个存在关联关系的模型（Item和Cprices1）中：

• 最初通过返回两个模型实例的方式导入，Cprices1的item_id始终为null
• 之后改用OnEachRow接口调整代码，问题依然存在

核心原因 #

1. 第一种方案：ToModel接口的model方法返回数组时，Laravel Excel不会自动处理关联模型的保存顺序，此时Item还未插入数据库，通过barcode查询自然拿不到ID。
2. 第二种方案：开启了WithBatchInserts批量插入，model方法返回的Item会被批量保存，而onRow方法在每一行处理时立即执行，当前行的Item还未写入数据库，导致查询不到ID。

解决方案 #

放弃ToModel+OnEachRow的组合，改用ToCollection接口手动控制保存顺序，确保Item保存后再创建Cprices1。

基础实现代码（逐行保存） #

<?php

namespace App\Imports;

use App\Models\Admin\Item;
use App\Models\Admin\Brand;
use App\Models\Admin\Style;
use App\Models\Admin\Gender;
use App\Models\Admin\Category;
use App\Models\Admin\Section;
use App\Models\Admin\Season;
use App\Models\Admin\Vendor;
use App\Models\Admin\Size;
use App\Models\Admin\Color;
use App\Models\Admin\Grade;
use App\Models\User\Cprices1;
use Illuminate\Support\Collection;
use Illuminate\Validation\Rule;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithChunkReading;

class CodingImport implements ToCollection, WithValidation, WithHeadingRow, WithChunkReading
{
    use Importable;

    public function collection(Collection $rows)
    {
        foreach ($rows as $row) {
            // 先创建并保存Item，直接获取ID
            $item = Item::create([
                'barcode'     => $row['barcode'],
                'name'        => $row['name'],
                'description' => $row['description'],
                'brand_id'    => Brand::where('name', $row['brand_id'])->pluck('id')->first(),
                'style_id'    => Style::where('name', $row['style_id'])->pluck('id')->first(),
                'gender_id'   => Gender::where('name', $row['gender_id'])->pluck('id')->first(),
                'category_id' => Category::where('name', $row['category_id'])->pluck('id')->first(),
                'section_id'  => Section::where('name', $row['section_id'])->pluck('id')->first(),
                'season_id'   => Season::where('name', $row['season_id'])->pluck('id')->first(),
                'vendor_id'   => Vendor::where('name', $row['vendor_id'])->pluck('id')->first(),
                'color_id'    => Color::where('name', $row['color_id'])->pluck('id')->first(),
                'size_id'     => Size::where('name', $row['size_id'])->pluck('id')->first(),
                'grade_id'    => Grade::where('name', $row['grade_id'])->pluck('id')->first()
            ]);

            // 使用Item的ID创建关联的Cprices1
            Cprices1::create([
                'item_id'    => $item->id,
                'msrp'       => $row['msrp'],
                'rtp'        => $row['rtp'],
                'item_cost'  => $row['cost']
            ]);
        }
    }

    public function rules(): array
    {
        return [
            '*.barcode'     => Rule::unique('items', 'barcode'),
            '*.brand_id'    => Rule::exists('brands', 'name'),
            '*.style_id'    => Rule::exists('styles', 'name'),
            '*.gender_id'   => Rule::exists('genders', 'name'),
            '*.category_id' => Rule::exists('categories', 'name'),
            '*.section_id'  => Rule::exists('sections', 'name'),
            '*.season_id'   => Rule::exists('seasons', 'name'),
            '*.vendor_id'   => Rule::exists('vendors', 'name'),
            '*.color_id'    => Rule::exists('colors', 'name'),
            '*.size_id'     => Rule::exists('sizes', 'name'),
            '*.grade_id'    => Rule::exists('grades', 'name')
        ];
    }

    public function chunkSize(): int
    {
        return 1000;
    }
}

优化实现（批量插入保性能） #

如果需要保留批量插入的性能优势，可以先批量插入Item，再通过barcode映射ID批量创建Cprices1：

public function collection(Collection $rows)
{
    $items = [];
    $priceTemp = [];

    foreach ($rows as $row) {
        // 收集Item数据
        $itemData = [
            'barcode'     => $row['barcode'],
            'name'        => $row['name'],
            'description' => $row['description'],
            'brand_id'    => Brand::where('name', $row['brand_id'])->pluck('id')->first(),
            'style_id'    => Style::where('name', $row['style_id'])->pluck('id')->first(),
            'gender_id'   => Gender::where('name', $row['gender_id'])->pluck('id')->first(),
            'category_id' => Category::where('name', $row['category_id'])->pluck('id')->first(),
            'section_id'  => Section::where('name', $row['section_id'])->pluck('id')->first(),
            'season_id'   => Season::where('name', $row['season_id'])->pluck('id')->first(),
            'vendor_id'   => Vendor::where('name', $row['vendor_id'])->pluck('id')->first(),
            'color_id'    => Color::where('name', $row['color_id'])->pluck('id')->first(),
            'size_id'     => Size::where('name', $row['size_id'])->pluck('id')->first(),
            'grade_id'    => Grade::where('name', $row['grade_id'])->pluck('id')->first()
        ];
        
        $items[] = $itemData;
        // 暂存价格和barcode关联数据
        $priceTemp[] = [
            'barcode' => $row['barcode'],
            'msrp' => $row['msrp'],
            'rtp' => $row['rtp'],
            'item_cost' => $row['cost']
        ];
    }

    // 批量插入Item
    Item::insert($items);

    // 获取barcode到ID的映射
    $itemIdMap = Item::whereIn('barcode', collect($items)->pluck('barcode'))
        ->pluck('id', 'barcode')
        ->toArray();

    // 组装Cprices1批量插入数据
    $cpricesData = collect($priceTemp)->map(function ($data) use ($itemIdMap) {
        return [
            'item_id' => $itemIdMap[$data['barcode']],
            'msrp' => $data['msrp'],
            'rtp' => $data['rtp'],
            'item_cost' => $data['item_cost']
        ];
    })->toArray();

    Cprices1::insert($cpricesData);
}

关键修改点 #

• 改用ToCollection接口，完全控制数据处理流程
• 移除WithBatchInserts和WithUpserts（批量插入会导致无法实时获取ID）
• 验证规则字段前添加*.，符合ToCollection的验证格式
• 直接通过模型实例的id属性获取保存后的ID，避免重复查询数据库

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