How to format server-side JSON from Editor (Node)?

dgruska

I previously had server-side DataTables working fine with a different backend (w/o SearchPanes). The data looked like this:

Now, with a Node backend and using the Editor API to work with SearchPanes, the data looks like this:

I was able to get around the bit issue ("active" field) by casting as signed, and the upper/lowercase is not an issue, but now the data is wrapped in "v_powerfeedthroughselectionguide", which is the name of the view I am pulling the data from.

On the client side, I tried using the following as a test in "dataSrc":

 , dataSrc: function (json) {
        return json.data[9].v_powerfeedthroughselectionguidedata;
  }

but DataTables shows "No matching records found", even though I am getting the test row back if I do a console.log.

I was able to rewrite my supporting client-side formatting JS to get the data back, but now SearchPanes doesn't show any records.

let fullRecordObjParsed = fullRecordObj.v_powerfeedthroughselectionguidedata;

In Node, I am using the following code:
app.use(express.urlencoded({ extended: true }));

and

await editor.process(req.body);
res.json(editor.data());

I also tried using the code below with the same end result:

const bodyParser = require('body-parser');
app.use(bodyParser.urlencoded({extended: true}));

Ideally I would like the formatting to look as it did, as we have a lot more of these SearchPanes projects to convert to server side, but I'd settle for SearchPanes just working, even if we do need to re-write more code.

Debug code: ijahil

Thanks in advance.

sandy

Hi @dgruska ,

I think I'll need to see a test case to debug this properly I'm afraid. Could you please send me a link to your page showing the issue?

It would also be helpful to see your controller please.

Thanks,
Sandy

dgruska

Hi Sandy,
It's going to be a bit of a production for me to post a test case, but I'll see what I can do.
Here's the whole controller:

const db = require('knex')({
  client: 'mysql2',
  connection: {
    host : '[server]',
    port : 3306,
    user : '[user]',
    password : '[password]',
    database : '[database]'
  }
});

let express = require('express');
let app = express();
let router = express.Router();

app.use(express.urlencoded({ extended: true }));

let e = require('datatables.net-editor-server');
let Editor = e.Editor;
let Field = e.Field;
let Validate = e.Validate;
let Format = e.Format;
let SearchPaneOptions = e.SearchPaneOptions;

router.all('(/*)', async function (req, res) {
    let editor = new Editor(db, 'v_powerfeedthroughselectionguidedata', 'v_powerfeedthroughselectionguidedata.id')
        .fields(
            new Field('v_powerfeedthroughselectionguidedata.PARTNO'),
            new Field('v_powerfeedthroughselectionguidedata.DISPLAYORDER'),
            new Field('v_powerfeedthroughselectionguidedata.DIMA_MM'),
            new Field('v_powerfeedthroughselectionguidedata.DIMB_MM'),
            new Field('v_powerfeedthroughselectionguidedata.DIMC_MM'),
            new Field('v_powerfeedthroughselectionguidedata.DIMD_MM'),
            new Field('v_powerfeedthroughselectionguidedata.DIME_MM'),
            new Field('v_powerfeedthroughselectionguidedata.DIMF_MM'),
            new Field('v_powerfeedthroughselectionguidedata.DIMG_MM'),
            new Field('v_powerfeedthroughselectionguidedata.PINSIZE_MM'),
            new Field('v_powerfeedthroughselectionguidedata.LENGTH_MM'),
            new Field('v_powerfeedthroughselectionguidedata.WELDLIPDIAMETER_MM'),
            new Field('v_powerfeedthroughselectionguidedata.WELDLIPTHICKNESS_MM'),
            new Field('v_powerfeedthroughselectionguidedata.CONDUCTORDIAMETER_MM'),
            new Field('v_powerfeedthroughselectionguidedata.WIRESIZE_MM'),
            new Field('v_powerfeedthroughselectionguidedata.OUTERDIAMETER_MM'),
            new Field('v_powerfeedthroughselectionguidedata.MATINGPINSIZE_MM'),
            new Field('v_powerfeedthroughselectionguidedata.CABLETHICKNESS_MM'),
            new Field('v_powerfeedthroughselectionguidedata.CABLEWIDTH_MM'),
            new Field('v_powerfeedthroughselectionguidedata.IMAGEURL'),
            new Field('v_powerfeedthroughselectionguidedata.ISDYNAMICSVG'),
            new Field('v_powerfeedthroughselectionguidedata.ICONURL'),
            new Field('v_powerfeedthroughselectionguidedata.REGIONLIST'),
            new Field('v_powerfeedthroughselectionguidedata.ACTIVE'),
            new Field('v_powerfeedthroughselectionguidedata.VOLTAGE'),
            new Field('v_powerfeedthroughselectionguidedata.AMPS'),
            new Field('v_powerfeedthroughselectionguidedata.AMPSPLUG'),
            new Field('v_powerfeedthroughselectionguidedata.POWER'),
            new Field('v_powerfeedthroughselectionguidedata.FREQUENCYKHZ'),
            new Field('v_powerfeedthroughselectionguidedata.FREQUENCYMHZ'),
            new Field('v_powerfeedthroughselectionguidedata.NUMOFCONDUCTOR'),
            new Field('v_powerfeedthroughselectionguidedata.CONDUCTORMATERIAL'),
            new Field('v_powerfeedthroughselectionguidedata.CONDUCTORDIAMETER'),
            new Field('v_powerfeedthroughselectionguidedata.STYLE'),
            new Field('v_powerfeedthroughselectionguidedata.CONDUCTORTHREAD'),
            new Field('v_powerfeedthroughselectionguidedata.AIRSIDE'),
            new Field('v_powerfeedthroughselectionguidedata.VACSIDE'),
            new Field('v_powerfeedthroughselectionguidedata.SHIELD'),
            new Field('v_powerfeedthroughselectionguidedata.FLANGEMOUNTING'),
            new Field('v_powerfeedthroughselectionguidedata.WELDLIPDIAMETER'),
            new Field('v_powerfeedthroughselectionguidedata.WELDLIPTHICKNESS'),
            new Field('v_powerfeedthroughselectionguidedata.WELDLIPMATERIAL'),
            new Field('v_powerfeedthroughselectionguidedata.INSULATORMATERIAL'),
            new Field('v_powerfeedthroughselectionguidedata.VOLTAGEPINTOPINKV'),
            new Field('v_powerfeedthroughselectionguidedata.FTCATEGORY'),
            new Field('v_powerfeedthroughselectionguidedata.FTSUBCATEGORY'),
            new Field('v_powerfeedthroughselectionguidedata.ONHAND'),
            new Field('v_powerfeedthroughselectionguidedata.ONHANDUK'),
            new Field('v_powerfeedthroughselectionguidedata.HASDXFFILE'),
            new Field('v_powerfeedthroughselectionguidedata.HASSTPFILE'),
            new Field('v_powerfeedthroughselectionguidedata.HASPDFFILE'),
            new Field('v_powerfeedthroughselectionguidedata.PAGESRELATIONTYPE'),
            new Field('v_powerfeedthroughselectionguidedata.HASSEOURLS'),
            new Field('v_powerfeedthroughselectionguidedata.PAGENAME'),
            new Field('v_powerfeedthroughselectionguidedata.HASPARTPAGES'),
            new Field('v_powerfeedthroughselectionguidedata.NOTE'),
            new Field('v_powerfeedthroughselectionguidedata.VOLTAGEKV')
                .searchPaneOptions(new SearchPaneOptions()),
            new Field('v_powerfeedthroughselectionguidedata.VOLTAGEKVPLUG')
                .searchPaneOptions(new SearchPaneOptions())
    ).write(false);

    await editor.process(req.body);
    res.json(editor.data());
});

app.use(router);
app.listen(process.env.PORT, function(err){
    if (err) console.log(err);
    console.log("Server listening on PORT", process.env.PORT);
});

allan

Hi,

Many thanks for the controller. First thin to say is that since you aren't using any joins, you don't actually need to specify v_powerfeedthroughselectionguidedata. as a prefix to all of the fields. That will stop the wrapping of your data for the rows by the v_powerfeedthroughselectionguidedata object as well.

If you did want to keep it though, then you don't need to use the ajax.dataSrc option (in fact, it would be wrong to do so in this case since the rows are in the data object which is the default DataTables is expecting.

Instead, each of your columns would need to reference the view name as well using columns.data - e.g.:

data: 'v_powerfeedthroughselectionguidedata.PARTNO'

rather than:

data: 'PARTNO'

as it currently is.

Sorting that out (either by removing the view name, or by correctly addressed the data points) which also sort out the SearchPanes issue. At the moment the server is returning data in the format:

    "searchPanes": {
        "options": {
            "v_powerfeedthroughselectionguidedata.VOLTAGEKV": [{
                "count": 106,
                "label": null,
                "total": 106,
                "value": null
            }, {
                "count": 113,
                "label": "1",
                "total": 113,
                "value": "1"
            }, {

But there is no column with that data name, hence it isn't showing up.

Hope this helps,
Allan

dgruska

Hi Allan,
This works! Thank you. I have immediately hit another big snag, though - I've asked a new question to keep things organized ( https://datatables.net/forums/discussion/71731/cant-select-another-item-within-the-same-filter-server-side-searchpanes-only/p1?new=1 ).