Filter/search/order for nested relations

[Luis-Goncalves-Searchprof]

Hi,

Back to the subject of issue #696 I'm wondering if the nested relations are already working. I'm using L5.3 and the latest version of the package and the search and ordering aren't working well.

Controller method:

public function indexAjax()
{
        $with = [
            'user',
            'country',
            'country.translations'   => function ($query) use ($localeId) {
                $query->where('locale_id', '=', $localeId);
            },
            'district',
            'district.translations' => function ($query) use ($localeId) {
                $query->where('locale_id', '=', $localeId);
            },
        ];

        $columns = [
            // tabela "candidates"
            'candidates.id',
            'candidates.first_name',
            'candidates.last_name',
            DB::raw('CONCAT(candidates.first_name, " ", candidates.last_name) AS name'),
            'candidates.country_id',
            'candidates.district_id',
            'candidates.created_at',
        ];

        $candidates = $this->candidateService->datatable($columns, $with);

        return Datatables::of($candidates)
            ->editColumn('name', function ($candidate) {
                logger($candidate);
                return str_limit($candidate->name, 30, '...');
            })
            ->editColumn('user.email', function ($candidate) {
                return $candidate->user ? str_limit($candidate->user->email, 30, '...') : null;
            })
            ->editColumn('country.translations.name', function ($candidate) {
                return $candidate->country ? str_limit($candidate->country->translations[0]->name, 30, '...') : null;
            })
            ->editColumn('district.translations.name', function ($candidate) {
                return $candidate->district ? str_limit($candidate->district->translations[0]->name, 30, '...') : null;
            })
            ->editColumn('created_at', function ($candidate) {
                return $candidate->created_at->format('Y-m-d');
            })
            ->addColumn('user.active', function ($candidate) {
                return $candidate->user ? $candidate->user->active : null;
            })
            ->addColumn('edit_url', function ($candidate) {
                return route('backend.administrator.candidates.edit', [$candidate->id]);
            })
            ->addColumn('delete_url', function ($candidate) {
                return route('backend.administrator.candidates.destroy', [$candidate->id]);
            })
            ->make(true);
}

JS code:
Inside the datatable construction i have the columns defined like this:

columns: [
                    {
                        data: 'select', name: 'select', orderable: false, searchable: false,
                        render: function (data, type, row) {
                            return '<input type="checkbox" name="selected[]" value="' + row.id + '" class="select-multiple">';
                        }
                    },
                    {data: 'id', name: 'id'},
                    {data: 'name', name: 'name'},
                    {data: 'user.email', name: 'user.email'},
                    {data: 'country.translations.name', name: 'country.translations.name'},
                    {data: 'district.translations.name', name: 'district.translations.name'},
                    etc
],

The database is populated well. The only fields where the search and order dont work well are the "country" and "district".
I noticed that in the documentation you mentioned them:
«Same strategy goes for nested relationships but do NOTE that ordering is not yet fully tested on nested relationships.»
So, is the nested relationships working well or? :)

Thanks

[mattpramschufer]

I took am having the same issue. I have the following code

protected function dataTables($request, $runs) {
            //Create DataTables object
            $runs = RunAvailability::query();
            $runs->with('foodprofile', 'foodprofileavailability', 'foodprofile.user');

            $dt = Datatables::of($runs);
            //Add Columns
            $dt->addColumn('organization', function ($row) {
                return $row->foodProfile->user->organization;
            });

            $dt->addColumn('date', function ($row) {
                return date('m/d/Y', strtotime($row->date));
            });

            $dt->addColumn('day', function ($row) {
                return date('l', strtotime($row->date));
            });

            $dt->addColumn('start_time', function ($row) {
                return date('g:ia', strtotime($row->foodProfileAvailability->start_time));
            });

            $dt->addColumn('end_time', function ($row) {
                return date('g:ia', strtotime($row->foodProfileAvailability->end_time));
            });

            $dt->addColumn('food_size_id', function ($row) {
                return $row->foodProfile->foodSizes()->first()->name;
            });

            $dt->addColumn('food_types', function ($row) {
                $foodTypes = $row->foodProfile->foodTypes()->pluck('name');
                return implode('<br>' , $foodTypes->toArray());
            });
            $dt->addColumn('city', function ($row) {
                return $row->foodProfile->user->city;
            });
            $dt->addColumn('state', function ($row) {
                return $row->foodProfile->user->state;
            });
            $dt->addColumn('zipcode', function ($row) {
                return $row->foodProfile->user->zipcode;
            });


            return $dt->make(true);
        }

With the following JS

$(function () {

            var $filterTag = $('.filter-tag');
            var dTable = $('#userList').DataTable({
                responsive: true,
                processing: true,
                serverSide: true,
                ajax: {
                    url: '{!! route('ajax-staff-runs-donor') !!}',
                    data: function (d) {

                        var tag = $filterTag.val();

                        if (tag) {
                            d.tag = tag;
                        }


                    }
                },
                pageLength: 25,
                order: [[1, "asc"]],
                columns: [
                    {data: "organization", name: "foodprofile.user.organization"},
                    {data: "date"},
                    {data: "day"},
                    {data: "start_time"},
                    {data: "end_time"},
                    {data: "food_size_id"},
                    {data: "food_types"},
                    {data: "city"},
                    {data: "state"},
                    {data: "zipcode"},

                    {
                        data: null,
                        render: function (data, type, row) {
                            return '<a href="https://app.foodrescue.us/runs/' + data.id + '/receiver" class="btn btn-success btn-sm">Select Donor</a>';
                        },
                        name: 'actions', orderable: false, searchable: false
                    }


                ]


            });

            $filterTag.on('change', function (e) {
                dTable.draw();
            });

        });

When I try to Order by or Search by Organization, I get Illuminate\Database\QueryException: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 56 column(s)

[mattpramschufer]

This is happening for me because we are adding a Global scope which adds 2 new columns. I.E.

$columns = \DB::connection()->getSchemaBuilder()->getColumnListing($table);

$builder->addSelect(DB::raw(implode(',', $columns) . ',x(' . $table . '.geolocation) as lat, y(' . $table . '.geolocation) as lng'));

If I remove the global scope it works fine, we would prefer NOT to have to remove the global scope though. Any suggestions?

[yajra]

@Luis-Goncalves-Searchprof nested relations is working but still have some issues. I am currently working on fixing ordering along with this PR #850. ATM, I must say that nested eager loading support is not yet stable. Thanks!

[Yarandi]

I have the same issue, I'm currently using datatabels 7.0 and Laravel 5.4
data in my grid is showing correctly but when I want to search on nested relationship column I got the error.

Here is my code

Controller

$videos = Video::with(['course_semester' => function ($query) {
            return $query->with('course', 'semester');
        }])->select('videos.*');
        return Datatables::of($videos)
                ->addColumn('check', '<input type="checkbox" name="selected-videos" value="{{$id}}">')
                ->escapeColumns([])
                ->make(true);

Javascript

columns: [
                 { data: 'check' , name: 'check',orderable: false, searchable: false },
                 { data: 'id', name: 'videos.id' },
                 { data: 'name', name: 'videos.name' },
                 { data: 'course_semester.semester.name', name: 'course_semester.semester.name'},
                 { data: 'course_semester.course.name', name: 'course_semester.course.name'},
                 { data: 'status', name: 'videos.status' },
                 { data: 'comment', name: 'videos.comment' },
                 { data: 'video_date', name: 'videos.video_date' },
            ]

Can anyone help me and notice my problem?
Thanks in advance.

[yajra]

@Yarandi see #1137 (comment) for a possible fix on v7. Thanks!

[ridaamirini]

@yajra Sorry i saw this is the active issue. So is this still WIP ?

[Jofeean]

I found a work around about the nested relationship problem which occurs when tables have a pivot table. I don't know if it's the best option but at the filterColumn there's a $query variable which you can use to use query builder methods. So it means that you can use the whereHas methods on a nested manner. let me show you

Note:
column_name = actual name of the column on your database table
$keyboard = text parameter passed by the search bar on your datatable
use $keyword = in order to use the $keyword variable on the inner most function on the block

->filterColumn("column_name", function($query, $keyboard){
$query->whereHas("first_table", function ($q) use ($keyword) {
$q->whereHas("second_table", function ($query) use ($keyword) {
$query->where("column_name_2", "like", "%" . $keyword . "%");
});
});
})