Laravel problem with delete() function from model

Question

I want to delete a record from Categories using delete function from laravel model but this function make me a strange query that doesn't make sens. So, i got an error with (sql constraint fail) as a result. And Laravel do this following weird query : delete from t_category where t_category.catLeft > 505 and t_category.catRight < 518 order by t_category.catLeft asc

Why laravel doesn't search for the id and delete the first record found ?

My controller : remove function

/**
   * Remove the category
   * @param  Request $request
   * @param  String  $no      category's no
   * @return \Illuminate\Http\RedirectResponse Redirect to the list of category
   */
  public function remove(Request $request, $no){
    // Get the category
    $category = Category::findByNo($no);

    if ($category == null)
      return redirect('/gest/categories');

    if ($category->isRoot())
      return redirect('/gest/categories');

    $parentID  = Category::where('catParentId', $category->idCategory)->get();

    try {

      if(empty($parentID[0]["catNo"]))
      {
        $category->delete();
        
        $request->session()->flash('message', ['result' => true,'title' => Lang::get('gest.deleteSucces.title'), 'msg' => Lang::get('gest.deleteSucces.msg', array('model' => $category->catNo.' - '.$category->catName))]);
      }
      else
      {

        $request->session()->flash('message', ['result' => false,'title' => Lang::get('gest.deleteCategoryParent.title'), 'msg' =>  Lang::get('gest.deleteCategoryParent.msg', array('category' => $category->catNo.' - '.$category->catName))]);
        return redirect('/gest/categories');
      }

    }
    catch (\Illuminate\Database\QueryException $e) {
      if ($e->errorInfo[1] == "1451")// SQL code 1451 = Constraint fail
          $request->session()->flash('message', ['result' => false,'title' => Lang::get('gest.deleteCategoryEchecContraintFail.title'), 'msg' =>  Lang::get('gest.deleteCategoryEchecContraintFail.msg', array('category' => $category->catNo.' - '.$category->catName))]);
      else
        $request->session()->flash('message', ['result' => false,'title' =>Lang::get('gest.deleteModelEchecUnknownError.title'), 'msg' =>  Lang::get('gest.deleteModelEchecUnknownError.msg', array('model' => $category->catNo.' - '.$category->catName))]);
    }
    return redirect()->back();
  }

Category Model :

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use App\Exceptions\InvalidArgumentExceptionClass;
use App\Traits\Uuids;
use Baum\NestedSet\Node;

class Category extends Model {
  use Node;
  use Uuids;

  /**
   * The attributes that are mass assignable.
   *
   * [See laravel seeding documentation](https://laravel.com/docs/5.7/eloquent)
   * @var array
   */
  protected $fillable = ['catNo', 'catName'];

  /**
   * The attributes that aren't mass assignable.
   * @var array
   */
  protected $guarded = ['idCategory','catParentId','catLeft','catRight','catDepth'];

  /**
   * The attributes that should be hidden for arrays.
   * @var array
   */
  protected $hidden = ['idCategory','catParentId'];

  /**
   * The table associated with the model.
   * @var String
   */
  protected $table = 't_category';

  /**
   * The "type" of the primary key ID.
   *
   * @var string
   */
  protected $keyType = 'string';

  /**
   * The primary key for the model.
   * @var String
   */
  protected $primaryKey = "idCategory";

  /**
   * Indicates if the IDs are auto-incrementing.
   * @var boolean
   */
  public $incrementing = false;

  /**
   * Indicates if the timestamps crated_at and updated_at are saved.
   * @var boolean
   */
  public $timestamps = false;

  // 'parent_id' column name
  protected $parentColumnName = 'catParentId';

  // 'lft' column name
  protected $leftColumnName = 'catLeft';

  // 'rgt' column name
  protected $rightColumnName = 'catRight';

  // 'depth' column name
  protected $depthColumnName = 'catDepth';


  /**
   * Get the kits that belong to the category
   *
   * @return array Kit
   */
  public function kits() {
      return $this->hasMany(Kit::class,'fkCategory')
        ->orderBy('kitNo','asc');
  }

  /**
   * Get the Additional Items that belong to the category
   *
   * @return array AddItem
   */
  public function addItems() {
      return $this->hasMany(AddItem::class,'fkCategory')
        ->orderBy('adiName','asc');
  }

  /**
   * Create an Category
   *
   * @param array $attributes
   *
   * @return \App\Models\Category
   *
   * @throws \App\Exceptions\InvalidArgumentExceptionClass
   */
  public static function create(array $attributes = []) {
      // Check if not duplicate No
      if (static::where('catNo', $attributes['catNo'])->first()) {
        throw InvalidArgumentExceptionClass::modelWithNoAlreadyExist(Category::class,$attributes['catNo']);
      }

      return static::query()->create($attributes);
  }

  /**
   * Find a Category by its id
   *
   * @param string $idCategory
   *
   * @return \App\Models\Category|null
   */
  public static function findById(string $idCategory) {
      $category = static::where('idCategory', $idCategory)->first();

      if (! $category) {
          return null;
      }

      return $category;
  }

  /**
   * Find a Category by its No
   *
   * @param string $catNo
   *
   * @return \App\Models\Category|null
   */
  public static function findByNo(string $catNo) {
      $category = static::where('catNo', $catNo)->first();

      if (! $category) {
          return null;
      }

      return $category;
  }
}

I try the destroy function passing id of the category in the function but same error

Answer 1

The reason for the query is that your model is using the Baum\NestedSet\Node trait.

Given that deleting a single node may require the deletion of related nodes, nested set builds the query appropriate for the instance you're aiming to delete.

To get a more simple DELETE FROM table WHERE id = x query, you'd need to remove the Node trait but that would break other functionality that you want to keep.

However, I don't think your problem is the delete query itself. You said :

So, i got an error with (sql constraint fail) as a result

This is likely to be a foreign key constraint error, which means that one of your other models has a dependency on your Category model. If you check your migrations you'll likely see something like

$table->foreign('category_id')->references('id')->on('categories');

Or

$table->foreignId('category_id')->constrained();

This means that before you can delete the Category, you need to delete any models attached to that category. Or you can define an ON DELETE behaviour such as

$table->foreignId('category_id')
      ->constrained()
      ->onDelete('cascade');

Which means that when you delete the Category instance, any related instances will also be deleted.

The other option is to remove your foreign key constraint in the DB, either by editing the migration and migrating your DB fresh or by creating a migration to alter the constrained tables.

The documentation here should help