Parent / child editing in child rows for NodeJS

tablo

Hi all,

Could someone please help me to convert PHP into JavaScript (NodeJS)?
It is the server side script from this blog:

Parent / child editing in child rows
https://datatables.net/blog/2019-01-11#Server-side-(PHP)

PHP:

//Parent table
Editor::inst( $db, 'sites' )
    ->fields(
        Field::inst( 'id' )->set( false ),
        Field::inst( 'name' )->validator( 'Validate::notEmpty' )
    )
    ->join(
        Mjoin::inst( 'users' )
            ->link( 'sites.id', 'users.site' )
            ->fields(
                Field::inst( 'id' )
            )
    )
    ->process( $_POST )
    ->json();


//Child table
if ( ! isset($_POST['site']) || ! is_numeric($_POST['site']) ) {
  echo json_encode( [ "data" => [] ] );
}
else {
  Editor::inst( $db, 'users' )
      ->field(
          Field::inst( 'users.first_name' ),
          Field::inst( 'users.last_name' ),
          Field::inst( 'users.phone' ),
          Field::inst( 'users.site' )
              ->options( 'sites', 'id', 'name' )
              ->validator( 'Validate::dbValues' ),
          Field::inst( 'sites.name' )
      )
      ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
      ->where( 'site', $_POST['site'] )
      ->process($_POST)
      ->json();
}

This is what I have done so far:

"use strict";

let db = require("./db");
let router = require("express").Router();

const {
  Editor,
  Field,
  Validate,
  Format,
  Options,
  Mjoin
} = require("datatables.net-editor-server");

router.all("/api/sites", async function(req, res) {
  // Parent table
  const editor = new Editor(db, "sites")
    .debug(true)
    .fields(
      new Field("id").set(false),
      new Field("name").validator(Validate.notEmpty())
    )
    .join(
      new Mjoin("users")
        .link('sites.id', 'users.site')
        .fields(
          new Field("id")
        )
    );

// Child table
if (condition) {

} else {

  const editor = new Editor(db, "users")
    .debug(true)
    .fields(
      new Field("users.first_name"),
      new Field("users.last_name"),
      new Field("users.phone"),
      new Field("users.site")
      .options('sites', 'id', 'name')
      .validator(Validate.dbValues()),
      new Field("sites.name")
    )
    .leftJoin( 'sites', 'sites.id', '=', 'users.site' )
    .where('site', $_POST['site']);
    }
    await editor.process(req.body);
    res.json(editor.data());
});

I have no idea how to convert the if condition...

daduffy

I am pretty sure that if the same post variable that was passed to your php script is getting passed to your node script that you can just look on the req.body object.

Did you setup your express parser middleware?

Dave

tablo

@daduffy: Thanks for the response!

I am pretty sure that if the same post variable that was passed to your php script is getting passed to your node script that you can just look on the req.body object.

I can't...I tried to remove the if condition and run it but I got this:
Something broke!

Did you setup your express parser middleware?

I didn't touch this stuff. I'm just using an example and replacing things that are different.

allan

.where('site', $_POST['site']);

Needs to be:

.where('site', req.body.site)

at the very least .

Which example are you using? Are you using the Editor NodeJS download package examples?

Allan

tablo

@allan: Thanks for your response!

that was easy...

How about the if condition and the rest?

Which example are you using? Are you using the Editor NodeJS download package examples?

No. It's something simple I created with the Generator for Editor.
Should I use something else as skeleton?

allan

No the Generator download is a good skeleton.

How about the if condition and the rest?

Can you show me what you've got and also a debug log trace of the error.

Allan

tablo

Can you show me what you've got and also a debug log trace of the error.

Meanwhile I tried again with Editor-NodeJS-1.9.0 and created a new controller.
With this script:

"use strict";

let db = require("../db");
let router = require("express").Router();

const {
  Editor,
  Field,
  Validate,
  Format,
  Options,
  Mjoin
} = require("datatables.net-editor-server");

router.all("/api/permissions", async function(req, res) {
  let editor = new Editor(db, "permission")
  .fields(new Field("permission.id"), new Field("permission.name"))
  .join(
    new Mjoin("users")
      .link("permission.id", "user_permission.permission_id")
      .link("users.id", "user_permission.user_id")
      .fields(
        new Field("id").validator(Validate.required()),
        new Field("first_name")
      )
  );

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

module.exports = router;

I get this result at http://localhost:8081/api/permissions:

{
    "data": [{
            "DT_RowId": "row_1",
            "permission": {
                "id": 1,
                "name": "Printer"
            },
            "users": [{
                    "id": 1,
                    "first_name": "Quynn"
                },
                {
                    "id": 2,
                    "first_name": "Kaitlin"
                },
         ...
            ]
        },
        {
            "DT_RowId": "row_2",
            "permission": {
                "id": 2,
                "name": "Servers"
            },
            "users": [{
                    "id": 5,
                    "first_name": "Kamal"
                },
                {
                    "id": 7,
                    "first_name": "Xantha"
                },
        ...
            ]
        },
        {
            "DT_RowId": "row_3",
            "permission": {
                "id": 3,
                "name": "Desktop"
            },
            "users": [{
                    "id": 1,
                    "first_name": "Quynn"
                },
                {
                    "id": 4,
                    "first_name": "Sophia"
                },
       ...
            ]
        },
        {
            "DT_RowId": "row_4",
            "permission": {
                "id": 4,
                "name": "VMs"
            },
            "users": [{
                    "id": 1,
                    "first_name": "Quynn"
                },
                {
                    "id": 2,
                    "first_name": "Kaitlin"
                },
        ...
            ]
        },
        {
            "DT_RowId": "row_5",
            "permission": {
                "id": 5,
                "name": "Web-site"
            },
            "users": [{
                    "id": 4,
                    "first_name": "Sophia"
                },
                {
                    "id": 12,
                    "first_name": "Martha"
                },
                {
                    "id": 30,
                    "first_name": "Dexter"
                }
            ]
        },
        {
            "DT_RowId": "row_6",
            "permission": {
                "id": 6,
                "name": "Accounts"
            },
            "users": [{
                    "id": 4,
                    "first_name": "Sophia"
                },
                {
                    "id": 6,
                    "first_name": "Dustin"
                },
             ...
            ]
        }
    ],
    "fieldErrors": [],
    "files": {},
    "options": {}
}

When I add the child table like this:

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


  // Child Table
  let editor = new Editor(db, "users")
  .debug(true)
    .fields(
        new Field("users.id"),
        new Field("users.first_name")
        )

    //.leftJoin( 'sites', 'sites.id', '=', 'users.site' )
    .where('permission', req.body.permission);


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

});

module.exports = router;

I get again "Something broke!".

So the API does not work. The main issue is the server side script. I don't understand how it should be. I think if I have it, I can go further.

Regarding the debugging.
I have ".debug(true)" but I don't see any debug info in the terminal.

How do I get "debug log trace of the error"? Do you mean this from the DataTables debugger?

allan

I have ".debug(true)" but I don't see any debug info in the terminal.

It adds the SQL that is generated by the libraries into the JSON response.

If you are getting "Something broke" then it probably isn't getting as far as that...

Have you added the permission property to the Ajax request? Can you show me your (browser) Javascript code please?

Allan

tablo

@allan: I tried again with the database and tables from Editor-Node.js-1.9.0. This is the current state:

Browser script (I just changed the API endpoints):

//////////////////////////////////////////////////////////////////////
//  CHILD TABLE
//////////////////////////////////////////////////////////////////////
// Creating a DataTable
//////////////////////////////////////////////////////////////////////
function createChild(row) {
    var rowData = row.data();

    // This is the table we'll convert into a DataTable
    var table = $('<table class="display" width="100%"/>');

    // Display it the child row
    row.child(table).show();

    //////////////////////////////////////////////////////////////////////
    // Editor configuration
    //////////////////////////////////////////////////////////////////////
    // Editor definition for the child table
    var usersEditor = new $.fn.dataTable.Editor({
        ajax: {
            url: "/api/_users",
            data: function(d) {
                d.site = rowData.id;
            }
        },
        table: table,
        fields: [
            {
                label: "First name:",
                name: "users.first_name"
            },
            {
                label: "Last name:",
                name: "users.last_name"
            },
            {
                label: "Phone #:",
                name: "users.phone"
            },
            {
                label: "Site:",
                name: "users.site",
                type: "select",
                placeholder: "Select a location",
                def: rowData.id
            }
        ]
    });
    //////////////////////////////////////////////////////////////////////

    //////////////////////////////////////////////////////////////////////
    // DataTable configuration
    //////////////////////////////////////////////////////////////////////
    // Child row DataTable configuration, always passes the parent row's id to server
    var usersTable = table.DataTable({
        dom: "Bfrtip",
        pageLength: 5,
        ajax: {
            url: "/api/_users",
            type: "post",
            data: function(d) {
                d.site = rowData.id;
            }
        },
        columns: [
            { title: "First name", data: "users.first_name" },
            { title: "Last name", data: "users.last_name" },
            { title: "Phone #", data: "users.phone" },
            { title: "Location", data: "sites.name" }
        ],
        select: true,
        buttons: [
            { extend: "create", editor: usersEditor },
            { extend: "edit", editor: usersEditor },
            { extend: "remove", editor: usersEditor }
        ]
    });
    //////////////////////////////////////////////////////////////////////

    // On change, update the content of the parent table's host row
    // This isn't particularly efficient as it requires the child row
    // to be regenerated once the main table has been reloaded. A
    // better method would be to query the data for the new user counts
    // and update each existing row, but that is beyond the scope of
    // this post.

    //////////////////////////////////////////////////////////////////////
    // Updating the parent table
    //////////////////////////////////////////////////////////////////////
    usersEditor.on( 'submitSuccess', function (e, json, data, action) {
        row.ajax.reload(function () {
            $(row.cell( row.id(true), 0 ).node()).click();
        });
    } );
}

function updateChild(row) {
    $("table", row.child())
        .DataTable()
        .ajax.reload();
}

//////////////////////////////////////////////////////////////////////
// Destroying a DataTable
//////////////////////////////////////////////////////////////////////
function destroyChild(row) {
    // Remove and destroy the DataTable in the child row
    var table = $("table", row.child());
    table.detach();
    table.DataTable().destroy();

    // And then hide the row
    row.child.hide();
}
//////////////////////////////////////////////////////////////////////


$(document).ready(function() {
    //////////////////////////////////////////////////////////////////////
    // Parent table: Editor Javascript
    //////////////////////////////////////////////////////////////////////
    var siteEditor = new $.fn.dataTable.Editor({
        ajax: "/api/_sites",
        table: "#sites",
        fields: [
            {
                label: "Site name:",
                name: "name"
            }
        ]
    });

    //////////////////////////////////////////////////////////////////////
    // Parent table: DataTables Javascript
    //////////////////////////////////////////////////////////////////////
    var siteTable = $("#sites").DataTable({
        dom: "Bfrtip",
        ajax: "/api/_sites",
        order: [1, "asc"],
        columns: [
            {
                className: "details-control",
                orderable: false,
                data: null,
                defaultContent: "",
                width: "10%"
            },
            { data: "name" },
            {
                data: "users",
                render: function(data) {
                    return data.length;
                }
            }
        ],
        select: {
            style: "os",
            selector: "td:not(:first-child)"
        },
        buttons: [
            { extend: "create", editor: siteEditor },
            { extend: "edit", editor: siteEditor },
            { extend: "remove", editor: siteEditor }
        ]
    });
    //////////////////////////////////////////////////////////////////////


    //////////////////////////////////////////////////////////////////////
    // Child table
    //////////////////////////////////////////////////////////////////////
    // Add event listener for opening and closing details
    $("#sites tbody").on("click", "td.details-control", function() {
        var tr = $(this).closest("tr");
        var row = siteTable.row(tr);

        if (row.child.isShown()) {
            // This row is already open - close it
            destroyChild(row);
            tr.removeClass("shown");
        } else {
            // Open this row
            createChild(row);
            tr.addClass("shown");
        }
    });
    //////////////////////////////////////////////////////////////////////


    // When updating a site label, we want to update the child table's site labels as well
    siteEditor.on("submitSuccess", function() {
        siteTable.rows().every(function() {
            if (this.child.isShown()) {
                updateChild(this);
            }
        });
    });
});

tablo

Controller _sites.js:

"use strict";

let db = require("../db");
let router = require("express").Router();

const {
  Editor,
  Field,
  Validate,
  Format,
  Options,
  Mjoin
} = require("datatables.net-editor-server");

router.all("/api/_sites", async function(req, res) {

  // Parent Table
  let editor = new Editor(db, "sites")
    .fields(
      new Field("sites.id"),
      new Field("sites.name").validator(Validate.required())
      )
    .join(
      new Mjoin("users")
        .link("sites.id", "users.site")
        //.order("name asc")
        .fields(
          new Field("id")
        )
    );

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

});

module.exports = router;

Controller _users.js:

"use strict";

let db = require("../db");
let router = require("express").Router();

const {
  Editor,
  Field,
  Validate,
  Format,
  Options,
  Mjoin
} = require("datatables.net-editor-server");

router.all("/api/_users", async function(req, res) {

  if (req.body.site !== null || isNaN(req.body.site)) {
  } else {
    console.log('Child Table');

    // Child Table
    let editor = new Editor(db, "users")
      //.debug(true)
      .fields(
        new Field("users.first_name"),
        new Field("users.last_name"),
        new Field("users.phone"),
        new Field("users.site").options(
          new Options()
            .table("sites")
            .value("id")
            .label("name")
        )
      )

      .leftJoin("sites", "sites.id", "=", "users.site")
      .where("site", req.body.site);

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

  }
});

module.exports = router;

And this is the new error I'm getting, when I try to access the APIs on:
http://localhost:8081/api/_users
or
http://localhost:8081/api/_sites

$ npm run start

> datatables.net-editor-demo@1.9.0 start /home/user/DataTables-Editor/Editor-NodeJS-1.9.0
> node index.js

DataTables Editor demo - navigate to http://localhost:8081/examples
{ method: 'raw',
  sql:
   'SELECT table_name FROM information_schema.tables WHERE table_schema = current_schema() AND table_catalog = ?',
  bindings: [ 'datatables2' ],
  options: {},
  __knexQueryUid: 'f838ee81-efb8-4382-bd11-0ea61ecb296c' }

{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [],
  __knexQueryUid: '0f8480ec-2f67-430e-9d02-3c0af8015636',
  sql:
   'select "id" as "id", "sites"."id" as "sites.id", "sites"."name" as "sites.name" from "sites"' }
{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ '1', '2', '3', '4', '5', '6' ],
  __knexQueryUid: '09243bcf-5115-48e0-9804-cf5897b811c5',
  sql:
   'select distinct "sites"."id" as "dteditor_pkey", "users"."id" as "id" from "sites" inner join "users" on "id" = "site" where "sites"."id" in (?, ?, ?, ?, ?, ?)' }
Unhandled promise error:  [object Promise]error: select distinct "sites"."id" as "dteditor_pkey", "users"."id" as "id" from "sites" inner join "users" on "id" = "site" where "sites"."id" in ($1, $2, $3, $4, $5, $6) - column reference "id" is ambiguous
stack: error: column reference "id" is ambiguous
    at Connection.parseE (/home/user/DataTables-Editor/Editor-NodeJS-1.9.0/node_modules/pg/lib/connection.js:554:11)
    at Connection.parseMessage (/home/user/DataTables-Editor/Editor-NodeJS-1.9.0/node_modules/pg/lib/connection.js:379:19)
    at Socket.<anonymous> (/home/user/DataTables-Editor/Editor-NodeJS-1.9.0/node_modules/pg/lib/connection.js:119:22)
    at Socket.emit (events.js:189:13)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)

I tried to fix the new error without success...
I don't understand why the browser script has higher priority than the APIs. If the APIs are giving me an error, how should the browser script work?

Also, I had before mistakenly debug: true, inside the connection, which I changed.

allan

I fear you've discovered a bug in the NodeJS libraries for Editor here! If using a single link for Mjoin, and you have two tables with an id column each it throws the error you are seeing. Its caused by this line - it needs to reference the tables as well.

I'm just looking into that and will post back when committed.

Regards,
Allan

allan

Here is the fix. If you grab the latest files from that git repo, that should fix that specific issue.

Allan

tablo

@allan: Thanks for the quick fix!
I have replaced the old files with the new files but now I'm getting a new error:

$ npm run start

> datatables.net-editor-demo@1.9.0 start /home/user/DataTables-Editor/Editor-NodeJS-1.9.0
> node index.js

DataTables Editor demo - navigate to http://localhost:8081/examples
{ method: 'raw',
  sql:
   'SELECT table_name FROM information_schema.tables WHERE table_schema = current_schema() AND table_catalog = ?',
  bindings: [ 'datatables2' ],
  options: {},
  __knexQueryUid: 'd72e1507-57f4-4d6a-9866-9cc53c08f3fa' }
###############################################
1: req.body:  {}
###############################################
{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [],
  __knexQueryUid: '9fa569dd-6534-4b91-ba3c-f651b56c8b56',
  sql:
   'select "id" as "id", "sites"."id" as "sites.id", "sites"."name" as "sites.name" from "sites"' }
{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ '1', '2', '3', '4', '5', '6' ],
  __knexQueryUid: '86cd1152-02b9-466a-bad6-925ab7b4ef66',
  sql:
   'select distinct "sites"."id" as "dteditor_pkey", "users"."id" as "id" from "sites" inner join "users" as "users" on "users"."id" = "sites"."site" where "sites"."id" in (?, ?, ?, ?, ?, ?)' }
Unhandled promise error:  [object Promise]error: select distinct "sites"."id" as "dteditor_pkey", "users"."id" as "id" from "sites" inner join "users" as "users" on "users"."id" = "sites"."site" where "sites"."id" in ($1, $2, $3, $4, $5, $6) - column sites.site does not exist
stack: error: column sites.site does not exist
    at Connection.parseE (/home/user/DataTables-Editor/Editor-NodeJS-1.9.0/node_modules/pg/lib/connection.js:554:11)
    at Connection.parseMessage (/home/user/DataTables-Editor/Editor-NodeJS-1.9.0/node_modules/pg/lib/connection.js:379:19)
    at Socket.<anonymous> (/home/user/DataTables-Editor/Editor-NodeJS-1.9.0/node_modules/pg/lib/connection.js:119:22)
    at Socket.emit (events.js:189:13)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)

Again, the API endpoints do not work.

allan

What a muppet - sorry! Proper fix is here.

Allan

tablo

@allan: Thanks for the new fix!
Sorry to say that but I have replaced the old files with the new files and now I'm getting a new error:

2 alert warnings:
DataTables warning: table id=sites - Requested unknown parameter 'name' for row 0, column 1. For more information about this error, please see http://datatables.net/tn/4

In the browser console:

editor-demo.js:39 Uncaught ReferenceError: editor is not defined
    at HTMLDocument.<anonymous> (editor-demo.js:39)
    at HTMLDocument.dispatch (jquery-3.3.1.js:5183)
    at HTMLDocument.elemData.handle (jquery-3.3.1.js:4991)
    at Object.trigger (jquery-3.3.1.js:8249)
    at HTMLTableElement.<anonymous> (jquery-3.3.1.js:8327)
    at Function.each (jquery-3.3.1.js:354)
    at jQuery.fn.init.each (jquery-3.3.1.js:189)
    at jQuery.fn.init.trigger (jquery-3.3.1.js:8326)
    at r (jquery.dataTables.min.js:76)
    at ua (jquery.dataTables.min.js:49)

this line shows an error in editor-demo.js:39:

    editor.on('preSubmit', function (e, data) {

and this the output from the server terminal:

$ npm run start

> datatables.net-editor-demo@1.9.0 start /home/user/DataTables-Editor/Editor-NodeJS-1.9.0
> node index.js

DataTables Editor demo - navigate to http://localhost:8081/examples
{ method: 'raw',
  sql:
   'SELECT table_name FROM information_schema.tables WHERE table_schema = current_schema() AND table_catalog = ?',
  bindings: [ 'datatables2' ],
  options: {},
  __knexQueryUid: 'abf8d77d-da24-465b-9ef1-4a0fbaa8acdb' }

{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [],
  __knexQueryUid: 'aafa2aef-e1af-4b7d-8764-6b8629e7f524',
  sql:
   'select "id" as "id", "sites"."id" as "sites.id", "sites"."name" as "sites.name" from "sites"' }
{ method: 'select',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ '1', '2', '3', '4', '5', '6' ],
  __knexQueryUid: '75fb5ea8-3fca-42e3-bb9b-08b12505987e',
  sql:
   'select distinct "sites"."id" as "dteditor_pkey", "users"."id" as "id" from "sites" inner join "users" as "users" on "users"."site" = "sites"."id" where "sites"."id" in (?, ?, ?, ?, ?, ?)' }

After the 2 alerts the column with the numbers of users appears but not the column with the site names or the column with the "+" sign.
When I check the "DataTables debug" I see "15 tests complete. No failures or warnings found!"

allan

I think that's progress. Can you use the upload feature in the debugger to send me some details please. Using the upload configuration option and then send me the 6 (or 7 - can't remember off the top of my head!) code.

Allan

tablo

@allan: I just uploaded the config data. Not sure if it was successfully uploaded.

then send me the 6 (or 7 - can't remember off the top of my head!) code.

I'm not sure what do you mean by that. Do you mean the table row?

<tr id="row_1" role="row" class="odd selected"><td class=" details-control"></td><td class="sorting_1"></td><td>6</td></tr>

colin

Ah, Allan meant to use the debugger,

C

tablo

@colin : Thanks!
So, is it OK now? I had the DataTables Debugger installed as boomarklet and pressed the button to upload the configuration. If I remember correctly I saw a confirmation message but nothing else. What is the 6 or 7 code?

colin

You should see a code, which would identify your upload, see screenshot below:

tablo

@colin : Thanks!
I've somehow missed that
I've uploaded the config data again and sent the code to @allan.

allan

Perfect - thanks.

You have:

"data": "name",

For the second column, but the data for the rows is structured in the JSON as:

{
    "DT_RowId": "row_1",
    "sites": {
        "id": 1,
        "name": "Edinburgh"
    },
    "users": [{
        "id": 1
    }, {
        "id": 7
    }, {
        "id": 13
    }, {
        "id": 19
    }, {
        "id": 25
    }, {
        "id": 31
    }]
}

So DataTables is correct in saying that there is no name property in the object. Its sites.name that you want.

Allan

tablo

Thanks!
I also had to change it accordingly in the controller:

{ data: "sites.name" },

Now I can see the data in all 3 columns of the parent table and edit the site names. So, the API of the the parent table is now reachable and seems to be working fine. However, the API of the child table still does not work!

When I press the + sign to see the data of the child table I see only "Loading".
Apart from that when I press "new" in the parent table to enter a new site the form appears but the dropdown menu does not work and also no data is being written.
The error "Uncaught ReferenceError: editor is not defined" is not gone.

allan

I'd need a link to your page to be able to diganose that error. Likely there is a Javascript error happening in the function used to get the Ajax data. It might be the getting of the id from the host row, or something else.

Allan

tablo

I'd need a link to your page to be able to diganose that error.

I'm testing it locally. What page?
Is it not possible to test it if you have the files? I mean there are only 4 files involved:
* 1 html file which with 3 tags.
* 1 browser script which is exactly the same with the script in the PHP example.
* and 2 controller files of which the code I've already post them before.
I could send you them again files if you want.

Like I wrote many times before I'm not familiar with PHP at all. I tried to convert the controller files into JS and I'm still not sure if they are correct (OK, now the file for the parent table seems to be OK).
Can you you please check the code in the controller and verify that is correct?

I have removed the "if block" and when I try to reach the API I get this error:

Unhandled promise error:  [object Promise]Error: Undefined binding(s) detected when compiling SELECT query: select "users"."id" as "users.id", "users"."first_name" as "users.first_name", "users"."last_name" as "users.last_name", "users"."phone" as "users.phone", "users"."site" as "users.site", "sites"."name" as "sites.name" from "users" left join "sites" on "sites"."id" = "users"."site" where "site" = ?
stack: Error: Undefined binding(s) detected when compiling SELECT query: select "users"."id" as "users.id", "users"."first_name" as "users.first_name", "users"."last_name" as "users.last_name", "users"."phone" as "users.phone", "users"."site" as "users.site", "sites"."name" as "sites.name" from "users" left join "sites" on "sites"."id" = "users"."site" where "site" = ?
    at QueryCompiler_PG.toSQL (/home/user/Editor-NodeJS-1.9.0/node_modules/knex/lib/query/compiler.js:151:13)
    at Builder.toSQL (/home/user/Editor-NodeJS-1.9.0/node_modules/knex/lib/query/builder.js:119:44)
    at /home/user/Editor-NodeJS-1.9.0/node_modules/knex/lib/runner.js:52:32
    at tryCatcher (/home/user/Editor-NodeJS-1.9.0/node_modules/bluebird/js/release/util.js:16:23)
    at /home/user/Editor-NodeJS-1.9.0/node_modules/bluebird/js/release/using.js:185:26
    at tryCatcher (/home/user/Editor-NodeJS-1.9.0/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/home/user/Editor-NodeJS-1.9.0/node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (/home/user/Editor-NodeJS-1.9.0/node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromise0 (/home/user/Editor-NodeJS-1.9.0/node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (/home/user/Editor-NodeJS-1.9.0/node_modules/bluebird/js/release/promise.js:694:18)
    at Promise._fulfill (/home/user/Editor-NodeJS-1.9.0/node_modules/bluebird/js/release/promise.js:638:18)
    at PromiseArray._resolve (/home/user/Editor-NodeJS-1.9.0/node_modules/bluebird/js/release/promise_array.js:126:19)
    at PromiseArray._promiseFulfilled (/home/user/Editor-NodeJS-1.9.0/node_modules/bluebird/js/release/promise_array.js:144:14)
    at Promise._settlePromise (/home/user/Editor-NodeJS-1.9.0/node_modules/bluebird/js/release/promise.js:574:26)
    at Promise._settlePromise0 (/home/user/Editor-NodeJS-1.9.0/node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (/home/user/Editor-NodeJS-1.9.0/node_modules/bluebird/js/release/promise.js:694:18)

This is the code of the controller:

"use strict";

let db = require("../db");
let router = require("express").Router();

const {
  Editor,
  Field,
  Validate,
  Format,
  Options,
  Mjoin
} = require("datatables.net-editor-server");

router.all("/api/_users", async function(req, res) {

  // if (req.body.site !== null || isNaN(req.body.site)) {
  // } else {
    let editor = new Editor(db, "users")
      .fields(
        new Field("users.first_name"),
        new Field("users.last_name"),
        new Field("users.phone"),
        new Field("users.site").options(
          new Options()
            .table("sites")
            .value("id")
            .label("name")
        ),
        new Field("sites.name"),
      )
      .leftJoin("sites", "sites.id", "=", "users.site")
      .where("site", req.body.site);
    await editor.process(req.body);
    res.json(editor.data());
  //}
});

module.exports = router;

allan

The controller code looks okay, which is why a link to a page would be useful (you'd need to publish it somewhere for that to be possible though).

From the error generated it sounds like the client-side isn't actually sending any information to the server. At this point:

            data: function(d) {
                d.site = rowData.id;
            }

Can you add console.log(rowData) and tell me what the console shows it to be please?

Allan

tablo

The controller code looks okay

The line:

echo json_encode( [ "data" => [] ] );

from here:

if ( ! isset($_POST['site']) || ! is_numeric($_POST['site']) ) {
    echo json_encode( [ "data" => [] ] );
}

is totally missing.

and this:

    ajax: {
        url: "/api/_users",
        type: "post",
        data: function(d) {
            d.site = rowData.id;
            console.log("#####################################");
            console.log("rowData");
            console.log(rowData);
            console.log(d);
            console.log("#####################################");
        }
    },

outputs:

When I change to:
d.site = rowData.sites.id;

I get {site: 1} but nothing happens...

allan

The NodeJS version of that code is just:

if ( ! request.body.site ) {
    res.json({ data: [] });
}

That's just for the case when querying the table without giving a site though.

d.site = rowData.sites.id;

That sounds better. So on the server request.body.site is 1 at that point?

That should stop the undefined binding error you noted above.

Allan

tablo

So on the server request.body.site is 1 at that point?
That should stop the undefined binding error you noted above.

It is. Yes. It did also before but it was still showing "Loading".

That's just for the case when querying the table without giving a site though.

And this was the missing part to make it work...
Both tables (reading & writing) work fine now!!!

A last thing which I find weird. Why the API endpoint of the "users" here:
localhost:8081/api/_users

shows this:

{
"data": []
}

I mean, why is "data" an empty array? Is this OK?

allan

I mean, why is "data" an empty array? Is this OK?

Because without a site for the child table, you don't want to display anything in the child table. Your alternative would be to check for site and if not present, then display the information for all rows, regardless of site.

Good to hear we've got it working now.

Allan

tablo

OK. Thanks!