Update only main table, not table from Left Join

Update only main table, not table from Left Join

daniloragodanilorago Posts: 19Questions: 4Answers: 0
edited March 10 in Free community support

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.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 64,143Questions: 1Answers: 10,584 Site admin
    edited March 10

    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();
    
    
  • allanallan Posts: 64,143Questions: 1Answers: 10,584 Site admin

    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

  • daniloragodanilorago Posts: 19Questions: 4Answers: 0

    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 > ``` ?>
  • daniloragodanilorago Posts: 19Questions: 4Answers: 0

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

  • daniloragodanilorago Posts: 19Questions: 4Answers: 0

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

  • allanallan Posts: 64,143Questions: 1Answers: 10,584 Site admin
    Answer ✓

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

    Allan

Sign In or Register to comment.