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