eloquent($query) ->addColumn('total_amount',function ($row){ $total = 0.0; $row->creditHistory->each(function ($credit) use (&$total){ $total += $credit->credited; }); return $row->role_id == '3' ? $total : $total; })->addColumn('current_balance',function ($row){ return $row->credit->new_bal ?? 0.0; }) ->addColumn('last_purchase_date',function ($row){ $date = $row->creditHistory()->latest()->first()->updated_at ?? ''; return $date; }) ->addColumn('username', function ($row) { return ''.$row->username.''; })->rawColumns(['username']); } /** * Get the query object to be processed by dataTables. * * @return \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder|\Illuminate\Support\Collection */ public function query() { if($this->request()->get('type')) { $type = $this->request()->get('type'); $roleId = strtolower($type) == 'reseller' ? Role::where('name', 'reseller')->firstOrFail()->id : Role::where('name', 'user')->firstOrFail()->id; // $query = User::query()->whereHas('company',function ($row) { // $row->where($row->company->company_status->status, 1); // })->where('role_id', '=', $roleId); $query = User::query()->whereHas('company',function ($row) { $row->whereHas('company_status',function ($row){ $row->where('status',1); }); })->where('role_id', '=', $roleId); } else { $query = User::query()->where('id',0); } return $this->applyScopes($query); } /** * Optional method if you want to use html builder. * * @return \Yajra\Datatables\Html\Builder */ public function html() { return $this->builder() ->columns($this->getColumns()) ->ajax('') ->dom('Bfrtip') ->buttons( Button::make('export'), Button::make('print'), Button::make('reset'), Button::make('reload') ); } /** * Get columns. * * @return array */ protected function getColumns() { return [ 'username' => ['title' => 'Client Name'], 'organisation' => ['title' => 'Organization'], 'full_name', 'total_amount' => ['title' => 'Total Amount Purchased'], 'current_balance', 'last_purchase_date', ]; } /** * Get filename for export. * * @return string */ protected function filename() { return 'adminclienthist_' . time(); } }