Combining 2 tables, editing only the second table

Combining 2 tables, editing only the second table

ostmalostmal Posts: 102Questions: 33Answers: 0
edited June 2021 in Editor

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();

Answers

  • ostmalostmal Posts: 102Questions: 33Answers: 0
    edited June 2021

    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();
    
    
    
  • ostmalostmal Posts: 102Questions: 33Answers: 0

    Please help me with your advice!

  • allanallan Posts: 61,446Questions: 1Answers: 10,055 Site admin

    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

  • ostmalostmal Posts: 102Questions: 33Answers: 0

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

Sign In or Register to comment.