Laravel-datatables: Custom search for relationship columns with Eager Loading don't work.

[manelsanz]

Summary of problem or feature request

Hello everyone.
First thanks @yajra for doing this great work adapting DataTables plugin to Laravel.

I implemented DataTables 8 as a Service for my work. And there is just a only thing that I don't achieved yet:

I added some html textbox inputs to DOM in order to filter in dataTable when I press a button "Filter" (added by me too). I override the global search function and I can filter with the added request parameters without problem.
But, when I want to filter with the fields of the other table in the relationship, I get always the same error: "Uknown column.... FROM first_table (NO second_table?) WHERE...".

There is my code snippet:

Code snippet of problem

class SessionsDataTable extends DataTable
{
    /**
    * Build DataTable class.
    *
    * @param mixed $query Results from query() method.
    * @return \Yajra\DataTables\DataTableAbstract
    */
    public function dataTable($query)
    {
        $request = request();

        return datatables($query)
        ->filter(function ($query) use ($request) {

            if ($request->filled('employee_name')) {
                $query->where('employee.name', '=', $request->get('employee_name'));
            }

The above query is stopping the execution of the app, because "employee.name" is not found in table 'sessions' that is logical, but at the function "query" I take both tables ('sessions', 'employee') as we can see below.

            if ($request->filled('employee_id')) {
                $query->where('user', '=', $request->get('employee_id'));
            }

            if ($request->filled('begin')) {
                $query->where('begin', '>=', $request->get('begin'));
            }

            if ($request->filled('end')) {
                $query->where('end', '<=', $request->get('end'));
            }
        });
    }

    /**
    * Get query source of dataTable.
    *
    * @param \App\Session $model
    * @return \Illuminate\Database\Eloquent\Builder
    */
    public function query(Session $model)
    {
        return $model->with('employee:id,name')
        ->where('machine_id', '=', $this->machine_id)
        ->limit(1)
        ->select('sessions.*');

Above I use Eager Loading (Session::with('employee')) that is working when the page loads, but when I filter seems that is not catching the 'employee' table data.

    }

    /**
    * Optional method if you want to use html builder.
    *
    * @return \Yajra\DataTables\Html\Builder
    */
    public function html()
    {
        return $this->builder()
        ->columns($this->getColumns())
        ->minifiedAjax('','data.employee_name = $("#employee_name input").val(); data.employee_id = $("#employee_id input").val(); data.begin = startDate; data.end = endDate; ')
        //->minifiedAjax('','function(d) { d.name = $("#manu").val(); }')
        ->parameters([
            'initComplete' => 'function () { var filters =
            ...
            </form>`; $("#search-form").html(filters); }',

            'dom'     => "<'row mb-2'<'#search-form.col-sm-8'><'col-sm-4 text-right'B>>" .
            "<'row'<'col-sm-12'tr>>" .
            "<'row'<'col-sm-5'i><'col-sm-7'p>>",
            'order'   => [[0, 'asc']],
            'language' => [
                'processing' => '<div class="fa-3x"><i class="fas fa-circle-notch fa-spin"></i></i></div>',
            ],
            'responsive' => true,
            // "iDisplayLength" => 10,
            'buttons' => [
                [
                    'extend' => 'print',
                    'className' => 'btn btn-light',
                    'text' => '<i class="fas fa-print"></i>',
                    'exportOptions' => [
                        'columns' => ':visible'
                    ],
                ],
                [
                    'extend' => 'copy',
                    'text' => '<i class="fas fa-copy"></i>',
                    'className' => 'btn btn-light'
                ],
                [
                    'extend' => 'reload',
                    'text' => '<i class="fas fa-sync-alt"></i>',
                    'title' => 'Reload',
                    'className' => 'btn btn-light'
                ],
                [
                    'extend' => 'export',
                    //'text' => '<i class="fas fa-sync-alt"></i>',
                    //'title' => 'Reload',
                    'alt' => 'Exportar ya',
                    'className' => 'btn btn-light'
                ],
            ],
        ]);
    }

    /**
    * Get columns.
    *
    * @return array
    */
    protected function getColumns()
    {
        return [
            'id',
            'user',
            ['title' => "Empleado", 'data' => 'employee.name', 'name' =>'employee.name'],
            'type',
            'begin',
            'end',
            'total_bill_iso_1',
            'total_bill_iso_2',
            'total_coin',
            'number_envelops',
            'quantity_envelops'
        ];
    }

}

System details

• Operating System: Windows 10
• PHP Version: 7.1.10
• Laravel Version: 5.5.33
• Laravel-Datatables Version: 8

[manelsanz]

I finally achieved that! Please @yajra, I think that filters in queries with Eloquent relationships (with two or more tables) should be explained better and with some more example.

This is the solution to all my problems.

Filter:

            if ($name = $request->get('employee_name')) {

                $query->whereHas('employee', function ($q) use ($name) {
                    $q->where('name', 'LIKE', "{$name}%");
                });

            }

Query:

        return $model->select('sessions.*')
        ->with('employee:id,name');

[ivansammartino]

Thanks a lot, @manelsanz , this saved my day too! :-)

[AkhilRaj003]

Thanks a lot @manelsanz . You solved most of my issues in this.

[danitrimujianto]

thanks bro @manelsanz you save my problems..