phplaravelinsertrelationship

Laravel 9, Parent child bulk insert while replacing uuid to id


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.


Solution

  • 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']
    );