How to create/edit row in tables with a link table in between? [Editor]

tablo

Hi all,

I'm trying to use the Mjoin class and have the following issue with DataTables-Editor.

By using some code like this:

async function crawl(req, res) {
  const editor = new Editor(knex, "users", "user_name")
    .fields(
      new Field("users.id"),
      new Field("users.user_name"),
    )
    .join(
      new Mjoin("permissions")
        .link("users.user_name", "user_permission.user_name")
        .link("permissions.name", "user_permission.permission_name")
        .order("name asc")
        .fields(
          new Field("name"),
          new Field("category")
        )
    );
  await editor.process(req.body);
  res.json(editor.data());
}

I'm able to create or edit a row in the "users" table but not in the link table and the "permissions" table.

When I use Objection.js and something like this:

  await users.query().insertGraph({
    user_name: "John",
    user_permissions: [
      {
        name: "Printer"
      }
    ]
  });

I'm able to insert a row to all 3 table without a problem.

Any ideas what I'm doing wrong?

tablo

I just noticed that in the table "users" I'm passing a custom PK called "user_name".

The table "permissions" has also a custom PK called "name". How can I inform the Editor about this change? Or it is not necessary?

tablo

I tried this:
new Mjoin("permissions", "name")

but it didn't work. Editor didn't complain though.

allan

I'm able to create or edit a row in the "users" table but not in the link table and the "permissions" table.

You won't be able to create, edit or delete anything in the permissions table using Mjoin. But it should allow you to make changes in the user_permission table (simply an insert and delete, no update) - it is just a link table.

It should just use "permissions.name" to determine the column name and value to add into the link table.

Can you show me the data you are submitting to the server please?

Allan

tablo

@allan : Thanks for the response!

"permissions" is an array. When I use "permissions.name" and not "permissions[].name" I get a warning:

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

Can you show me the data you are submitting to the server please?

Do you mean this:

{
    "data": [{
        "DT_RowId": "row_abc",
        "users": {
            "id": 18,
            "user_name": "abc"
        },
        "permissions": []
    }],
    "fieldErrors": [],
    "debug": []
}

And this is how my script looks like:

              var editor; // use a global for the submit and return data rendering in the examples

              $(document).ready(function() {
                editor = new $.fn.dataTable.Editor({
                  ajax: "/api/v1/myapi",
                  table: "#example",
                  //idSrc: "DT_RowId",
                  fields: [
                    {
                      label: "users.name:",
                      name: "users.name"
                    },
                    {
                      label: "permissions.name",
                      name: "permissions[].name"
                    }
                  ]
                });

                // Activate an inline edit on click of a table cell
                $("#example").on("click", "tbody td:not(:first-child)", function(e) {
                  editor.inline(this);
                });

                $("#example").DataTable({
                  dom: "Bfrtip",
                  ajax: "/api/v1/myapi",
                  order: [[1, "asc"]],
                  columns: [
                    {
                      data: "users.id"
                    },
                    {
                      data: "users.name"
                    },

                    {
                      data: "permissions[].name"
                    }

                  ],
                  select: {
                    style: "os",
                    selector: "td:first-child"
                  },
                  buttons: [
                    {
                      extend: "create",
                      editor: editor
                    },
                    {
                      extend: "edit",
                      editor: editor
                    },
                    {
                      extend: "remove",
                      editor: editor
                    }
                  ]
                });
              });

When I use Objection.js I can the the comma separated permissions in the last column. When I try to edit them with Editor, nothing happens, not even an error. Same when I try to create a new user with permissions. Nothing happens.

tablo

When I use Objection.js I can see the comma separated permissions in the last column.

Sorry, I see no option to edit...

tablo

You won't be able to create, edit or delete anything in the permissions table using Mjoin. But it should allow you to make changes in the user_permission table (simply an insert and delete, no update) - it is just a link table.

I thought it would be possible. Is this not very limiting? How can I deal with many Tables with relationships?

Why Editor does not use libraries such as Objection or Bookshelf instead of only Knex.js? Wouldn't this approach address the limitation?

allan

Thanks for the extra information - I think I might understand the issue now. Is it that you are always wanting an insert into the linked tables. Not just to create a link between existing records?

What makes me think that is the use of:

      {
        label: "permissions.name",
        name: "permissions[].name"
      }

without type: 'checkbox' or similar to select existing entries.

Editor's Mjoin action will just add a link between existing records - it doesn't aim to create a new record in the mjoined table. For that a second table / editor instance would be needed (like here).

Why Editor does not use libraries such as Objection or Bookshelf instead of only Knex.js? Wouldn't this approach address the limitation?

Possibly, but I wanted lower level access to the databases, to allow the APIs for our PHP, .NET and NodeJS libraries to be similar. I might diverge them in future, but it makes maintenance almost manageable .

Allan

tablo

Is it that you are always wanting an insert into the linked tables. Not just to create a link between existing records?

Yes! Sorry, the example with the permissions was rather misleading. The names of my tables and fields are different.

Editor's Mjoin action will just add a link between existing records - it doesn't aim to create a new record in the mjoined table.

Maybe you should add this information to the documentation. From reading the doc I got the impression that Editor fully supports CRUD operations for one-to-many relationships.

For that a second table / editor instance would be needed

Thanks for the hint and the example! I was thinking the same. The example looks very interesting.

Possibly, but I wanted lower level access to the databases, to allow the APIs for our PHP, .NET and NodeJS libraries to be similar. I might diverge them in future, but it makes maintenance almost manageable

Understood! It makes sense now!

Thanks!