One more with a parent/child problem

ben.hell

Hi,

I am struggeling now for hours with a parent/child (1:n) table. The basic setup is quite simple:
There is one table called "kunde" with just id, name, description and iskunde (boolean for filtering). This one is related 1:n to another table called "kundealiases" with id, kunde and alias (sorry for the strange naming. That's how my collegue has setup the database...). The tables are not linked by id but rather by the "name" and "kunde" column, both varchar.

What is working:

• I can add entries to the parent table with no problem
• I can select an entry in the parent and the child table properly updates with the corresponding entries
• I can edit and remove entries in the child table

What is not working:

• when a parent entry is selected, adding a new child does not show the parent in the form, which would be "kunde.name"
• When manually entering the name and an alias I get an SQL error because "kunde" is null

I switched off the column constrains to get the sql in the debug output and actually the INSERT statement only contains the alias field. I am sure it must be something stupid simple, but I am almost blind of looking at the code now for hours, so a little help is greatly appreciated.

Thanks a lot in advance!

Ben

This is the code:

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

$(document).ready(function () {

  $("#btn_nav_home").button();
  $("#btn_nav_issues").button();
  $("#btn_nav_customers").button();
  $("#btn_nav_excludepatterns").button();

  ////////////////////////////////////////////////////////////////////////////////
  // customer (parent)
  ////////////////////////////////////////////////////////////////////////////////

  customer_editor = new $.fn.dataTable.Editor({
    ajax: "customers.php",
    table: "#customers",
    fields: [
      {
        label: "Customer",
        name: "name"
      },
      {
        label: "Description",
        name: "description"
      },
      {
        label: "iskunde",
        name: "iskunde",
        type: "hidden",
        def: "TRUE"
      }
    ]
  });

  // Enable edit on doubleclick
  $('#customers').on('dblclick', 'tr', function (e) {
    customer_editor.edit(this).title('Edit').buttons('Save');
  });

  customer_editor.on('submitSuccess', function() {
    customer_aliases_table.ajax.reload();
  });
  
  var customer_table = $('#customers').DataTable({
    pageLength: 25,
    lengthChange: false,
    ajax: "customers.php",
    "columns": [
      {
        "data": "name"
      },
      {
        "data": "description"
      }
    ],
    select: {
      style: "single"
    }
  });

  new $.fn.dataTable.Buttons(customer_table, [
    {
      extend: "create",
      editor: customer_editor
    },
    {
      extend: "edit",
      editor: customer_editor
    },
    {
      extend: "remove",
      editor: customer_editor
    },
    ]);

  customer_table.buttons().container()
    .prependTo($('div.fg-toolbar:eq(0)', customer_table.table().container()));

  customer_table.on('select', function() {
    customer_aliases_table.ajax.reload();
    customer_aliases.field('kundealias.kunde').def(customer_table({selected: true}).data().name);
  });
  
  customer_table.on('deselect', function() {
    customer_aliases_table.ajax.reload();
  });
  

  
  ////////////////////////////////////////////////////////////////////////////////
  // customer aliases (child)
  ////////////////////////////////////////////////////////////////////////////////

  customer_aliases_editor = new $.fn.dataTable.Editor({
    ajax: {
      url: "customer_aliases.php",
      data: function(d) {
        var selected = customer_table.row({selected: true});
        if(selected.any()) {
          d.kunde = selected.data().name;
          console.log("Kunde: "+d.kunde);
        }
      }
    },
    table: "#customer_aliases",
    fields: [
      {
        //type: "readonly",
        label: "Customer",
        name: "kunde.name"
      },
      {
        label: "Alias",
        name: "kundealias.alias"
      }
    ]
  });

  customer_aliases_editor.on('submitSuccess', function() {
    customer_table.ajax.reload();
  });

  var customer_aliases_table = $('#customer_aliases').DataTable({
    pageLength: 25,
    lengthChange: false,
    ajax: {
      url: "customer_aliases.php",
      type: 'post',
      data: function(d) {
        var selected = customer_table.row({selected: true});
        if(selected.any()) {
          d.kunde = selected.data().name;
        }
      }
    },
    "columns": [
      {
        "data": "kunde.name"
      },
      {
        "data": "kundealias.alias"
      }
    ],
    select: {
      style: "single"
    }
  });

  new $.fn.dataTable.Buttons(customer_aliases_table, [
    {
      extend: "create",
      editor: customer_aliases_editor
    },
    {
      extend: "edit",
      editor: customer_aliases_editor
    },
    {
      extend: "remove",
      editor: customer_aliases_editor
    },
  ]);

  customer_aliases_table.buttons().container()
    .prependTo($('div.fg-toolbar:eq(0)', customer_aliases_table.table().container()));

});

Server-side script (customers.php):

header('Content-Type: application/json');

include("../php/datatables/DataTables.php");

use DataTables\Editor;
use DataTables\Editor\Field;
use DataTables\Editor\Format;
use DataTables\Editor\Mjoin;
use DataTables\Editor\Options;
use DataTables\Editor\Upload;
use DataTables\Editor\Validate;

Editor::inst($db,'kunde')->fields(
  Field::inst('name'),
  Field::inst('description'),
  Field::inst('iskunde')
)->where('iskunde',"TRUE")->process($_POST)->json();

Server-side script (customer_aliases.php):

header('Content-Type: application/json');

include("../php/datatables/DataTables.php");

use DataTables\Editor;
use DataTables\Editor\Field;
use DataTables\Editor\Format;
use DataTables\Editor\Mjoin;
use DataTables\Editor\Options;
use DataTables\Editor\Upload;
use DataTables\Editor\Validate;

if (!isset($_POST['kunde'])) {
  echo json_encode(["data"=>[]]);
} else {
  Editor::inst($db,'kundealias')
    ->debug(true)
    ->fields(
    Field::inst('kunde.name'),
    Field::inst('kundealias.alias')
  )
  ->leftJoin('kunde','kunde.name', '=', 'kundealias.kunde')
  ->where('kunde',$_POST['kunde'])
  ->process($_POST)
  ->json();
}

rf1234

You need an options instance to select the parent kunde.name and insert it into kundealiases.kunde. I would also consider changing the database to have the id of kunde as a foreign key in kundealiases rather than the name of the client itself ..

Take a look at this please:
https://editor.datatables.net/manual/php/joins#Options

and this:
https://editor.datatables.net/examples/advanced/joinLinkTable.html

You see there are select fields at the front end and corresponding options instances at the back end that retrieve the options which can be selected at the front.

And a more sophisticated options example:
https://datatables.net/forums/discussion/comment/116199/#Comment_116199

ben.hell

Hi rf1234,

Thanks for the clarification. I have read through your referenced examples and I think I also have to clarify as well with the help of a screenshot of the GUI (left table is parent, right table is child, Customer ASYM2 is selected, as you can see).

In the form to add a child I have no select options. What I need is, that the form shows the currently selected customer in the Customer field and that the customer is correctly used in the INSERT INTO statement.

BTW: I know that the database design is, well, quite unusual. I will ask my collegue to fix this to be up to the standards.

Best,

Ben

rf1234

if (!isset($_POST['kunde'])) {
  echo json_encode(["data"=>[]]);
} else {
  Editor::inst($db,'kundealias')
    ->debug(true)
    ->fields(
    Field::inst('kundealiases.kunde')->set(Field::SET_CREATE)
                                     ->setValue($_POST['kunde']),
    Field::inst('kundealias.alias')
  )
  ->leftJoin('kunde','kunde.name', '=', 'kundealias.kunde')
  ->where('kunde',$_POST['kunde'])
  ->process($_POST)
  ->json();
}

if you really want to see kundealiases.kunde in the Editor javascript instance as well (wouldn't know why because you've selected that customer before and don't want to change it either) you would need to add the field in javascript Editor and disable it so that it can't be edited.

ben.hell

OK, I have it working now. I also removed the customer from the child table, as it was only for debugging & learning.

Thanks for your support!