I’m using yajra datatables v10 with laravel v9. I’m facing some issues as i want to enable seperate search on each column. This thing is done the search is only works for id, email, total paid columns but search is not working for car, payment status, booking status, pickup date columns.
I have tried many solution i can get from gpt, bard.
Please review my code and help me to get the solution.
public function dataTable(QueryBuilder $query,Request $request): EloquentDataTable
{
return (new EloquentDataTable($query))
->addColumn('email',fn($query) => $query->email)
->addColumn('total paid',fn($query) => $query->total_paid)
->addColumn('car',fn($query) => $query->makeTitle .' '. $query->makeModelTitle)
->addColumn('payment status',fn($query) => $query->payment_status == 1 ? 'Paid' : 'Unpaid')
->addColumn('pickup date', fn ($query) => $query->pickup_date)
->addColumn('booking status',function($query) {
$status = $query->booking_status;
$bgColor = 'bg-warning';
if($status == 'cancelled') $bgColor = 'bg-danger';
elseif($status == 'confirmed') $bgColor = 'bg-success';
return "<span class='badge $bgColor'>$status</span>";
})
->addColumn('actions',function($query) {
$status = $query->booking_status;
$returnValue = '<div class="d-flex justify-between">';
if($status == 'pending'){
$confirmRoute = route('bookings.confirm', $query->id);
$cancelRoute = route('bookings.cancel', $query->id);
$token = csrf_token();
$confirmForm = '<form action="'.$confirmRoute.'" method="POST">
<input type="hidden" name="_token" value="'.$token.'">
<button type="submit" class="btn btn-sm btn-icon btn-success" title="approve">
<i class="fa fa-check-circle"></i>
</button>
</form>';
$cancelForm = '<form action="'.$cancelRoute.'" method="POST">
<input type="hidden" name="_token" value="'.$token.'">
<button type="submit" class="btn btn-sm btn-icon btn-danger" title="reject">
<i class="fa fa-times-circle"></i>
</button>
</form>';
$returnValue .= $confirmForm . $cancelForm ;
}
$showRoute = route('bookings.show', $query->id);
$showButton = '<div class="d-inline-block text-nowrap">
<button class="btn btn-sm btn-icon show-record">
<a href="'.$showRoute.'">
<i class="fa fa-eye"></i>
</a>
</button>';
$returnValue .= $showButton ;
if(auth()->user()->hasRole('admin')){
$editRoute = route('bookings.edit', $query->id);
$editButton = '<button class="btn btn-sm btn-icon edit-record"><a href="'.$editRoute.'"><i
class="fa fa-edit"></i></a></button>';
$returnValue .= $editButton ;
}
return $returnValue.'</div>';
})
->rawColumns(['booking status','actions'])
// order
->orderColumn('email', function ($query, $order) {
$query->orderBy('email', $order);
})
->orderColumn('total paid', function ($query, $order) {
$query->orderBy('total_paid', $order);
})
->orderColumn('car', function ($query, $order) {
$query->orderBy('makeTitle', $order);
})
->orderColumn('payment status', function ($query, $order) {
$query->orderBy('payment_status', $order);
})
->orderColumn('booking status', function ($query, $order) {
$query->orderBy('booking_status', $order);
})
// search
->filter(function ($query) use ($request) {
$searchValue = $request->search['value'] ?? false;
$startDate = $request->start_date ?? false;
$endDate = $request->end_date ?? false;
$query->where(fn ($query) =>
$query->when($searchValue, fn ($query) =>
$query->where('email', 'like', "%$searchValue%")
->orWhere('booking_status', 'like', "%$searchValue%")
->orWhere('total_paid', 'like', "%$searchValue%")
->orWhere('booking_status', 'like', "%$searchValue%")
->orWhereHas('car.make', fn ($query) =>
$query->where('title', 'like', "%$searchValue%")
)
->orWhereHas('car.makeModel', fn ($query) =>
$query->where('title', 'like', "%$searchValue%")
)
)
->when($startDate && $endDate, fn($query) => $query->whereBetween('bookings.pickup_date', [$startDate, $endDate])
));
})
->setRowId('id');
}
public function query(Booking $model): QueryBuilder
{
// return $model->newQuery();
$selectColumns = [
'bookings.id',
'bookings.email',
'bookings.total_paid',
'bookings.payment_status',
'bookings.booking_status',
'bookings.pickup_date',
'makes.title as makeTitle',
'make_models.title as makeModelTitle',
];
$query = $model->select($selectColumns)->join('cars','cars.id','=','bookings.car_id')
->join('makes','makes.id','=','cars.make_id')
->join('make_models','make_models.id','=','cars.make_model_id')
->where('bookings.payment_status', '<>', 0);
if(auth()->user()->hasRole('shop')){
$shopCarsIds = auth()->user()->shop->cars->pluck('id');
$query->whereIn('car_id',$shopCarsIds);
} elseif (auth()->user()->hasRole('partner')){
$partner_id = auth()->user()->partner_id;
$query->where('partner_id',$partner_id);
}
return $query;
}
public function html(): HtmlBuilder
{
return $this->builder()
->setTableId('bookings-table')
->columns($this->getColumns())
->minifiedAjax()
->ajax([
'url' => route('bookings.index'),
'type' => 'GET',
'data' => 'function(d) {d.start_date = start_date, d.end_date = end_date}'
])
->dom('Bflrtip')
->orderBy(0,'asc')
->parameters([
"pageLength" => "10",
'initComplete' => 'function () {
var table = this.api();
var searchRow = $("<tr/>").appendTo($(table.table().header()));
table.columns().every(function () {
var column = this;
var searchCell = $("<th/>").appendTo(searchRow);
var headerText = column.header().innerText;
if (headerText.includes("ACTIONS")) {
// Exclude action button column
return;
}
var input = document.createElement("input");
var placeholder = "";
var dataColumn = "";
// Set attributes based on column name
switch (column.header().innerText) {
case "ID":
placeholder = "Search ID";
dataColumn = "id";
break;
case "EMAIL":
placeholder = "Search Email";
dataColumn = "email";
break;
case "TOTAL PAID":
placeholder = "Search Paid";
dataColumn = "paid";
break;
case "CAR":
placeholder = "Search Car";
dataColumn = "car";
break;
case "PICKUP DATE":
placeholder = "Search Pickup date";
dataColumn = "pickup date";
break;
case "PAYMENT STATUS":
placeholder = "Search Payment Status";
dataColumn = "payment status";
break;
case "BOOKING STATUS":
placeholder = "Search Booking Status";
dataColumn = "booking status";
break;
}
$(input).appendTo(searchCell)
.attr("type", "text")
.addClass("form-control dt-input")
.attr("placeholder", placeholder)
.attr("data-column", dataColumn)
.attr("data-column-index", column.index())
.on("keyup change clear", function () {
var searchValue = $(this).val();
var searchRegex = column.search() instanceof RegExp ?
searchValue :
$.fn.dataTable.util.escapeRegex(searchValue);
column.search(searchRegex, false, false, true).draw();
});
});
}'
]);
}
public function getColumns(): array
{
return [
Column::make('id'),
Column::make('email',true),
Column::make('total paid', true),
Column::make('car', true),
Column::make('pickup date', true),
Column::make('payment status', true),
Column::make('booking status', true),
Column::make('actions'),
];
}