Left Join – how to make correct editing

ostmal

There is a problem and I really hope for your help!
There is a list of apartments and a list of tenants, each of whom lives in a specific room.
I.e. we have 2 tables: "a_apartaments" and "a_people" with a one-many ratio.
I would like to create a shared table to edit the tenants in the apartment.

Option 1
Here I created the Left Join relation, "a_people" - on the left.
In the " JS "file ,I "catch" the line selection and send it to the PHP file of the apartment id to add new tenants: in this test version, before adding a new tenant, you need to select a line with the apartment number

JS

(function($){
$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        table: '#people_apartaments',
        ajax: '/abc_crm/dt/my/people_apartaments/people_apartaments.php',
        fields: [
            {
                label: "persons_name:",
                name: "a_people.persons_name"
            },
            {
                name: "a_people.apartment_id",
                type: "hidden",
                // If we enter a new tenant, then we take the apartment ID of the row that is HIGHLIGHTED!!!
                def:
                function () {
                    var data = $('#people_apartaments').DataTable().row('.selected').data();
                    return data["a_apartaments"]["id"];
                }
            }
        ]
    } );

    var table = $('#people_apartaments').DataTable( {
        dom: 'Bfrtip',
        ajax: '/abc_crm/dt/my/people_apartaments/people_apartaments.php',
        columns: [
            {data: "a_apartaments.apartment_number"},
            {data: "a_people.persons_name"}
        ],
        select: true,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor }
            // ,{ extend: 'remove', editor: editor }
        ]
    } );
} );
}(jQuery));

PHP

<?php
include( "../../prog/php/lib/DataTables.php" );

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

Editor::inst( $db, 'a_people', 'id' )
    ->fields(
        Field::inst( 'a_apartaments.id' ),
        Field::inst( 'a_apartaments.apartment_number' ),
        Field::inst( 'a_people.apartment_id' ),
        Field::inst( 'a_people.persons_name' )
    )
    ->leftJoin( 'a_apartaments', 'a_apartaments.id', '=', 'a_people.apartment_id' )
    ->process( $_POST )
    ->json();

In this option, you can edit the names of residents. But apartments with numbers THREE and FOUR are not displayed, where there is not a single tenant. And I DEFINITELY need a complete list of apartments to be displayed INITIALLY.
Then I made another option.

Option 2
Unlike the first option: I changed the Left Join relation on the contrary , "a_people" became on the right.

JS

(function($){
$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        table: '#apartaments_people',
        ajax: '/abc_crm/dt/my/apartaments_people/apartaments_people.php',
        fields: [
            {
                label: "persons_name:",
                name: "a_people.persons_name",
            },
            {
                name: "a_people.apartment_id",
                type: "hidden",
                // If we enter a new tenant, then we take the apartment ID of the row that is HIGHLIGHTED!!!
                def:
                function () {
                    var data = $('#apartaments_people').DataTable().row('.selected').data();
                    return data["a_apartaments"]["id"];
                }
            }
        ]
    } );

    var table = $('#apartaments_people').DataTable( {
        dom: 'Bfrtip',
        ajax: '/abc_crm/dt/my/apartaments_people/apartaments_people.php',
        columns: [
            {data: "a_apartaments.apartment_number"},
            {data: "a_people.persons_name"}
        ],
        select: true,
        buttons: [
            { extend: 'create', editor: editor },
            { extend: 'edit',   editor: editor }
            // ,{ extend: 'remove', editor: editor }
        ]
    } );


    editor.on( 'submitSuccess', function () {
        table.ajax.reload();
    } );
} );
}(jQuery));

PHP

<?php
include( "../../prog/php/lib/DataTables.php" );

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

Editor::inst( $db, 'a_apartaments', 'id' )
    ->fields(
        Field::inst( 'a_apartaments.id' ),
        Field::inst( 'a_apartaments.apartment_number' ),
        Field::inst( 'a_people.apartment_id' ),
        Field::inst( 'a_people.persons_name' )
    )
    ->leftJoin( 'a_people', 'a_people.apartment_id', '=', 'a_apartaments.id' )
    ->process( $_POST )
    ->json();

But, if there are several tenants living in the apartment. Then when you edit one – everything changes!
I don't know what to do.
These are all test tables, I ask you to do whatever you want.

allan

Would something like this work for you?

The leftJoin is for 1:1 relationships, which you note isn't right for your use case - so Mjoin (Many join) is the way to go here, and our way of being able to edit the information in the sub-table is through a DataTable inside the form.

Allan

ostmal

I'm sorry! The relevance of the topic has disappeared, you can delete it.