Parent / child editing in child rows for NodeJS

Parent / child editing in child rows for NodeJS

tablotablo Posts: 58Questions: 13Answers: 0

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...

This question has an accepted answers - jump to answer

Answers

  • daduffydaduffy Posts: 31Questions: 5Answers: 1

    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

  • tablotablo Posts: 58Questions: 13Answers: 0

    @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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    .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

  • tablotablo Posts: 58Questions: 13Answers: 0

    @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?

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    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

  • tablotablo Posts: 58Questions: 13Answers: 0

    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?

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin
    edited July 2019

    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

  • tablotablo Posts: 58Questions: 13Answers: 0

    @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);
                }
            });
        });
    });
    
  • tablotablo Posts: 58Questions: 13Answers: 0

    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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    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

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

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

    Allan

  • tablotablo Posts: 58Questions: 13Answers: 0

    @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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    What a muppet - sorry! Proper fix is here.

    Allan

  • tablotablo Posts: 58Questions: 13Answers: 0

    @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!"

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    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

  • tablotablo Posts: 58Questions: 13Answers: 0

    @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>

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Ah, Allan meant to use the debugger,

    C

  • tablotablo Posts: 58Questions: 13Answers: 0

    @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?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

  • tablotablo Posts: 58Questions: 13Answers: 0

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

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    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

  • tablotablo Posts: 58Questions: 13Answers: 0

    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.

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    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

  • tablotablo Posts: 58Questions: 13Answers: 0

    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;
    
  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    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

  • tablotablo Posts: 58Questions: 13Answers: 0

    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...

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin

    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

  • tablotablo Posts: 58Questions: 13Answers: 0

    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?

  • allanallan Posts: 61,439Questions: 1Answers: 10,052 Site admin
    Answer ✓

    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

  • tablotablo Posts: 58Questions: 13Answers: 0

    OK. Thanks!

This discussion has been closed.