Unable to use FilterColumn with DB::raw
zainabideengeeko opened this issue · 2 comments
zainabideengeeko commented
public function dataTable(QueryBuilder $query, Request $request): EloquentDataTable
{
return (new EloquentDataTable($query))
->addColumn('title',fn($query) => "$query->makeTitle $query->makeModelTitle $query->year")
->addColumn('body type',fn($query) => $query->bodyTypeTitle)
->addColumn('price', fn($query) => $query->price)
->addColumn('image', function($query) {
$image = asset($query->getFirstMediaUrl('featured_image'));
return '<img src="'.$image.'" alt="user-image" class="rounded-circle"
style="height: 50px;width: 50px;object-fit: cover;">';
})
->addColumn('active',function($query){
$checked = $query->is_active ? "checked" : "";
$switcherRoute = route('cars.toggle',$query->id);
$csrfToken = csrf_token();
$switcher = '
<form action="'.$switcherRoute. '" method="post">
<label class="switch">
<input type="hidden" name="_token" value="'.$csrfToken.'">
<input type="hidden" name="_method" value="PATCH">
<input type="checkbox" id="carActivation'.$query->id.'" class="switch-input" '. $checked .' data-car-id="'.$query->id. '" onclick="carActivation(this,'.$query->id.',this.checked)"/>
<span class="switch-toggle-slider">
<span class="switch-on">
<i class="bx bx-check"></i>
</span>
<span class="switch-off">
<i class="bx bx-x"></i>
</span>
</span>
<span class="switch-label">Active</span>
</label>
</form>
';
return $switcher;
})
->addColumn('created',fn($query) => date('Y-m-d', strtotime($query->created_at)))
->addColumn('bookings',fn($query) => $query->booking_count)
->addColumn('actions',function($query){
$showRoute = route("cars.show", $query->id);
$show = '<button class="btn btn-sm btn-icon show-record">
<a href="'.$showRoute.'">
<i class="fa fa-eye"></i>
</a>
</button>';
$editRoute = route('cars.edit', $query->id);
$edit = '<button class="btn btn-sm btn-icon edit-record">
<a href="'.$editRoute.'">
<i class="fa fa-edit"></i>
</a>
</button>';
$delete = '<button class="btn btn-sm btn-icon delete-record" type="submit" style="color: #5a8dee" data-bs-toggle="modal" data-bs-target="#enableOTPCar'. $query->id.'">
<i class="fa fa-trash"></i>
</button>';
$deleteRoute = route('cars.destroy',$query->id);
$include = view('_partials.deleteModal',[
'id' => 'Car'.$query->id,
'title' => 'Delete car',
'msg' => "Are you sure about deleting {$query->make->title} {$query->makeModel->title} {$query->year}",
'form_action' => $deleteRoute
]);
return $show . $edit . $delete . $include;
})
->rawColumns(['image','actions','active'])
->filterColumn('bookings', function($query, $keyword) {
$keywords = trim($keyword);
$query->havingRaw("COUNT(bookings.id) AS booking_count = ?", ["{$keywords}"]);
})
->orderColumn('title', function ($query, $order) {
$query->orderBy('makeTitle', $order);
})
->orderColumn('body type', function ($query, $order) {
$query->orderBy('bodyTypeTitle', $order);
})
->orderColumn('price', function ($query, $order) {
$query->orderBy('price', $order);
})
->filter(
fn ($query) =>
$query->when(
$request->search['value'] ?? false, fn($query) =>
$query->where('year', 'like', '%' . $request->search['value'] . '%')
->orWhere('price', 'like', '%' . $request->search['value'] . '%')
->orWhere('cars.created_at', 'like', '%' . $request->search['value'] . '%')
// Relations
->orWhereHas('make', fn ($query) =>
$query->where('title', 'like', '%' . $request->search['value'] . '%')
)
->orWhereHas('makeModel', fn ($query) =>
$query->where('title', 'like', '%' . $request->search['value'] . '%')
)
->orWhereHas('bodyType', fn ($query) =>
$query->where('title', 'like', '%' . $request->search['value'] . '%')
)
)
)
->setRowId('id');
}
public function query(Car $model): QueryBuilder
{
$selectColumns = [
'cars.id',
'makes.title as makeTitle',
'body_types.title as bodyTypeTitle',
'price_packages.price as price',
'cars.make_id',
'cars.is_active',
DB::raw('COUNT(bookings.id) AS booking_count'),
'cars.created_at',
'make_models.title as makeModelTitle',
'cars.make_model_id',
'cars.body_type_id',
'cars.price_plan_id',
];
$query = $model->select($selectColumns)->join('makes','makes.id','=','cars.make_id')
->join('make_models','make_models.id','=','cars.make_model_id')
->join('body_types','body_types.id','=','cars.body_type_id')
->join('price_plans','price_plans.id','=','cars.price_plan_id')
->join('bookings','bookings.car_id','=','cars.make_model_id')
->leftJoin('price_packages',fn($join) =>
$join->on('price_packages.price_plan_id','=','price_plans.id')
->where('price_packages.title','default')
)
->groupBy('cars.id', 'makes.title', 'body_types.title', 'price_packages.price', 'cars.make_id', 'cars.is_active', 'cars.created_at', 'make_models.title', 'cars.make_model_id', 'cars.body_type_id', 'cars.price_plan_id');
if(auth()->user()->hasRole('shop')){
$query->where('cars.shop_id',auth()->user()->shop_id);
}
// dd($query->toSql());
return $query;
}
Help to get out of this issue.
I'm using individual column search, the filter column is not working with DB::raw.
These are the versions
"yajra/laravel-datatables": "9.0",
"yajra/laravel-datatables-oracle": "^10.0"
yajra commented
Afaik, having SQL count is not supported by the framework inside a nested where clause.
Workaround, include the having SQL on the query() method.
zainabideengeeko commented
Afaik, having SQL count is not supported by the framework inside a nested where clause.
Workaround, include the having SQL on the query() method.
I'm facing problems with raw queries only.
filter column is from datatable method and raw queries from the query method
please note I'm using individual column searching
DB::raw("CASE WHEN bookings.payment_status = 1 THEN 'Paid' ELSE 'Unpaid' END AS payment_status")
->filterColumn('payment_status', function($query, $keyword) {
$keywords = trim($keyword);
$query->whereRaw("CASE WHEN bookings.payment_status = 1 THEN 'Paid' ELSE 'Unpaid' END LIKE ?", ["%{$keywords}%"]);
})
DB::raw("CONCAT(makes.title,' ',make_models.title) AS car"),
->filterColumn('car', function($query, $keyword) {
$keywords = trim($keyword);
$query->whereRaw("CONCAT(makes.title,' ',make_models.title) like ?", ["%{$keywords}%"]);
})
