Back

Laravel Datatables won't search on relationship GROUP_CONCAT(CONCAT(first_name, " ", surname )) as guests


Adam Duffield posted 1 week ago

Hi all,

Currently working on a project using (yajra/laravel-datatables ) for the first time, great stuff! However it looks as though it can't handle what I'm currently after, I can't filter on first_name and surname, I've tried multiple online solutions with no success. See code below...

 public function getTodaysVisitsTableData($request) {


        $visits = DB::table('visits')
            ->join('company', 'company.id', '=', 'visits.company_id')
            ->join('visit_guest', 'visit_guest.id', '=', 'visits.id')
            ->select('visits.id',
                DB::raw( '(SELECT GROUP_CONCAT(CONCAT(first_name, " ", surname )) FROM visit_guest VG WHERE VG.visit_id = visits.id) as guests'),
                'company.company_name',
                'visitor_pass_number',
                'vehicle_reg',
                'visit_date',
                'leave_date_time'
            )
            ->whereDate('visits.visit_date', '=', date('Y-m-d'));


        $datatables = Datatables::of($visits);

        $datatables->addColumn('action', function () {
            return '';
        });

        $datatables->setRowData([
            'data-href' => '{{ url("/visit/" . $id) }}',
            'data-visit-id' => '{{$id}}',
        ]);

        if ($keyword = $request->get('search')['value']) {

            $datatables->filterColumn('guests', function($visits, $keyword) {
                $sql = "visit_guest.first_name like ?";
                $visits->whereRaw($sql, ["%{$keyword}%"]);
            });

        }

        return $datatables->make(true);
    }
 var dataTable = $('.today-visits-data-table').DataTable({
            processing: true,
            serverSide: true,
            language : {
                sLengthMenu: "Show _MENU_"
            },
            ajax: window.site.todaysVisitsTableData,
            columns: [
                { data: 'action', name: 'action', orderable: false, searchable: false, className: 'select-checkbox checkbox-td', targets: 0 },
                { data: 'id', name: 'visits.id' },
                { data: 'guests', name: 'guests' },
                { data: 'company_name', name: 'company.company_name'},
                { data: 'visitor_pass_number', name: 'visits.visitor_pass_number' },
                { data: 'vehicle_reg', name: 'visits.vehicle_reg' },
                { data: 'visit_date', name: 'visits.visit_date' },
                { data: 'leave_date_time', name: 'visits.leave_date_time' },
            ],
            "fnDrawCallback":function(){
                DataTableInit.handlePaginationState();
                DataTableInit.addDropdownArrows();
                DataTableInit.makeRowsClickable();
                DataTableInit.addDropdownArrowsSelectbox();
            },
            "fnInitComplete":function() {
                DataTableInit.addSearchCross();
                DataTableInit.makeRowsClickable();
            },
            'createdRow': function( row, data, dataIndex ) {
                $(row).attr('data-href', window.site.url + '/visit/' + data.id);
                $(row).attr('data-visit-id', data.id);
            },
            select: {
                style:    'multi',
                selector: 'td:first-child'
            }
        });

Sign in to participate in this thread!



We'd like to thank these amazing companies for supporting us