I am using Yajra Datatables to fetch the data inside my table.
I need to display the data of the table accreditors wherein the nonofficial_category_id column for the said table should display the area_name equivalent of it from another database table non_officials_categories.
Here is the Accreditors Model showing its relationship with NonOfficialsCategories Model:
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
class Accreditors extends Model
{
use HasFactory;
use SoftDeletes;
protected $table = "accreditors";
protected $fillable = [
'name',
'official_category_id',
'subcategory',
'position',
'photo',
];
protected $dates = [
'deleted_at',
'created_at',
'updated_at'
];
public function nonOfficialsCategories()
{
return $this->belongsTo(NonOfficialsCategories::class, 'nonofficial_category_id');
}
}
Here is the method indexDatatables inside AccreditorsController which would display the datatable:
namespace App\Http\Controllers;
use App\Models\Accreditors;
use App\Models\NonOfficialsCategories;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Storage;
class AccreditorsController extends Controller
{
public function indexDatatables()
{
$model = Accreditors::with('nonOfficialsCategories')->select('accreditors.*');
if (request()->ajax()) {
return datatables()->of($model)
->addColumn('action', function ($data) {
return $this->getActionColumn($data);
})
->rawColumns(['action'])
->make(true);
}
$categoryCount = NonOfficialsCategories::count();
return view('personnels.nonofficials.accreditors.datatables.datatables')->with('categoryCount', $categoryCount);
}
}
Here is the NonOfficialsCategories Model showing its relationship with Accreditors Model:
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
class NonOfficialsCategories extends Model
{
use HasFactory;
use SoftDeletes;
protected $table = "non_officials_categories";
protected $fillable = [
'area_number',
'area_name',
'subcategory',
'is_accreditor_head',
'is_taskforce_head',
'is_auditor',
'use_counter',
];
protected $dates = [
'deleted_at',
'created_at',
'updated_at'
];
public function taskforces()
{
return $this->has(TaskForces::class);
}
public function accreditors()
{
return $this->has(Accreditors::class);
}
}
Here is the excerpt of the .blade file for the accreditors:
<div class="container-fluid d-flex justify-content-center">
<div class="row">
<table class="poppins-normal text-md" id="accreditorsDatatables">
<thead>
<tr>
{{-- <th class="text-white">Id</th> --}}
<th class="text-white">Name</th>
<th class="text-white">Area Name</th>
<th class="text-white">Subcategory</th>
<th class="text-white">Position</th>
<th class="text-white">Actions</th>
</tr>
</thead>
</table>
</div>
</div>
<script>
$(document).ready( function () {
$.ajaxSetup({
headers: {
'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
}
});
$('#accreditorsDatatables').DataTable({
processing: true,
serverSide: true,
ajax: '{!! route("accreditors-datatables") !!}',
columns: [
// {data: 'id', name: 'id'},
{data: 'name', name: 'name'},
{data: 'nonofficial_category_id', name: 'nonOfficialsCategories.area_name'},
{data: 'subcategory', name: 'subcategory'},
{data: 'position', name: 'position'},
{data: 'action', name: 'action', orderable: false},
]
});
});
</script>
Here is the current (and wrong) display of the datatables as it is:
__________________________________________________________________________
|name |area name |subcategory |position |actions |
__________________________________________________________________________
| |1 //wrong | | | |
__________________________________________________________________________
| |2 //wrong | | | |
__________________________________________________________________________
| |3 //wrong | | | |
__________________________________________________________________________
wherein the display should be:
__________________________________________________________________________
|name |area name |subcategory |position |actions |
__________________________________________________________________________
| |Area I //right | | | |
__________________________________________________________________________
| |Area II //right | | | |
__________________________________________________________________________
| |Area III //right | | | |
__________________________________________________________________________
My mistake might be the following:
.blade section in the columns array of the script section.indexDatatables method for the AccreditorsController.Thank you.
After checking out my mistake, I found the solution.
I need to add another addColumn() API on the indexDatatables():
public function indexDatatables()
{
$model = Accreditors::with('nonOfficialsCategories')->select('accreditors.*');
if (request()->ajax()) {
return datatables()->of($model)
->addColumn('action', function($data) {
return $this->getActionColumn($data);
})
->addColumn('area_name', function (Accreditors $accreditors) { // the added column
return $accreditors->nonOfficialsCategories->area_name;
})
->rawColumns(['action'])
->make(true);
}
$categoryCount = NonOfficialsCategories::count();
return view('personnels.nonofficials.accreditors.datatables.datatables')->with('categoryCount', $categoryCount);
}
Then I could just use the following line on the script section of the .blade file:
// format {data: 'name_parameter_in_add_column', name: 'relationshipName.column_name_to_be_searched'},
{data: 'area_name', name: 'nonOfficialsCategories.nonofficial_category_id'},
// the 'area_name' in the script section should be the same name declared in the first parameter of the "addColumn()" API
Here is the link for the documentation reference.
Now, I could still be wrong on the relationship declaration because I still confuse myself on the usage of belongsTo()/belongsToMany() API. If someone could correct me, I would appreciate it.