Combining 2 tables, editing only the second table

ostmal

Hello!
I've been suffering for two days and I'm asking for help.

I have two tables:
Table 1: "aaa_kv":
- id
- kv_num

Table 2: "aaa_fio":
- id
- kv_id
- fio_name

They are connected like this:
aaa_kv.id = aaa_fio. kv_id
Table 1 "aaa_kv" - should not be changed, and in Table 2 "aaa_fio" records can be added, edited and deleted.
Unfortunately, I did not find a suitable example for my case.

So I connected two tables using "join" and specially (for test purposes) output all the fields:
http://www.a0250268.xsph.ru/index.php
The output works fine, but how to make an edit (+add, + delete)?

Here I tried "Mjoin" - it does not work:
http://www.a0250268.xsph.ru/index.php/kv-mjoin

Here is the code with "join":

(function($){
$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: '/abc_crm/dt/my/kv/kv.php',
        table: '#aaa_kv',
        fields: [
            {
                label: "fio_name:",
                name: "aaa_fio.fio_name"
            }
        ]
    } );

    var table = $('#aaa_kv').DataTable( {
        dom: 'Bfrtip',
        ajax: '/abc_crm/dt/my/kv/kv.php',
        columns: [
            {data: "aaa_kv.id"},
            {data: "aaa_kv.kv_num"},
            {data: "aaa_fio.id"},
            {data: "aaa_fio.kv_id"},
            {data: "aaa_fio.fio_name"}
        ],
        select: true,
        lengthChange: false,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor },
            { extend: 'remove', editor: editor }
        ]
    } );
} );

}(jQuery));

<?php
// DataTables PHP library and database connection
include( "../../prog/php/lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'aaa_kv', 'id' )
    ->fields(
        Field::inst( 'aaa_kv.id' ),
        Field::inst( 'aaa_kv.kv_num' ),
        Field::inst( 'aaa_fio.id' ),
        Field::inst( 'aaa_fio.kv_id' ),
        Field::inst( 'aaa_fio.fio_name' )
    )
    ->leftJoin( 'aaa_fio', 'aaa_fio.kv_id', '=', 'aaa_kv.id' )
    ->process( $_POST )
    ->json();

ostmal

This is the code with "Mjoin":

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: '/abc_crm/dt/my/kv_mjoin/kv.php',
        table: '#aaa_kv',
        fields: [
            {
                label: "fio_name:",
                name: "fio_name"
            }
        ]
    } );

    var table = $('#aaa_kv').DataTable( {
        dom: 'Bfrtip',
        ajax: '/abc_crm/dt/my/kv_mjoin/kv.php',
        columns: [
            {data: "aaa_kv.id"},
            {data: "aaa_kv.kv_num"},
            {data: "id"},
            {data: "kv_id"},
            {data: "fio_name"}
        ],
        select: true,
        lengthChange: false,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor },
            { extend: 'remove', editor: editor }
        ]
    } );
} );

<?php
// DataTables PHP library and database connection
include( "../../prog/php/lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'aaa_kv', 'id' )
    ->fields(
        Field::inst( 'aaa_kv.id' ),
        Field::inst( 'aaa_kv.kv_num' )
    )
    // ->leftJoin( 'aaa_fio', 'aaa_fio.kv_id', '=', 'aaa_kv.id' )
    ->join(
        Mjoin::inst( 'aaa_fio' )
            ->link( 'aaa_kv.id', 'aaa_fio.kv_id' )
            ->fields(
                Field::inst( 'id' ),
                Field::inst( 'kv_id' ),
                Field::inst( 'fio_name' )
            )
    )
    ->process( $_POST )
    ->json();

ostmal

That didn't help:
https://editor.datatables.net/manual/php/mjoin
https://editor.datatables.net/examples/advanced/joinArray.html

ostmal

Please help me with your advice!

allan

Mjoin won't do what you are looking for here I'm afraid. The way it operates is based on reference links, so it will actually delete rows and then insert the new links, so if you have other columns in the table, it can actually result in lost data.

Normally the approach we've taken for editing nested tables is having another editable table inside your host editor - however, in this case you aren't editing the main table at all, so would it not be better to just use aaa_fio as the host table and leftJoin to aaa_kv?

Allan

ostmal

Allan, thank you. I was already thinking about the same thing, I'll try your advice.