I am returning a users datatable in a laravel datatable like so:
<?php
namespace App\DataTables\Users;
use App\Models\User;
use App\Models\Accounting;
use Illuminate\Support\Carbon;
use Yajra\DataTables\Html\Button;
use Yajra\DataTables\Html\Column;
use Illuminate\Support\Facades\DB;
use Yajra\DataTables\EloquentDataTable;
use Yajra\DataTables\Services\DataTable;
use Yajra\DataTables\Html\Builder as HtmlBuilder;
use Illuminate\Database\Eloquent\Builder as QueryBuilder;
class UsersDataTable extends DataTable
{
/**
* Build DataTable class.
*
* @param QueryBuilder $query Results from query() method.
* @return \Yajra\DataTables\EloquentDataTable
*/
public function dataTable(QueryBuilder $query): EloquentDataTable
{
return (new EloquentDataTable($query))
->addColumn('name', function ($user) {
return view('webinars.users.name', compact('user'));
})
->addColumn('email', function ($user) {
return maskEmail($user->email);
})
->addColumn('status', function ($user) {
return view('webinars.users.status', compact('user'));
})
->addColumn('cadre_name', function ($user) {
$cadre = DB::table('cadres')
->where('id', $user->cadre)
->first();
return $cadre ? $cadre->name : 'N/A';
})
->addColumn('webinars_attended', function ($user) {
$count = DB::table('invoices')
->where('email', $user->email)
->count();
return $count;
})
->addColumn('last_webinar', function ($user) {
$lastInvoice = DB::table('invoices')
->where('email', $user->email)
->orderBy('created_at', 'desc')
->first();
if (!$lastInvoice) {
return 'N/A';
}
$webinar = DB::table('webinars')
->where('webinar_id', $lastInvoice->webinar_id)
->first();
if (!$webinar) {
return 'N/A';
}
return Carbon::parse($webinar->start_date)->format('d/m/Y');
})
->addColumn('registered_date', function ($user) {
return Carbon::parse($user->created_at)->format('d/m/Y');
})
->addColumn('action', function ($user) {
return view('webinars.users.action', compact('user'));
})
->addColumn('balance', function ($user) {
return Accounting::getUnitDifference($user->id);
})
->setRowId('id');
}
/**
* Get query source of dataTable.
*
* @param \App\Models\User $model
* @return \Illuminate\Database\Eloquent\Builder
*/
public function query(User $query): QueryBuilder
{
return $query->when($this->request->search['value'] ?? false, function ($query, $search) {
$query->where(function ($query) use ($search) {
$query->where('first_name', 'like', "%$search%")
->orWhere('last_name', 'like', "%$search%")
->orWhere('email', 'like', "%$search%")
->orWhere('phone', 'like', "%$search%")
->orWhereExists(function ($subquery) use ($search) {
$subquery->from('cadres')
->whereRaw('cadres.id = users.cadre')
->where('cadres.name', 'like', "%$search%");
});
});
})->newQuery();
}
/**
*
* @return \Yajra\DataTables\Html\Builder
*/
public function html(): HtmlBuilder
{
return $this->builder()
->columns($this->getColumns())
->minifiedAjax()
->orderBy(1)
->scrollCollapse()
->selectStyleSingle()
->setTableAttribute('class', 'table table-striped table-bordered table-hover')
->setTableAttribute('style', 'width:98%')
->parameters([
'dom' => '<"row justify-between mb-2"<"col-sm-6 col-md-4"l><"col-sm-6 col-md-8"f>>t<"row"<"col-sm-12 col-md-5"i><"col-sm-12 col-md-7"p>>',
'drawCallback' => 'function(settings) {
$("#dataTableBuilder tbody tr td").each(function (index) {
var class_name = $("thead th").eq(index).attr("class");
var text = $("thead th").eq(index).text();
if (class_name && class_name.includes("sg_td")) {
$(this).attr("data-column", text);
}
});
}',
'buttons' => [
'copy',
'excel',
'csv',
'print'
],
'lengthMenu' => [[5, 10, 25, 50, 100, 250, -1], [5, 10, 25, 50, 100, 250, 'All']],
'language' => [
'searchPlaceholder' => __('Search users...'),
'lengthMenu' => '_MENU_ ' . __('Users per page'),
'search' => '',
'zeroRecords' => __('No records found'),
'info' => __('Showing _START_ to _END_ of _TOTAL_ entries'),
'infoEmpty' => __('Showing 0 to 0 of 0 entries'),
'infoFiltered' => __('(filtered from _MAX_ total entries)')
],
'pagingType' => 'full_numbers',
'pageLength' => 10,
]);
}
public function getColumns(): array
{
return [
Column::computed('name')->title(__('Name')),
Column::computed('email')->title(__('Email')),
Column::computed('cadre_name')->title(__('Cadre')),
Column::make('country')->title(__('Country')),
Column::computed('status')->title(__('Status')),
Column::computed('balance')->title(__('Balance')),
Column::computed('webinars_attended')->title(__('Webinars')),
Column::computed('registered_date')->title(__('Registered')),
Column::computed('action')
->exportable(false)
->printable(false)
->width(60)
->addClass('text-center'),
];
}
protected function filename(): string
{
return 'Users_' . date('YmdHis');
}
}
however, on the datatable view, i am unable to search for an email while it's masked, i have tried to have a hidden email column that is searchable but it does not work, also changing the column to make instead of computed and vice versa does not work too.
below is a filter sample for a demo email while it's masked
this format of the datatable where the email is not explicitly added like so:
<?php
namespace App\DataTables\Users;
use App\Models\User;
use App\Models\Accounting;
use Illuminate\Support\Carbon;
use Yajra\DataTables\Html\Button;
use Yajra\DataTables\Html\Column;
use Illuminate\Support\Facades\DB;
use Yajra\DataTables\EloquentDataTable;
use Yajra\DataTables\Services\DataTable;
use Yajra\DataTables\Html\Builder as HtmlBuilder;
use Illuminate\Database\Eloquent\Builder as QueryBuilder;
class UsersDataTable extends DataTable
{
/**
* Build DataTable class.
*
* @param QueryBuilder $query Results from query() method.
* @return \Yajra\DataTables\EloquentDataTable
*/
public function dataTable(QueryBuilder $query): EloquentDataTable
{
return (new EloquentDataTable($query))
->addColumn('name', function ($user) {
return view('webinars.users.name', compact('user'));
})
->addColumn('status', function ($user) {
return view('webinars.users.status', compact('user'));
})
->addColumn('cadre_name', function ($user) {
$cadre = DB::table('cadres')
->where('id', $user->cadre)
->first();
return $cadre ? $cadre->name : 'N/A';
})
->addColumn('webinars_attended', function ($user) {
$count = DB::table('invoices')
->where('email', $user->email)
->count();
return $count;
})
->addColumn('last_webinar', function ($user) {
$lastInvoice = DB::table('invoices')
->where('email', $user->email)
->orderBy('created_at', 'desc')
->first();
if (!$lastInvoice) {
return 'N/A';
}
$webinar = DB::table('webinars')
->where('webinar_id', $lastInvoice->webinar_id)
->first();
if (!$webinar) {
return 'N/A';
}
return Carbon::parse($webinar->start_date)->format('d/m/Y');
})
->addColumn('registered_date', function ($user) {
return Carbon::parse($user->created_at)->format('d/m/Y');
})
->addColumn('action', function ($user) {
return view('webinars.users.action', compact('user'));
})
->addColumn('balance', function ($user) {
return Accounting::getUnitDifference($user->id);
})
->setRowId('id');
}
/**
* Get query source of dataTable.
*
* @param \App\Models\User $model
* @return \Illuminate\Database\Eloquent\Builder
*/
public function query(User $query): QueryBuilder
{
return $query->when($this->request->search['value'] ?? false, function ($query, $search) {
$query->where(function ($query) use ($search) {
$query->where('first_name', 'like', "%$search%")
->orWhere('last_name', 'like', "%$search%")
->orWhere('email', 'like', "%$search%")
->orWhere('phone', 'like', "%$search%")
->orWhereExists(function ($subquery) use ($search) {
$subquery->from('cadres')
->whereRaw('cadres.id = users.cadre')
->where('cadres.name', 'like', "%$search%");
});
});
})->newQuery();
}
/**
*
* @return \Yajra\DataTables\Html\Builder
*/
public function html(): HtmlBuilder
{
return $this->builder()
->columns($this->getColumns())
->minifiedAjax()
->orderBy(1)
->scrollCollapse()
->selectStyleSingle()
->setTableAttribute('class', 'table table-striped table-bordered table-hover')
->setTableAttribute('style', 'width:98%')
->parameters([
'dom' => '<"row justify-between mb-2"<"col-sm-6 col-md-4"l><"col-sm-6 col-md-8"f>>t<"row"<"col-sm-12 col-md-5"i><"col-sm-12 col-md-7"p>>',
'drawCallback' => 'function(settings) {
$("#dataTableBuilder tbody tr td").each(function (index) {
var class_name = $("thead th").eq(index).attr("class");
var text = $("thead th").eq(index).text();
if (class_name && class_name.includes("sg_td")) {
$(this).attr("data-column", text);
}
});
}',
'buttons' => [
'copy',
'excel',
'csv',
'print'
],
'lengthMenu' => [[5, 10, 25, 50, 100, 250, -1], [5, 10, 25, 50, 100, 250, 'All']],
'language' => [
'searchPlaceholder' => __('Search users...'),
'lengthMenu' => '_MENU_ ' . __('Users per page'),
'search' => '',
'zeroRecords' => __('No records found'),
'info' => __('Showing _START_ to _END_ of _TOTAL_ entries'),
'infoEmpty' => __('Showing 0 to 0 of 0 entries'),
'infoFiltered' => __('(filtered from _MAX_ total entries)')
],
'pagingType' => 'full_numbers',
'pageLength' => 10,
]);
}
public function getColumns(): array
{
return [
Column::computed('name')->title(__('Name'))->searchable(true),
Column::computed('email')->title(__('Email'))->searchable(true),
Column::computed('cadre_name')->title(__('Cadre')),
Column::make('country')->title(__('Country')),
Column::computed('status')->title(__('Status')),
Column::computed('balance')->title(__('Balance')),
Column::computed('webinars_attended')->title(__('Webinars')),
Column::computed('registered_date')->title(__('Registered')),
Column::computed('action')
->exportable(false)
->printable(false)
->width(60)
->addClass('text-center'),
];
}
protected function filename(): string
{
return 'Users_' . date('YmdHis');
}
}
The email is searchable or can be filtered as shown below
