Laravel Validation class for update method

Question

I have a problem when I try to modify existing data in the database. It is a product category that has a unique slug. When I create a new category I check if there is a slug in the database. When I want to change the existing category, I get an error that the slug is already taken.

I found some examples on StackOveflow but nothing can solve my problem.

---

Structure

category table

category_id, parent_id, status, created_at, ...
-------------------------------------------------- 

category_details

category_id, name, slug, description, ....
-------------------------------------------------- 

Models

class Category extends Model
{
  use HasFactory;
    
  protected $table = "category";
  protected $fillable = [
    'parent_id',
    'image',
    'status',
    'show_in_menu',
    'sort_order'
  ];

  /**
   * Category details table
   */
  public function details()
  {
    return $this->hasOne(CategoryDetails::class, 'category_id', 'id');
  }
}

class CategoryDetails extends Model
{
  use HasFactory;
    
  protected $primaryKey = 'category_id';
  protected $fillable = [
    'category_id',
    'name',
    'slug',
    'description',
    'description_below',
    'meta_title',
    'meta_description'
  ];
}

Form Request Class

public function rules(): array
{
  if($this->isMethod('post')) {
    return [
      'name' => 'required',
      'slug' => 'required|unique:category_details',
      'meta_title' => 'required|max:60',
      'meta_description' => 'max:160',
      'status' => 'accepted'
    ];
  }

  if($this->isMethod('patch')) {
    return [
      'name' => 'required',
      'slug' =>'required|unique:category_details,slug,'.$this->category_id,
      'meta_title' => 'required|max:60',
      'meta_description' => 'max:160',
    ];
  }
}

Controller

public function update(CategoryRequest $request, int $id)
{
  $category = Category::find($id)->first();
    
  $category->update([
    'parent_id' => $request->input('parent_id'),
    'image' =>  '',
    'status' => $request->input('status') == 'on' ? 1 : 0,
    'sort_order' => $request->input('sort_order')
  ]);
    
  $category->details->update([
    'category_id' => $id,
    'name' => $request->input('name'),
    'slug' => $request->input('slug'),
    'description' => $request->input('description'),
    'meta_title' => $request->input('meta_title'),
    'meta_description' => $request->input('meta_description')
  ]);
    
  return redirect()
    ->route('categories.edit', $category->id)
    ->with('success', "Category {$category->details->name} successfully updated");
}

---

Error Message

I received an error during the update event.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause'

select
  count(*) as aggregate
from `category_details`
where `slug` = dekubitus2
  and `id` <> 

Answer 1

I think the problem in this line:

 'slug' =>'required|unique:category_details,slug,'.$this->category_id,

for the unique validation rule, you should provide column name so Laravel check the DB existence. but if you did not pass that parameter, it be by default 'id': change that line to:

'slug' => [
         'required',
          Rule::unique('category_details', 'slug')->ignore($this->category, 'category_id'),
            ],

Answer 2

-- Reflection to your SQL code

I don't understand it... what generated this SQL code?

select
  count(*) as aggregate
from `category_details`
where `slug` = dekubitus2
  and `id` <> 

This SQL code tried found category_details.id but it's not exists on your structure. You used category_details.category_id as primary key on category_details table, and you used it for relationship too.

1.) One To One Relationship on Laravel

hasOne()

return $this->hasOne(Phone::class, 'foreign_key', 'local_key');

Your relation

// From Category (local) To CategoryDetails (foreign)
return $this->hasOne(CategoryDetails::class, 'category_id', 'id');

You used category_details.category_id as foreign_key, and used category.id as local_key. However you don't declared category.id column on your database.

category table structure

category_id, parent_id, status, created_at, ...

If I want follow your structure, need declare category.category_key as local_key on your relationship.

Solution # 1

Need declare your relationship with exists columns.

// Category.php Model

public function details()
{
  return $this->hasOne(CategoryDetails::class, 'category_id', 'category_id');
  //      /\                  /\                    /\             /\
  //   local_model        foreign_model         foreign_key     local_key
}

and need declare your primary key name on your Category model:

protected $primaryKey = 'category_id';

Solution # 2

Or can rename your category.category_id column to category.id. If will choise it, then your relation perfect what here declared.

---

2.) Update with One To One Relationship on Laravel

public function update(CategoryRequest $request, int $id)
{
  // Get category by id
  $category = Category::find($id)->firstOrFail();
  // Use ->firstOrFail() instead of ->first()
  // because if category cannot found, then don't need run update
    
  // Update category (thats ok)
  $category->update([
    'parent_id' => $request->input('parent_id'),
    'image' =>  '',
    'status' => $request->input('status') == 'on' ? 1 : 0,
    'sort_order' => $request->input('sort_order')
  ]);
    
  // Update category's details
  // Can try use ->updateOrCreate() instead of ->update()
  // - I don't know your full logic, so thats just my idea.. if details not exists when you tried update them
  $category->details->updateOrCreate([
    /* 'category_id' => $id, */ /* dont't need declare category_id, because used relationship for update running */
    'name' => $request->input('name'),
    'slug' => $request->input('slug'),
    'description' => $request->input('description'),
    'meta_title' => $request->input('meta_title'),
    'meta_description' => $request->input('meta_description')
  ]);
  
  // Redirect
  return redirect()
    ->route('categories.edit', $category->id)
    ->with('success', "Category {$category->details->name} successfully updated");
}

Answer 3

try this

use Illuminate\Validation\Rule;

return [
    'slug' => ['required', Rule::unique('category_details')->ignore($this->category_id)],
];

Answer 4

Your model for Category, change the ID to the category_id, because from what you have mentioned above, there's no ID in the table structure :

class Category extends Model
{
  use HasFactory;
    
  protected $table = "category";
  protected $fillable = [
    'parent_id',
    'image',
    'status',
    'show_in_menu',
    'sort_order'
  ];

  /**
   * Category details table
   */
  public function details()
  {
    return $this->hasOne(CategoryDetails::class, 'category_id', 'category_id');
  }
}