php - How to search "concatenated column" in yajra datable?

956

I am using datatable class on my controller and used it to draw the datatable in which i have a concatenated "Name" column. so the default search dosent work .i couldn't find example for the custom filter in this scenario

//This is the datatable. i tried to use filterColumn() on dataTable function but it said no method found.

namespace App\DataTables;

use App\User;
use Illuminate\Support\Facades\DB;
use Yajra\DataTables\Services\DataTable;

class UsersDatatable extends DataTable
{

  public function dataTable($query)
  {
    return datatables($query)
      ->addColumn('action', function ($users) {
        return '<a href="'.route('users.edit',$users->id).'" ><i
                          ></i> Edit</a></a>&nbsp;&nbsp;<a href="'.route('users.destroy',$users->id).'" ><i ></i> Delete</a>';
      });
  }

  public function query(User $model)
  {
    return $model->newQuery()->select('id', DB::raw('CONCAT(FirstName," ", Surname ) AS name'), 'email', 'phone_no');
  }

  public function html()
  {
//  
    return $this->builder()
          ->columns($this->getColumns())
          ->minifiedAjax()
          ->addAction(['width' => '130px'])
          ->parameters($this->getBuilderParameters());
  }

  protected function getColumns()
  {
    return [
      'id',
      'name',
      'email',
      'phone_no',
    ];
  }
  protected function filename()
  {
    return 'Users_' . date('YmdHis');
  }
}

this is controller


namespace App\Http\Controllers\Backend;

use App\DataTables\UsersDatatable;
use App\User;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Yajra\DataTables\DataTables;
class RegisterController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(UsersDatatable $user)

    {
            return $user->render('backend.Users.indexUser');

    }

//this is view

    <div >
      Index User
    </div>
    <div >
      <h5 >Table</h5>
      {!! $dataTable->table(['class' => 'table table-striped ']) !!}
    </div>
  </div>
@endsection
@section('scripts')
  <script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
  <!-- from dataTables push-->
  <script src="https://cdn.datatables.net/buttons/1.4.2/js/dataTables.buttons.min.js"></script>
  <script src="{{asset('vendor/datatables/buttons.server-side.js')}}"></script>
  {!! $dataTable->scripts() !!}```
177

Answer

Solution:

Add yourfilterColumn just afteraddColumn oreditColumn (or somewhere else afterdatatables($query)):

datatables($query)
    ->addColumn('action', function ($obj) {
        return '';          
    })
    ->filterColumn('name', function ($query, $keyword) {
        $keywords = trim($keyword);
        $query->whereRaw("CONCAT(first_name, sur_name) like ?", ["%{$keywords}%"]);
     });

People are also looking for solutions to the problem: php - how to assign ajax variables for multiple buttons

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.