How to get Node editor to not INSERT but still run UPDATE on left table?

YOM

Version: "datatables.net-editor-server": "^2.0.8"

Short problem description: My editor instance handles the initial create POST request well, but subsequent updates to the LEFT JOIN table are not executed in the DB.

Full context below

Hi, I have the following relevant field configs

{
 "supply.inventory_id": {
    set: false,
  },
  "inventory.inventory_id": {
    set: false,
  },
  "inventory.prod_list_type": {
    formatter: (val, _) => trimStringSetEmptyToNull(val),
  },
  "inventory.inventory_shelf": {
    formatter: (val, _) => trimStringSetEmptyToNull(val),
  }
}

Here is the editor instance (shortened to only show relevant tables)

const editor = new Editor(db, "supply", "upc")
.leftJoin("inventory", "supply.inventory_id", "=", "inventory.inventory_id");

I am handling the insertion into the inventory table with the postCreate event on the editor. I have to do this because otherwise there's no way to retrieve the inventory_id after creation and update the supply row with it.

Currently the create form works as expected. However, probably because the inventory_id fields are using "set: false" any updates to the inventory.inventory_shelf field have no effect. If I remove the set: false the updates will work, but the editor will try creating rows on the inventory table from create form submissions.

How can I fix this? I'd like to handle the left join table insertion logic myself while still having the ability to edit fields in the left joined table normally.

Attempted solution:

I've tried using the set type option on one or both inventory_id fields to no avail

  "inventory.inventory_id": {
    setType: Field.SetType.EDIT,
  },

when I enable this option, for some reason the editor tries to insert into the inventory table during the create event

allan

Are you submitting the primary key for the joins?

What does:

 "supply.inventory_id": {
    set: false,
  },

result in for both the Javascript and C# code? There isn't a field.set option in the Javascript, although there is a Field.Set() method in C#, so I guess you are building the instance from the loop? Or is it doing that for the JS as well?

Are you able to link to a test case so I can see what the client-side is submitting?

Allan

YOM

Hey Allan, I am submitting all fields upon edit. You can see an example of the POST request here: https://pastebin.com/GhMyxxqE note the inventory_id fields

As to how the field configuration objects work in my backend, there is a loop that goes through all the field configs and adds it to the editor.

const addFieldByName = (editor, colConfig, colName) => {
  const field = new Field(colName);

  const config = colConfig[colName];

  if (config.validators !== undefined && config.validators.length > 0) {
    config.validators.forEach((validator) => field.validator(validator));
  }

  if (config.formatter) field.setFormatter(config.formatter);

  if (config.set === false) field.set(false);
  if (config.setType !== undefined) field.set(config.setType);

  editor.field(field);
};

colConfig in this case refers to an object containing all the field configurations, with the field name (called colName here) as the key.

Let me explain the two exact pieces of business logic I'm trying to implement

1. I need the supply table to be updated with the inventory_id from inventory when the inventory row is created
2. I need the field inventory.inventory_title to have a default value that uses the value of supply.supply_title

I'm currently doing this with an event listener as I'm not sure how else to get the inventory_id into the supply table, nor do I know how to set the inventory_title value. Maybe there is a better way?

function attachEventListeners(editor, knex) {
  editor.on("postCreate", async (editor, id, values, $row) => {
    try {
      if (values.duplicate_create) return; // don't create new inv if duplicating

      if ($row) {
        const inventoryRecord = {
          inventory_shelf: values.inventory.inventory_shelf,
          inventory_title: values.supply.supply_title,
        };

        const [inventoryId] = await knex("inventory").insert(inventoryRecord);

        await knex("supply")
          .where("upc", id)
          .update({ inventory_id: inventoryId });
      }
    } catch (error) {
      console.error("Error in postCreate event handler:", error);
    }
  });
}

And ofc because I'm using postCreate, I need editor to not try to INSERT into the inventory table.

Let me know if you understand the problem I am facing. It appears there may be a bug in setType: Field.SetType.EDIT or I am using it incorrectly

YOM

bump

allan

Gosh - sorry this slipped by me! Many thanks for the extra details. I've just looked through the logic for SetType.EDIT and I'm not immediately seeing an error there - that isn't to say there isn't one, I might just be missing it, but it isn't immediately apparent what it would be.

I'm wondering if it might be falling into this check and bailing out at that point. And if it is, why is it doing that, rather than matching a field.

I think that would be the first thing to determine. Is this project available somewhere that I might be able to run (and modify the node libraries) to trace it through? If not, could you add a break point or console.log statement there to see if it is getting that far, and if that is where it is bailing out of the edit / update?

Many thanks,
Allan

YOM

No worries Allan I figured it got buried, forum seems busy lately!

The issue with the set: false case is that it exits here in this part of the _insertOrUpdate function

else {
    // We need submitted information about the joined data to be
    // submitted as well as the new value. We first check if the
    // host field was submitted
    let field = this._findField(parentLink, 'db');

    if (! field || ! field.apply('edit', values)) {
        // If not, then check if the child id was submitted
        field = this._findField(childLink, 'db');

        if (! field || ! field.apply('edit', values)) {
            // No data available, so we can't do anything
            continue;
        }
    }

    whereVal = field.val('set', values);
}

it exits because supply.inventory_id is set to false so .apply() returns false

The issue with using set: Field.SetType.Edit is that even with this field type, the editor will try to insert rows if the foreign record doesn't exist as you can see here

else if (this.table().indexOf(table) === -1) {
    // Update on a linked table - the record might not yet exist, so need to check.
    let check = await this
        .db()
        .table(table)
        .select('*')
        .where(where);

    if (check && check.length) {
        await this
            .db()
            .table(table)
            .update(set)
            .where(where);
    }
    else {
        await this
            .db()
            .table(table)
            .insert({...set, ...where});
    }
}

Basically for the two bits of business logic I mentioned above it seems editor may not support it directly. Particularly, I'm not sure how to get the inventory_id from the row that was inserted into inventory without using the postCreate event. totally possible I'm overlooking a simpler approach.

For my case though, I can just dynamically enable/disable setting on these fields depending on the submitted action type and data. For anyone who might find this useful here is the function I'm using to do this:

function attachLeftJoinEditFix(editor, action, existingInventoryId) {
  const fieldsToDisable = ["supply.inventory_id", "inventory.inventory_id"];
  if ((action === "create" && !existingInventoryId) || !existingInventoryId) {
    for (const fldName of fieldsToDisable) {
      const field = editor.field(fldName);
      field.set(false);
    }
  }
}

allan

it exits because supply.inventory_id is set to false so .apply() returns false

Ah! Resulting in the insert. What a dilemma.

I don't think there is a better way around this at the moment than to write to the db directly in the event handlers (they were added to allow extra manipulation of the db, so this is within scope of their planned use).

I wonder if I should figure out a way to allow a join to be marked to have updates happen before writing to the main table. Currently they always happen after the main table update, but that causes a bunch of problems here.

An alternative option might be to do nested editing which simplifies the joined table editing actions.

Allan

YOM

Ah okay good to know this is probably the best approach atm. And can also sympathize with the difficulty in changing such things haha. Good luck if you decide to go for a change later on in the library and as always appreciate the help Allan