Issue with Search and editor

Jensen010

I'm still having an issue with getting search to work with server side processing.

I posted about this once before, but to quickly recap:
I have a setup where I need to change the numerical, "Owner", value in my Assets table, to match the ID value in my Contacts table. Then on the client side, present a dropdown of names that correspond to those numbers. To do this I've set up a join, and used the Field->options() to generate dropdown options. THAT part works fine.

What doesn't work now is search. When I attempt to search through the assets table. I get the message: DataTables warning: table id=assets-table - Unknown field: (index 0)

I've posted my code below, what do I need to change?

Here's what I have:
PHP Library Code:
```php
<?php

//Globals
global $_POST;

//Local Vars
include("....\lib\DataTables\Editor-PHP-1.7.2\php\DataTables.php");
include("....\lib\DataTables\Editor-PHP-1.7.2\php\Editor\Editor.php");

//Load Editor Modules
use
DataTables\Editor,
//...Others Below...

Editor::inst( $db, 'Assets')
->fields(
Field::inst( 'Contacts.Alias', 'Alias' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Contacts.Last_Name', 'Last_Name' )->setFormatter( Format::ifEmpty( null ) ),
Field::inst( 'Assets.Owner', 'Owner' )->setFormatter( Format::ifEmpty( null ) )
->options( Options::inst()
->table( 'Contacts' )
->value( 'Contacts.ID')
->label( array( 'Contacts.Alias', 'Contacts.Last_Name' ))
->order( 'Contacts.Alias asc')
),
)
->leftJoin( 'Contacts', 'Contacts.ID', '=', 'Assets.Owner' )
->process( $_POST )
->json();

<?php > ``` **Editor Object Configuration:** ```js //Load Editor Overlay - Assets var assetsEditor = new $.fn.dataTable.Editor({ ajax: { url: srvRoot+"/wp-content/plugins/datatables/php/table-data/assets-table.php", type: "POST" }, table: '#assets-table', fields: [ { label: "Owner", name: "Owner", className: "col-sm-5 float-left", type: "select" } ] }); ``` **Datatables Object Configuration:** ```js //Load Client Side Table - Assets var assetsDataTable = $('#assets-table').DataTable({ serverSide: true, deferRender: true, ajax: { url: srvRoot+"/wp-content/plugins/datatables/php/table-data/assets-table.php", type: "POST" }, scrollX: "1720px", scrollY: "550px", columnDefs: [ Owner Column*/{ "width": "125px", "targets": 1 } ], columns: [ //Owner Column { data: null, editField: "Owner", render: function(data, type, row, meta) { if(data.Alias === null && data.Last_Name === null){ return 'No owner assigned'; } else if(data.Alias === null && data.Last_Name === ''){ return 'Not set'; } else if (data.Alias === null) { return '(No first name specified) '+data.Last_Name; } else {return data.Alias+' '+data.Last_Name;} } ] }); ``` ?>

allan

You've got data: null in the first column, so the server-side processing doesn't "know" what data point it should be using on the server-side to search on. Since it is server-side processing it can't search on the client-side render result.

One option would be to have it set to Owner and then use row. rather than data. to get all of the data points you need. Although that would only allow search on the Owner field.

Allan

Jensen010

Ok, that makes sense. Although that still leaves me searching numerical data while the client sees a name string on the owners column.

I guess I could fake it, by making owners not searchable, and including two hidden columns for alias and last name. But then I would have to make sure those two columns were being paired up with the right asset

Jensen010

Ok, I solved this. Since I was already pulling Alias and Last name from the Contacts table, I just put them in as hidden, searchable columns.

Then on the Owners column, I changed the data attribute to "Owner", and used the render function to pull it's data from the row data object (thanks Allan!) instead of data.

Finally, I set the searchable flag on the Owner columnDefs section to false

This gives me a table that retrieves a numerical owner ID from Assets, joins that to the ID column on the Contacts table, and displays the concatenated Alias+' '+Last_Name string to the client side when they load the table.

This gives editor the data it needs to search the table, and when the client goes to change the owner on a particular asset, Editor knows to only change the Numerical ID associated with that record.

Phew!