Update only main table, not table from Left Join

danilorago

Dear all,

I'm making a model, which I'll present this week, to possibly buy the solution. I'm excited about DataTables, but I'm still learning how to use it.
I'm here to turn to you once again.
I have a call to a table that establishes the relationship between the other two.
This table only has the ID of two other tables, which in turn have the ID of other tables from which I bring the information just to show on the screen to make it easier for the user.
The first table is the processes:

That is linked to the Relation Between End To End and Process:

And the table of End to End:

Then on the table Process I have a relation with the Business Area, that have link to the Project:

Now the interesting part, in my code, to bring information from all tables, and be as shown in the image below, I do LEFT JOIN in several of them, but when I have to ADD a new row, I only need two pieces of information in the "Relation Between End To End and Process" table, which are ID__Business__Process and ID__Business__End_To_End_Process, but as there is LEFT JOIN, when creating a new row, the information of ID__Business__Area of ​​the Business__Process table is changed, which is exactly what I don't want, I just want to add the information to the main table and not change any information in the JOIN tables.

Here the image of the creation, with the only fields that I need:

Now the question is, how to create one item, inserting just on the main table, don't updating the LEFT JOINs, cause when I create a new line, as the ID__Business__Process isn't on the form, send a value "0" (Zero) to the LEFT JOIN table.

HTML

<script>
addEventListener("DOMContentLoaded", function () {
var editor = new DataTable.Editor( {
    ajax: 'datatables--business-end-to-end-process-vs-process.php',
    table: '#DT__List',
    formOptions: {
        main: {
            focus: null,
            submit: 'changed'
        }
    },
    fields: [
        {
        "label": "ID__REL__Business__End_To_End_Process_X_Business__Process :",
        "name": "REL__Business__End_To_End_Process_X_Business__Process.ID__REL__Business__End_To_End_Process_X_Business__Process",
        "type": "readonly",
        "attr": {"disabled": true}
        },
        {
        "label": "ID__Project:",
        "name": "Business__Area.ID__Project",
        "type": "select",
        "placeholder": "Select the project!",
        "def": <?php echo $_SESSION["ID__Project"]; ?>,
        "attr": {"disabled": true}
        },
        {
        "label": "ID__Business__Area:",
        "name": "Business__Process.ID__Business__Area",
        "type": "select",
        "placeholder": "Select the area!",
        "attr": {"disabled": true}
        },
        {
        "label": "ID__Business__End_To_End_Process:",
        "name": "REL__Business__End_To_End_Process_X_Business__Process.ID__Business__End_To_End_Process",
        "type": "select",
        "placeholder": "Select the end to end process!"
        },
        {
        "label": "ID__Business__Process:",
        "name": "REL__Business__End_To_End_Process_X_Business__Process.ID__Business__Process",
        "type": "select",
        "placeholder": "Select the process!"
        },
        {
        "label": "REL__Business__End_To_End_Process_X_Business__Process__Date:",
        "name": "REL__Business__End_To_End_Process_X_Business__Process.REL__Business__End_To_End_Process_X_Business__Process__Date",
        "type": "readonly",
        "attr": {"disabled": true}
        },
        {
        "label": "ID__Login:",
        "name": "REL__Business__End_To_End_Process_X_Business__Process.ID__Login",
        "type": "readonly",
        "attr": {"disabled": true}
        }
    ]
} );
var table = new DataTable('#DT__List', {
    ajax: 'datatables--business-end-to-end-process-vs-process.php',
    columns: [
    {"data": "REL__Business__End_To_End_Process_X_Business__Process.ID__REL__Business__End_To_End_Process_X_Business__Process"},
    {"data": "Project.Project__Name","editField": "Business__Area.ID__Project"},
    {"data": "Business__Area.Business__Area","editField": "Business__Process.ID__Business__Area"},
    {"data": "Business__End_To_End_Process.Business__End_To_End_Process__Name","editField": "REL__Business__End_To_End_Process_X_Business__Process.ID__Business__End_To_End_Process"},
    {"data": "Business__Process.Business__Process__Name","editField": "REL__Business__End_To_End_Process_X_Business__Process.ID__Business__Process"},
    {"data": "REL__Business__End_To_End_Process_X_Business__Process.REL__Business__End_To_End_Process_X_Business__Process__Date"},
    {"data": "Login.Login__Name","editField": "REL__Business__End_To_End_Process_X_Business__Process.ID__Login"}
    ],
    colReorder: true,
    fixedColumns: {start: 1},
    order: [[0, 'desc']],
    pageLength: 20,
    paging: true,
    scrollX: true,
    scrollY: 550,
    select: {attr: {className: 'selected-row'},selector: 'td:first-child'
    }
});
editor.on('open', function(e, mode, action){
    if((action === 'create')||(action === 'edit')){
        this.hide(['REL__Business__End_To_End_Process_X_Business__Process.ID__REL__Business__End_To_End_Process_X_Business__Process']);
        this.hide(['Business__Area.ID__Project']);
        this.hide(['Business__Process.ID__Business__Area']);
        this.hide(['REL__Business__End_To_End_Process_X_Business__Process.REL__Business__End_To_End_Process_X_Business__Process__Date']);
        this.hide(['REL__Business__End_To_End_Process_X_Business__Process.ID__Login']);
    } else {
        this.show(['REL__Business__End_To_End_Process_X_Business__Process.ID__REL__Business__End_To_End_Process_X_Business__Process']);
        this.show(['Business__Area.ID__Project']);
        this.show(['Business__Process.ID__Business__Area']);
        this.show(['REL__Business__End_To_End_Process_X_Business__Process.REL__Business__End_To_End_Process_X_Business__Process__Date']);
        this.show(['REL__Business__End_To_End_Process_X_Business__Process.ID__Login']);
    }
})
editor.on('submitSuccess', () => {
    $('#DT__List').DataTable().ajax.reload();
});
table.on('click', 'tbody td:not(:first-child)', function (e) {
    editor.inline(this, {
        onBlur: 'submit'
    });
});
});
</script>

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

Moved PHP code to allow syntax formatting and highlighting:

PHP

<?php
// >>> SESSION (PROJECT ID)
session_start();
$Int__ID__Project = $_SESSION["ID__Project"];
// <<< SESSION (PROJECT ID)

include "DataTables/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,
    "REL__Business__End_To_End_Process_X_Business__Process",
    "ID__REL__Business__End_To_End_Process_X_Business__Process"
)
    ->fields(
        Field::inst(
            "REL__Business__End_To_End_Process_X_Business__Process.ID__REL__Business__End_To_End_Process_X_Business__Process"
        ),
        Field::inst("Business__Area.ID__Project")
            ->options(
                Options::inst()
                    ->table("Project")
                    ->value("ID__Project")
                    ->label("Project__Name")
            )
            ->validator(
                Validate::notEmpty(
                    ValidateOptions::inst()->message(
                        "Error: Fill the Project field!"
                    )
                )
            ),
        Field::inst("Project.Project__Name"),
        Field::inst("Business__Process.ID__Business__Area")->options(
            Options::inst()
                ->table("Business__Area")
                ->value("ID__Business__Area")
                ->label("Business__Area")
        ),
        Field::inst("Business__Area.Business__Area"),
        Field::inst(
            "REL__Business__End_To_End_Process_X_Business__Process.ID__Business__End_To_End_Process"
        )
            ->options(
                Options::inst()
                    ->table("Business__End_To_End_Process")
                    ->value("ID__Business__End_To_End_Process")
                    ->label("Business__End_To_End_Process__Name")
            )
            ->validator(
                Validate::notEmpty(
                    ValidateOptions::inst()->message(
                        "Error: Fill the Business Area field!"
                    )
                )
            ),
        Field::inst(
            "Business__End_To_End_Process.Business__End_To_End_Process__Name"
        ),
        Field::inst(
            "REL__Business__End_To_End_Process_X_Business__Process.ID__Business__Process"
        )
            ->options(
                Options::inst()
                    ->table("Business__Process")
                    ->value("ID__Business__Process")
                    ->label("Business__Process__Name")
            )
            ->validator(
                Validate::notEmpty(
                    ValidateOptions::inst()->message(
                        "Error: Fill the Business Area field!"
                    )
                )
            ),
        Field::inst("Business__Process.Business__Process__Name"),
        Field::inst(
            "REL__Business__End_To_End_Process_X_Business__Process.REL__Business__End_To_End_Process_X_Business__Process__Date"
        )->setValue(date("Y-m-d")),
        Field::inst(
            "REL__Business__End_To_End_Process_X_Business__Process.ID__Login"
        )
            ->setValue($_SESSION["ID__Login"])
            ->options(
                Options::inst()
                    ->table("Login")
                    ->value("ID__Login")
                    ->label("Login__Name")
            )
            ->validator(
                Validate::notEmpty(
                    ValidateOptions::inst()->message(
                        "Error: Fill the Login Name field!"
                    )
                )
            ),
        Field::inst("Login.Login__Name")
    )
    ->leftJoin(
        "Business__End_To_End_Process",
        "Business__End_To_End_Process.ID__Business__End_To_End_Process",
        "=",
        "REL__Business__End_To_End_Process_X_Business__Process.ID__Business__End_To_End_Process"
    )
    ->leftJoin(
        "Business__Process",
        "Business__Process.ID__Business__Process",
        "=",
        "REL__Business__End_To_End_Process_X_Business__Process.ID__Business__Process"
    )
    ->leftJoin(
        "Business__Area",
        "Business__Area.ID__Business__Area",
        "=",
        "Business__Process.ID__Business__Area"
    )
    ->leftJoin(
        "Project",
        "Project.ID__Project",
        "=",
        "Business__Area.ID__Project"
    )
    ->leftJoin(
        "Login",
        "Login.ID__Login",
        "=",
        "REL__Business__End_To_End_Process_X_Business__Process.ID__Login"
    )
    ->where("Business__Area.ID__Project", $Int__ID__Project)
    ->process($_POST)
    ->json();

allan

Add ->set(false) to the Field instances on the server-side that you don't want updated / created. E.g.:

Field::inst("Business__Process.Business__Process__Name")
  ->set(false)

Allan

danilorago

Hello @allan
Thanks for the update, but didn't worked.
I did like:

```
<?php
Field::inst('Business__Process.ID__Business__Area')
->options(
Options::inst()
->table('Business__Area')
->value('ID__Business__Area')
->label('Business__Area')
)
->set(false)
,
Field::inst('Business__Area.Business__Area')
->set(false),

<?php > ``` ?>

danilorago

It's still creating a line on "Business__Area" table

danilorago

Worked, it was my mistake, tks a lot @allan

allan

Super - great to hear you've got it working as you need! Thanks for the update.

Allan