Search select input wont show records using foreign key database

mdaniel1

Link to example case: https://codepen.io/piyush_05/pen/JjGbwZY
Debugger code (debug.datatables.net):
Error messages shown: none
Description of problem: The problem occurs when using select input and server side data processing with a foreign key.
If you choose to select a column that gets its data from another database via foreign key (shows correct options) it will try to search but will say no records found. If you choose a column thats gets its data from the current database it will sort properly. How can I make the input select properly sort the select option?

columns: [{
  data: 'event_id', // works
  name: 'event_id'
  },
 {
   data: 'eventdata.date', // does not work
    name: 'eventdata.date'
},

allan

Hi,

I'm not entirely clear on how the given example demonstrates what you describe. Could you give me step-by-step instructions on how to see the error?

Thanks,
Allan

mdaniel1

See my sceeenshots for examples. On status, event date, city When I try to select and filter by that option it always says "no matching records found"
So it seems to me that this effect is only happening on data being pulled from its foreign key match. Hopefully this brings more clarity. thank you

here is my js

 $('#reportdatatable').DataTable({
        processing: true,
        serverSide: true,
        autoWidth: true,
        responsive: true,
        select: true,
        ajax: '{{ route("backend.$module_name.index_data") }}',
        columns: [{
                data: 'event_id',
                name: 'event_id'
            },
            {
                data: 'event_name',
                name: 'event_name'
            },
            {
                data: 'eventdata.open',
                name: 'status'
            },
            {
                data: 'eventdata.date',
                name: 'eventdate'
            },
            {
                data: 'eventdata.city',
                name: 'eventcity'
            },
            {
                data: 'firstname',
                name: 'firstname'
            },
            {
                data: 'lastname',
                name: 'lastname'
            },
            {
                data: 'organization',
                name: 'organization'
            },
            {
                data: 'action',
                name: 'action',
                orderable: false,
                searchable: false
            }
        ],
        initComplete: function () {
            this.api().columns([0,2,3,4]).every( function () {
                var column = this;
                var placeHolder = '';
                placeHolder = 'Select ' + $(this.header()).html()
                       if (column.index() < 8 ) {
                       var select = $('<select><option value="">'+placeHolder+'</option></select>')
                           .appendTo( $(column.header()).empty() )
                           .on( 'change', function () {
                               var val = $.fn.dataTable.util.escapeRegex(
                                   $(this).val()
                               );

                               column
                                   .search( val ? '^'+val+'$' : '', true, false )
                                   .draw();
                           } );

                       column.data().unique().sort().each( function ( d, j ) {
                           select.append( '<option value="'+d+'">'+d+'</option>' )
                       } );
                       } 
            } );
        }
    });

my html

<table id="reportdatatable" class="table table-bordered table-hover table-responsive">
                    <thead>
                        <tr>
                            <th>
                                Event #
                            </th>
                            <th>
                               Event Name
                            </th>
                            <th>
                                Status
                            </th>
                            <th>
                                Event Date
                            </th>
                            <th>
                                Event City
                            </th>
                            <th>
                               First Name
                            </th>
                            <th>
                               Last Name
                            </th>
                            <th>
                               Organization
                            </th>
                            <th class="text-end">
                            Action
                            </th>
                        </tr>
                    </thead>
                </table>

allan

serverSide: true,

What script are you using for the server-side processing? Whatever that is, is responsible for doing the search. In this case, it sounds like it would need to do a left join to match the value submitted to the text.

What might be better is to have both the foreign key id and the text value from the left join in the data. Then build your select with the text value as what the user sees, but the value is the id. Then that is submitted and filtered on in the primary table without needing the look up (and the issues that would arise if two rows had the same text value).

Allan