php - Laravel 9, Parent child bulk insert while replacing uuid to id - Stack Overflow

admin管理员组

文章数量:1351623

I have following table structure.

Table: movies

id	uuid	title	...
1	uuid-m01	movie 1	...

I have following table structure.

Table: movies

id	uuid	title	...
1	uuid-m01	movie 1	...

Table: categories

id	uuid	title	...
1	uuid-c01	category 1	...
2	uuid-c02	category 2	...

Table: movie_categories

id	movie_id	category_id	uuid	...
..	........	...........	........	...

POST: .../api/movies/create

{
  "title": "movie 2",
  "category_ids": [
    "uuid-c01",
    "uuid-c02"
  ]
}

Models/APIs/v1/Movie.php

class Movie extends Model {
    ...

    public function movieCategories() {
        return $this->hasMany(MovieCategory::class);
    }

    ...
}

Models/APIs/v1/MovieCategory.php

class MovieCategory extends Model {
    ...

    public function movie() {
        return $this->belongsTo(Movie::class);
    }
    public function category() {
        return $this->hasOne(Category::class);
    }
}

Models/APIs/v1/Category.php

class Category extends Model {
    public function movieCategory() {
        return $this->belongsTo(MovieCategory::class);
    }
}

Controllers/APIs/v1/MovieController.php

public function store(MovieRequest $request) {
    try {
        $Post = $request->validated();

        $Movie = Movie::create([
            'uuid'  => Str::uuid(),
            'title' => $Post['title'],
        ]);

        $Movie->movieCategories()->create($Post['category_ids']);

        ...
    } catch (\Throwable $th) {
        ...
    }
}

Now, the question is that in the category_ids I am passing the uuids of categories, but in the movie_categories table the column is accepting the id of the categories table.

When I am running the code, I am getting the following error:

{
  ...
  "error": {
    "code": "22003",
    "message": "SQLSTATE[22003]: Numeric value out of range: 1264 Out of 
                range value for column 'category_id' at row 1 (SQL: 
                insert into `movie_categories` 
                (`movie_id`, `category_id`, `uuid`, `updated_at`, `created_at`) 
                values 
                (3, uuid-c01, uuid-string, 2025-04-01 07:36:18, 2025-04-01 07:36:18))"
  }
}

Whereas the output should be:

Table: movie_categories

id	movie_id	category_id	uuid	...
#	2	1	uuid-mc3	...
#	2	2	uuid-mc4	...

I know that there are various other ways to achieve this, such as: Observers, MySQL triggers, Fetching ids before inserting, but I was wondering if there is more appropriate(Laravel based) solution this problem.

• php
• laravel
• insert
• relationship

Share Improve this question Follow edited Apr 1 at 9:11 Mr.Singh asked Apr 1 at 9:02 Mr.SinghMr.Singh 1,81766 gold badges3131 silver badges6262 bronze badges 4

• If you don't fetch the real IDs, there is no way to insert the data without doing an extra query to fetch those IDs, are you database is using an ID column instead of a UUID column, so the only way is to fetch the IDs, in any language. I would not approach any type of SQL solution apart from a query, as you will be offuscating code and that will not be good, but there is theory and reality, so maybe you have to use them if you app really hurts in performance when doing this. – matiaslauriti Commented Apr 1 at 11:47
• I have already considered and discussed with the team about this, and we have decided to go with MySQL BEFORE INSERT TRIGGER as it will happen directly in the database hence, the execution and processing time for ID fetching queries should be reduced. – Mr.Singh Commented Apr 1 at 11:58
• That is fine, but have in mind that it is a very bad practice to do so, as you are hiding (obfuscating) code in the DB, so your app now is way more harder to trace, keep up with, and maintain, but again, theory is super nice but reality exists. I would still try to find another solution than doing what you have done – matiaslauriti Commented Apr 1 at 12:01
• 1 Totally understandable, I will keep that in mind. However, at the moment, this option looks to be the viable one. Thanks again for suggestion :) – Mr.Singh Commented Apr 1 at 12:14

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 0

In POST /api/movies/create you should send integer id instead of UUID

{
  "title": "movie 2",
  "category_ids": [
    1,
    2
  ]
}

The issue is because you're directly trying to insert UUIDs (strings) into the category_id column of the movie_categories table, which expects an integer (or the actual primary key of the categories table).

Solution

Instead of inserting category_ids directly as UUIDs, first map them to their corresponding primary keys (IDs) before inserting them into movie_categories.

Modify Store Method in MovieController.php

public function store(MovieRequest $request) {
    try {
        $Post = $request->validated();

        // Create movie
        $Movie = Movie::create([
            'uuid'  => Str::uuid(),
            'title' => $Post['title'],
        ]);

        // Fetch category IDs from the given UUIDs
        $categoryIds = Category::whereIn('uuid', $Post['category_ids'])->pluck('id');

        // Attach the categories to the movie
        foreach ($categoryIds as $categoryId) {
            $Movie->movieCategories()->create([
                'uuid'        => Str::uuid(),
                'category_id' => $categoryId,
            ]);
        }

        return response()->json(['message' => 'Movie created successfully.'], 201);

    } catch (\Throwable $th) {
        return response()->json(['error' => $th->getMessage()], 500);
    }
}

For more efficient approach without additional queries try this:

Modify Relationships to Use UUIDs

Instead of using id, modify your movie_categories relationship to use UUID as the foreign key.

Update MovieCategory.php

class MovieCategory extends Model {
    protected $fillable = ['uuid', 'movie_id', 'category_id'];

    public function movie() {
        return $this->belongsTo(Movie::class, 'movie_id', 'uuid'); // Use UUID
    }

    public function category() {
        return $this->hasOne(Category::class, 'uuid', 'category_id'); // Use UUID
    }
}

Modify Movie.php to Use UUIDs

class Movie extends Model {
    protected $fillable = ['uuid', 'title'];

    public function movieCategories() {
        return $this->hasMany(MovieCategory::class, 'movie_id', 'uuid'); // Use UUID
    }
}

Update the Controller

public function store(MovieRequest $request) {
    try {
        $Post = $request->validated();

        // Create movie with UUID
        $Movie = Movie::create([
            'uuid'  => Str::uuid(),
            'title' => $Post['title'],
        ]);

        // Bulk insert movie_categories using only UUIDs
        $movieCategories = array_map(fn($categoryUuid) => [
            'uuid'        => Str::uuid(),
            'movie_id'    => $Movie->uuid,  // Using UUID directly
            'category_id' => $categoryUuid, // Direct UUID insertion
            'created_at'  => now(),
            'updated_at'  => now(),
        ], $Post['category_ids']);

        // Use bulk insert to minimize queries
        MovieCategory::insert($movieCategories);

        return response()->json(['message' => 'Movie created successfully.'], 201);

    } catch (\Throwable $th) {
        return response()->json(['error' => $th->getMessage()], 500);
    }
}

I think the appropriate "Laravel Way" of implementing it would be to use Laravel's prepareForValidation method, which you will perform the query to replace the category UUID with the ID, so in your store method, the category_ids field already contains the IDs and not UUID.

e.g.

class WhateverReqeust extends FormRequest {

    protected function prepareForValidation() {

        $cat_uuids = $this->input('category_ids', []);
        
        if ( $cat_uuids && is_array($cat_uuids) ) {

            $ids = \Illuminate\Support\Facades\DB::table('categories')
                ->whereIn('uuid', $cat_uuids)
                ->pluck('id')
                ->toArray();
            
            $this->merge(['category_ids' => $ids]);
        }
    }
    
    public function rules() {
        return [
            // Your validation rule
        ];
    }
}

But If you prefer performance without additional query on category table just to get the category ID, you can run raw query after the movie is created in your store method to create the movie category relation.

$movieID = $Movie->id;
$placeholder = implode(',', array_fill(0, count($Post['category_ids']), '?'));

return DB::statement(
    "INSERT INTO movie_categories (movie_id, category_id)
        SELECT $movieID, id
        FROM  categories
        WHERE uuid IN ($placeholder)", 
    $Post['category_ids']
);

本文标签： phpLaravel 9Parent child bulk insert while replacing uuid to idStack Overflow