laravel-datatables Problem sorting when relation belongsTo is null

I use :

        "yajra/laravel-datatables": "^1.5",
        "yajra/laravel-datatables-oracle": "^9.9"

My query :

public function query(Keyword $model)
    {
        return $model->newQuery()
                    ->with('operator')
            ->whereCategory($this->request()->category);

    }

my relation in Model :

public function operator()
    {
        return $this->belongsTo(SubCustomer::class, 'subcustomer_id');
    }

Column:

protected function getColumns()
    {
        return [
           [....]
            Column::make('operator')->name('operator.name'),

        ];
    }

My datatable :

public function dataTable($query)
    {

        return datatables()
            ->eloquent($query)
            ->filterColumn('operator', function($query, $subcustomer) {
                $query->whereHas('operator', function($q) use ($subcustomer) {
                    $q->where('name', 'like', "%$subcustomer%");
                });
            })
            ->editColumn('operator', function(Keyword $keyword) {
                return $keyword->operator ? $keyword->operator->name : '';
            })
             [......]
            ->addColumn('action', function(Keyword $keyword) {
                if (!$keyword->id)
                    dd($keyword);

                $routeRefresh = route('keywords.refresh', $keyword->id);
                $routeSerp = route('keywords.serp', $keyword->id);
                $routeDelete = route('keywords.destroy', $keyword->id);
                $csrf = csrf_token();
                return <<<HTML
                        <div class="d-flex">
                            <form action="$routeRefresh" method="POST">
                                <input type="hidden" name="_token" value="$csrf">
                                <button class="edit btn btn-success btn-sm">  <i class="fas fa-sync"></i></button>
                            </form>
                            <a href="$routeSerp" class="edit btn btn-info btn-sm ml-2">  <i class="fas fa-eye"></i></a>
                            <button class="delete ml-2 btn btn-danger btn-sm" data-remote="$routeDelete"><i class="fas fa-trash"></i> </button>
                        </div>
                    HTML;
            })
            ->rawColumns(['keyword', "action", 'sites', "p0", "keyword"]);
    }

I've the same problem, when filter, so i've added 2 method :

 ->editColumn('operator' ...)
 ->filterColumn('operator', ...)

if my relation is null, My keyword::model lost attribute id .

error: "Exception Message:↵↵Missing required parameters for [Route: keywords.refresh] [URI: keywords/{keyword}/refresh].

If you have a solution. Thk lot of

Jun 16 '20 13:06 Djomobil

My dump from keyword :

->addColumn('action', function(Keyword $keyword) {
                if (!$keyword->id)
                    dd($keyword);
--------------------

App\Keyword {#1280
  #guarded: []
  #dispatchesEvents: []
  #connection: "mysql"
  #table: "keywords"
  #primaryKey: "id"
  #keyType: "int"
  +incrementing: true
  #with: []
  #withCount: []
  #perPage: 15
  +exists: true
  +wasRecentlyCreated: false
  #attributes: array:27 [
    "id" => null
    "keyword" => "genting bet promo code"
    "category" => "0"
    "potential" => 300
    "lang" => "English"
    "prio" => 1
    "se_id" => 22
    "loc_id" => 2826
    "serpurl" => "https://www.google.co.uk"
    "search_engine" => "google.co.uk"
    "tasks_id" => 16618663558
    "comment" => null
    "lastcheck_at" => null
    "subcustomer_id" => null
    "created_at" => null
    "updated_at" => null
    "name" => null
    "deal" => null
    "referral_code" => null
    "url" => null
    "contact" => null
    "email" => null
    "skype" => null
    "phone" => null
    "user_id" => null
    "market_id" => null
    "customer_id" => null
  ]
  #original: array:27 [
    "id" => null
    "keyword" => "genting bet promo code"
    "category" => "0"
    "potential" => 300
    "lang" => "English"
    "prio" => 1
    "se_id" => 22
    "loc_id" => 2826
    "serpurl" => "https://www.google.co.uk"
    "search_engine" => "google.co.uk"
    "tasks_id" => 16618663558
    "comment" => null
    "lastcheck_at" => null
    "subcustomer_id" => null
    "created_at" => null
    "updated_at" => null
    "name" => null
    "deal" => null
    "referral_code" => null
    "url" => null
    "contact" => null
    "email" => null
    "skype" => null
    "phone" => null
    "user_id" => null
    "market_id" => null
    "customer_id" => null
  ]
  #changes: []
  #casts: []
  #classCastCache: []
  #dates: []
  #dateFormat: null
  #appends: []
  #observables: []
  #relations: array:1 [
    "operator" => null
  ]
  #touches: []
  +timestamps: true
  #hidden: []
  #visible: []
  #fillable: []
}

Jun 16 '20 13:06 Djomobil

Try using nullable pattern by setting default for null relations.

return $this->belongsTo(SubCustomer::class, 'subcustomer_id')->withDefault();

Jun 17 '20 02:06 yajra

The same problem with your fix:

return $this->belongsTo(SubCustomer::class, 'subcustomer_id')->withDefault();

result Model :

#original: array:27 [
    "id" => null
    "keyword" => "genting bet promo code"
    "category" => "0"
    "potential" => 300
    "lang" => "English"
    "prio" => 1
    "se_id" => 22
    "loc_id" => 2826
    "serpurl" => "https://www.google.co.uk/search?q=genting%20bet%20promo%20code&num=100&hl=en&gl=GB&gws_rd=cr&ie=UTF-8&oe=UTF-8&uule=w+CAIQIFISCamRx0IRO1oCEXoliDJDoPjE"
    "search_engine" => "google.co.uk"
    "tasks_id" => 16618663558
    "comment" => null
    "lastcheck_at" => null
    "subcustomer_id" => null
    "created_at" => null
    "updated_at" => null
    "name" => null
    "deal" => null
    "referral_code" => null
    "url" => null
    "contact" => null
    "email" => null
    "skype" => null
    "phone" => null
    "user_id" => null
    "market_id" => null
    "customer_id" => null
  ]
  #changes: []
  #casts: []
  #classCastCache: []
  #dates: []
  #dateFormat: null
  #appends: []
  #observables: []
  #relations: array:1 [
    "operator" => App\SubCustomer {#1279
      #guarded: []
      #with: array:1 [
        0 => "market"
      ]
      #connection: null
      #table: "sub_customers"
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #withCount: []
      #perPage: 15
      +exists: false
      +wasRecentlyCreated: false
      #attributes: []
      #original: []
      #changes: []
      #casts: []
      #classCastCache: []
      #dates: []
      #dateFormat: null
      #appends: []
      #dispatchesEvents: []
      #observables: []
      #relations: []
      #touches: []
      +timestamps: true
      #hidden: []
      #visible: []
      #fillable: []
    }
  ]

im result network :

error: "Exception Message:↵↵Missing required parameters for [Route: keywords.refresh] [URI: keywords/{keyword}/refresh].

Jun 17 '20 08:06 Djomobil

@yajra an idea ?

Jun 18 '20 08:06 Djomobil

@yajra I've got the same problem, do you have any idea / fix?

Sep 10 '21 15:09 Restingo

@Djomobil @Restingo

error: "Exception Message:↵↵Missing required parameters for [Route: keywords.refresh] [URI: keywords/{keyword}/refresh].

I think the issue here is on your route and not the relation sorting per se. Since your loaded relation is null object, then the {keyword} parameter is also null thus making the route helper raise an error?

Sep 11 '21 01:09 yajra

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

Jan 11 '23 01:01 github-actions[bot]

This issue was closed because it has been inactive for 7 days since being marked as stale.

Jan 18 '23 01:01 github-actions[bot]

laravel-datatables laravel-datatables copied to clipboard

Problem sorting when relation belongsTo is null

laravel-datatables
laravel-datatables copied to clipboard