Nested editing for additional fields on Mjoin link table

elmospa

Hi! I'm facing some difficulty with Editor.

A piece of my CRUD app have a N:M relationship between two tables: products [prodotti] and accessories [accessori]. The link table is products_accessories [accessori_prodotti] and have the two pks of the other tables. However, in addition to that, I need to store two optional extra fields that describe the link: minimum_qty [quantita_minima] and obligatory [obbligatorio].

I digged into other threads on this forum and found out it's not possible to manage this situation out of the box, so I tried to achieve this with nested editing.
What I have now is that the main editor form allows to link accessories to a product, and the nested editor allows for every row to add the additional fields.
However it seems it's not working: the nested editor doesn't save any data back to the main editor form, and the preSubmit log of the nested editor looks suspicious, eg:
preSubmit edit {"data":{"[object HTMLTableRowElement]":{"accessori_prodotti":{"quantita_minima":"2","obbligatorio":"1"}}},"action":"edit"}.

I was wondering if it's something feasible or if I'm on the wrong path.

This is the main editor:

var editor = new DataTable.Editor({
        ajax: 'dt-php/table.ACCESSORI.php',
        table: '#accessoriprodotti',
        fields: [
            {
                label: 'Codice:',
                name: 'prodotti.codice'
            },
            {
                label: 'Alfanumerico:',
                name: 'prodotti.alfanumerico'
            },
            {
                label: '',
                name: 'accessori_joined[].alfanumerico',
                type: 'datatable',
                multiple: true,
                optionsPair: {
                    value: 'alfanumerico'
                },
                config: {
                    paging: false,
                    scrollY: 250,
                    scrollCollapse: false,
                    editor: editorAttributiAssociazioni,
                    buttons: [
                        { extend: 'edit', editor: editorAttributiAssociazioni }
                    ],
                    columns: [
                        {
                            title: 'Codice',
                            data: 'codice'
                        },
                        {
                            title: 'Alfanumerico',
                            name: 'alfanumerico',
                            data: 'alfanumerico'
                        },
                        {
                            title: 'Descrizione',
                            name: 'descrizione_sintetica',
                            data: 'descrizione_sintetica'
                        },
                        {
                            title: 'Q.min',
                            data: 'accessori_prodotti.quantita_minima',
                            name: 'accessori_prodotti.quantita_minima'
                        },
                        {
                            title: 'Obb.',
                            data: 'accessori_prodotti.obbligatorio',
                            name: 'accessori_prodotti.obbligatorio',
                            type: "checkbox",
                            options: [
                                { label: '', value: 1 }
                            ],
                            separator: '',
                            unselectedValue: 0,
                            defaultContent : 0
                        },
                        {
                            data: null,
                            defaultContent: '<i class="bi bi-pencil-square"></i>'
                        }
                    ]
                    }
                }
            }
        ]
    });

and this is the nested editor:

var editorAttributiAssociazioni = new DataTable.Editor({
        idSrc: 'alfanumerico',
        fields: [
            {
                label: 'Quantità minima',
                name: 'accessori_prodotti.quantita_minima', def: 1, attr: { min: 0 }
            },

            {
                label: 'Obbligatorio',
                name: 'accessori_prodotti.obbligatorio',
                type: 'checkbox',
                options: [{ label: '', value: 1 }],
                separator: ','
            }
        ]
    });

Do you have any suggestion on this problem? Thank you in advance

rf1234

What I have now is that the main editor form allows to link accessories to a product, and the nested editor allows for every row to add the additional fields.
However it seems it's not working: the nested editor doesn't save any data back to the main editor form,

The child editor should contain two foreign keys - because it is a link table - and the additional fields. No data need to be saved "back to the main editor form".

It is just regular parent - child editing with the child table being a link table in addition. What you need in your child editor is e.g. a "select" field that allows you the selection of the "other" foreign key from the "other" parent table.

Take a look at this example please:
https://editor.datatables.net/examples/datatables/parentChild

Please also note the usage of "submit: false".

Too bad I don't have a use case with field type "datatable" for your case. I have many use cases of it from the time BEFORE field type "datatable" was introduced. I solved the problem by having a button on one of the parent tables that allowed opening a child data table with a child Editor which also allowed the foreign key selection from the "other" parent data table. If you are interested in that, I could post something.

elmospa

Thank you, I've checked that example already but I believe it's not as close to my case as I'd hope.

This is the main editor form:

Columns Codice, Alfanumerico and Descrizione are coming from the mjoin options list. Columns Q.min and Obb. are the extra columns present on the link table, but are not present as data on the options parameter coming from PHP editor (because they're not about the accessory to be linked, but about the already existing link.

This is the child editor form:

but it's not working, and don't actually know if it can be done with a N:M type of relationship.

About your use case, if I got it right, on the child editor you have to select again what parent row you're editing? What I'd like to achieve is clicking on the pencil icon on the right of the parent editor row, and have the child editor to be directly setted on that record and, so, foreign key.

Right now, as a plan B, I created a brand new page based 1:1 with the link table on the database, to allow the edit of the extra fields. But with that, every update on the association between a product and accessories, resets all extra fields.

rf1234

About your use case, if I got it right, on the child editor you have to select again what parent row you're editing?

Not really. That selection is implicit. But you need to select the second parent table's foreign key.

What are the error message you are getting?

In your code I don't understand this:

multiple: true,

It's either redundant or maybe even harmful. It's clear that your child table can have multiple records. That doesn't need to be specified.

I don't see this line:

submit: false,

If you don't have this line it means that accessori_joined[].alfanumerico is being submitted to the server. That usually causes a crash in parent child editing.

I had this in my code BEFORE Allan introduced "submit: false"

//not required any longer because we are setting "submit: false" above
    .on('preSubmit', function( e, d, action) {
        //this crap has to be deleted to make field type "datatable" work:
        //we are not updating ANYTHING in the parent table for the "datatable" fields
        if ( typeof d.data !== 'undefined' ) {
            var key = Object.keys(d.data)[0];
            delete d.data[key].sub_exec_cashflow;
            delete d.data[key].sub_earmark;
            delete d.data[key].sub_proof_schedule;
        }
    })  

I will post a simple example from my own coding in the subsequent post.

rf1234

I have a parent child Editor window with one parent editor (just the first two fields) and three child editors. I will limit this post to the first child.

So this is the parent Editor. 2 fields and then the field of type "datatable":

var subTablesEditor = new $.fn.dataTable.Editor({
    ajax: {
        url: 'actions.php?action=tblSubReg'
    },
    table: "#tblSubReg",
    i18n: { edit: { title: lang === 'de' ? 'Basistermine bearbeiten' : 'Edit Basis Due Dates' } },
    formOptions: {
        main: {
            focus: 1
        }
    },
    fields: [
         {
            label: lang === 'de' ? 'Maßnahme:' : 'Measure:',
            name:  "sub.sub_name",
            type:  "readonly"
        }, {
            label: lang === 'de' ? 'Mittelabruf-Automatik?' : 'Automatic Funds Calls?',
            name: "sub_exec.automatic_funds_calls",
            type: "checkbox",
            options: [
                {label: yes, value: 1}
            ],
            separator: '',
            unselectedValue: 0
        }, {
            label: lang === 'de' ? 'Mittelabruf(e):' : 'Call(s) for Funds:',
            name: 'sub_exec_cashflow[].id',
            type: 'datatable',
            editor: subExecCashflowEditor,
            submit: false,
            config: {
                searching: false,
                fixedHeader: false,
                paging: false,
                ajax: {
                    url: 'actions.php?action=tblSubExecCashflow',
                    type: 'POST',
                    data: function ( d ) {
                        d.ctr_id = parentId;
                        d.is_sub_provider = parentIsSubProvider;
                    }
                },
                language: languageEditorDts,
                buttons: [
                    { extend: 'create', editor: subExecCashflowEditor },
                    { extend: 'edit',   editor: subExecCashflowEditor },
                    { extend: 'remove', editor: subExecCashflowEditor }
                ],
                // order: [[0, 'asc']],
                order: [], //no ordering by Data Tables
                columns: [
                    {   title: lang === 'de' ? 'Frist Mittelabruf' : 'Deadline Funds Call',
                        data: 'sub_exec_cashflow.cashflow_due_date'         },
                    {   title: lang === 'de' ? 'Abrufbetrag' : 'Call Amount',
                        data: 'sub_exec_cashflow.cashflow_amount'           },
                    {   title: lang === 'de' ? 'Erste Erinnerung' : 'First Reminder',
                        data: 'sub_exec_cashflow.first_reminder_date'},
                    {   title: lang === 'de' ? 'Zweite Erinnerung' : '2nd Reminder',
                        data: 'sub_exec_cashflow.second_reminder_date'      },
                    {   title: lang === 'de' ? 'Datum Abruf / Zahlung' : 'Date Funds Call',
                        data: 'sub_exec_cashflow.cashflow_call_date'        },
                    {   title: lang === 'de' ? 'erwartet / erhalten' : 'expected / received',
                        data: 'sub_exec_cashflow.cashflow_amount_paid'           }
                ],
                rowCallback: function (row, data) {
                    if ( data.sub_exec_cashflow.is_repayment > 0 ) {
                        $(row).addClass('text-warning-plus');
                    } else {
                        $(row).removeClass('text-warning-plus');
                    }
                }
            }
        }, { 

I don't need "optionsPair" or "multiple".

And this is part of the child Editor (many fields - not interesting).

var subExecCashflowEditor = new $.fn.dataTable.Editor({
    ajax: {
        url: 'actions.php?action=tblSubExecCashflow',
        data: function ( d ) {
            d.ctr_id = parentId;
            d.is_sub_provider = parentIsSubProvider;
        }
    },
    formOptions: { main: { focus: null } },
    fields: [        
        {
            label: lang === 'de' ? 'Mittelrückzahlung?' : 'Funds Repayment?',
            name: "sub_exec_cashflow.is_repayment",
            type: "checkbox",
            options: [
                {label: yes, value: 1}
            ],
            separator: '',
            unselectedValue: 0
        }, {
            label: lang === 'de' ? 'Frist Mittelabruf:' : 'Deadline Funds Call:',
            name: "sub_exec_cashflow.cashflow_due_date",
            attr: {
                class: dateMask
            },
            type: "datetime",
            format: 'L',
            opts: {
                showWeekNumber: true,
                momentLocale: momentLocale
            }
        }, {

The PHP for the child Editor is not interesting. It is just like any other Editor. Please note: Since we set "submit: false" there are absolutely no changes to the parent PHP Editor because nothing from the child Editor is submitted via the parent!

Since the parent ID is not part of the child Editor I need to send it to the server in the child Editor somehow. It is the ID of the selected parent record.

Here is the relevant part in the PHP of the child Editor:

->leftJoin( 'ctr', 'sub_exec_cashflow.ctr_id', '=', 'ctr.id')
->where( function ( $q ) {        
    $q  ->where( 'sub_exec_cashflow.ctr_id', $_POST['ctr_id'] );
} )

And here is the JS for it (as stated above already). parentId and the other variable posted are taken from the selected parent record.

ajax: {
        url: 'actions.php?action=tblSubExecCashflow',
        data: function ( d ) {
            d.ctr_id = parentId;
            d.is_sub_provider = parentIsSubProvider;
        }
    },

elmospa

I'm kind of lost, since I can't figure out if it's something that can be done with additional fields that are directly present inside the link table, with a many-to-many relationship between two tables (products and accessories).

Since the example of the parent-child editing doesn't have the Server code section, I find it difficult to transpose that to my case.

This is currently the schema of this part of app:

Does this parent-child editing need the extra fields to be on another table that references the link table?

rf1234

Your case is straightforward and should work just the same way as the example from my own coding I presented above. Please try to adapt it. If you need more of my server PHP code just let me know. The fact that you are calling id fields "SKU" makes it a little more difficult because you need to make sure Editor knows this.

Does this parent-child editing need the extra fields to be on another table that references the link table?

No!

Looking at your code again I noticed that your nested Editor cannot work because you are not passing the parentID to the server as in my example above.

elmospa

Thank you for the check!
Can I ask you the server code for the parent and the child editor of your case?

rf1234

The server code of the parent Editor is huge because it is a very large data table with half a dozen JS Editors for it. So I can only give you a small excerpt from that. The total length of the server code for the parent Editor is 1,271 lines of code ... As I said, the parent Editor has absolutely no reference to the client Editor. So it is fairly unimportant anyway.

As you can see I don't need to specify the primary key of database table "ctr" because it is called "id".

But here is the code excerpt from the parent Editor:

Editor::inst( $db, 'ctr' )
    ->field(
        Field::inst( 'ctr.id' )->set( false ),
        Field::inst( 'ctr.is_subsidy_register' )->set(Field::SET_CREATE)
                                                ->setValue( 1 ),
        Field::inst( 'sub.sub_name' )
            ->validator( function ( $val, $data, $opts ) use ( $msg, $signsLit ) {
                if ( isset($val) ) {
                    if ( $val <= '' ) {
                        return $msg[0];
                    }
                    if ( mb_strlen($val) > 100 ) {
                        return $msg[6] . 100 . $signsLit;
                    }
                }
                return true;
            } ),
        Field::inst( 'sub.sub_partner_id' )
            ->validator( function ( $val, $data, $opts ) use ( $msg, $db ) {
                if ( ! isset($val) ) {
                    return true;
                }
                if ( $val <= '' ) {
                    return $msg[0];
                }
                $editingMode = false;
                //we are editing or deleting a record: otherwise ctr id wouldnt be set!
                if ( isset($data['ctr']['id']) ) {
                    if ( $data['ctr']['id'] > 0 ) {
                        $editingMode = true;
                    }
                }
                //we only need to validate if the value is set!! otherwise not relevant
                if ( ( ! $editingMode ) || ( ! isset($data['sub_partner']['is_sub_provider']) ) ) {
                    return true;
                }
                //return opposite due to partner perspective 
                $originalIsSubProvider = $data['sub_partner']['is_sub_provider'] < 1 ? 1 : 0;
                $res = $db->raw()
                    ->bind( ':sub_partner_id', $val )
                    ->exec( 'SELECT is_sub_provider 
                               FROM sub_partner
                              WHERE id = :sub_partner_id' );      
                $r = $res->fetch(PDO::FETCH_ASSOC);
                if ( ! empty($r) ) {
                    if ( $r['is_sub_provider'] != $originalIsSubProvider ) {
                        if ($_SESSION['lang'] === 'de') {   
                            return 'Sie können bei einer bestehenden Maßnahme nicht zwischen '
                            . 'Fördergeber und Fördernehmer wechseln.';
                        } 
                        return 'You cannot change between a supporter and a supported entity '
                        . 'in an existing project.';
                    }
                }
                return true;
            } )
            ->options( Options::inst()
                    ->table('sub_partner')
                    ->value('id')
                    ->label( ['partner_name', 'is_sub_provider'] )
                    ->render( function ( $row ) {    
                        if ( (bool)$row['is_sub_provider'] ) {
                            $type = $_SESSION['lang'] === "de" ? "Fördergeber: " : "Supporter: ";
                        } else {
                            $type = $_SESSION['lang'] === "de" ? "Fördernehmer: " : "Supported: ";
                        }
                        return $type . $row["partner_name"]; 
                    } )
                    //where clause MUST be a closure function in Options!!!
                    ->where( function($q) {                            
                        $q ->where('ctr_installation_id', 
                           '( SELECT DISTINCT a.ctr_installation_id
                                FROM ctr_govdept_has_ctr_installation a
                          INNER JOIN sub_govdept_has_user_complete b  ON 
                                     a.ctr_govdept_id = b.ctr_govdept_id
                               WHERE b.user_id = :user_id
                            )', 'IN', false);
                        $q ->bind( ':user_id', $_SESSION['id'] );
                    } )
            ),
        Field::inst( 'sub_partner.partner_name' )->set( false ),
        Field::inst( 'sub_partner.partner_name as sub_partner.more_partner_names' )->set( false )
            ->getFormatter( function($val, $data, $opts) use ( $db ){
                if ( $data['sub.main_project'] > 0 ) {
                    $lnk = getSubProjectIds( $data['ctr.id'], $db );
                    $ctrIdStringChildren = implode(", ", array_column($lnk, "linked_ctr_id"));             
                    $subProjectString = " AND b.ctr_id IN ( ' . $ctrIdStringChildren .  ' ) ";
                    $result = $db->raw()
                        ->bind( ':partnerName', $data['sub_partner.partner_name'] )
                        ->exec( 'SELECT DISTINCT a.partner_name
                                   FROM sub_partner a 
                             INNER JOIN sub b ON b.sub_partner_id = a.id 
                                  WHERE a.partner_name <> :partnerName ' 
                                        .  $subProjectString . ' 
                               ORDER BY 1' );      
                    $partners = $result->fetchAll(PDO::FETCH_ASSOC);
                    if ( ! empty($partners) ) {
                        return "<br>( " . implode("; ", array_column($partners, "partner_name")) . " )";
                    }
                }
                return "";
            }),
        Field::inst( 'sub_partner.is_sub_provider' )->set( false )
            ->getFormatter( function($val, $data, $opts) {
                return $val < 1 ? 1 : 0; //return opposite due to gov perspective
            }),

rf1234

And the code of the child Editor.

if ( ! isset($_POST['ctr_id']) || ! is_numeric($_POST['ctr_id']) ) {
    echo json_encode( [ "data" => [] ] );
} else {
    Editor::inst( $db, 'sub_exec_cashflow' )
    ->field(
        Field::inst( 'sub_exec_cashflow.id' )->set( false ),
        Field::inst( 'sub_exec_cashflow.ctr_id' )->set(Field::SET_CREATE)
                                                 ->setValue( filter_var($_POST['ctr_id']) ),
        Field::inst( 'sub_exec_cashflow.is_repayment' ),
        Field::inst( 'sub_exec_cashflow.cashflow_due_date' )
            ->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
            ->getFormatter( function ( $val, $data, $opts ) {
                return getFormatterDate($val);                   
            } )
            ->setFormatter( function ( $val, $data, $opts ) {
                return setFormatterDate($val);
            } ),
        Field::inst( 'sub_exec_cashflow.cashflow_amount' )
            ->getFormatter( function($val, $data, $opts) {
                return getFormatterAmount($val);
            })
            ->setFormatter( function($val, $data, $opts) {
                $isSubProvider = filter_var($_POST['is_sub_provider']);
                if ( (bool)$data["sub_exec_cashflow"]["is_repayment"] ) {
                    $isSubProvider = $isSubProvider < 1 ? 1 : 0; //return opposite due to repayment
                }
                return setFormatterSubAmount( $val, $isSubProvider ); //are we providing or receiving the subsidy?
            }),
        Field::inst( 'sub_exec_cashflow.first_reminder_date' )
            ->validator( function ( $val, $data, $opts ) use ($msg, $db) {
                if ( isset($val) ) {
                    if ( $val <= "" ) {
                        $automaticFundsCalls = false;
                        $res = $db->raw()
                            ->bind( ':ctr_id', $_POST['ctr_id'] )  
                            ->exec( 'SELECT automatic_funds_calls
                                       FROM sub_exec   
                                      WHERE ctr_id = :ctr_id' );
                        $row = $res->fetch(PDO::FETCH_ASSOC);
                        if ( ! empty($row) ) {
                            $automaticFundsCalls = (bool)$row["automatic_funds_calls"];
                        }
                        if ( ! $automaticFundsCalls ) {
                            return $msg[0];
                        }
                    }
                    $date = $data["sub_exec_cashflow"]["cashflow_due_date"];
                    if ( $val > "" && $date <= "" ) {
                        return $msg[1];
                    }
                    if ( setFormatterDate($val) >= setFormatterDate($date) ) {
                        return $msg[2];
                    }
                }
                return true;
            })        
            ->getFormatter( function ( $val, $data, $opts ) {
                return getFormatterDate($val);                   
            } )
            ->setFormatter( function ( $val, $data, $opts ) {
                return setFormatterDate($val);
            } ),
        Field::inst( 'sub_exec_cashflow.second_reminder_date' )
            ->validator( function ( $val, $data, $opts ) use ($msg) {
                if ( isset($val) ) {
                    if ( $val <= "" ) {
                        return true;
                    }
                    $date = $data["sub_exec_cashflow"]["first_reminder_date"];
                    if ( $val > "" && $date <= "" ) {
                        return $msg[3];
                    }
                    if ( setFormatterDate($val) <= setFormatterDate($date) ) {
                        return $msg[4];
                    }
                    $date = $data["sub_exec_cashflow"]["cashflow_due_date"];
                    if ( setFormatterDate($val) >= setFormatterDate($date) ) {
                        return $msg[2];
                    }
                }
                return true;
            })                
            ->getFormatter( function ( $val, $data, $opts ) {
                return getFormatterDate($val);                   
            } )
            ->setFormatter( function ( $val, $data, $opts ) {
                return setFormatterDate($val);
            } ),
        Field::inst( 'sub_exec_cashflow.cashflow_call_date' )
            ->getFormatter( function ( $val, $data, $opts ) {
                return getFormatterDate($val);                   
            } )
            ->setFormatter( function ( $val, $data, $opts ) use ($db) {
                if ( $val <= " " ) {
                    $automaticFundsCalls = false;
                    $res = $db->raw()
                        ->bind( ':ctr_id', $_POST['ctr_id'] )  
                        ->exec( 'SELECT automatic_funds_calls
                                   FROM sub_exec   
                                  WHERE ctr_id = :ctr_id' );
                    $row = $res->fetch(PDO::FETCH_ASSOC);
                    if ( ! empty($row) ) {
                        $automaticFundsCalls = (bool)$row["automatic_funds_calls"];
                    }
                    if ( $automaticFundsCalls ) {
                        return setFormatterDate( $data["sub_exec_cashflow"]["cashflow_due_date"] );
                    }
                }
                return setFormatterDate($val);
            } ),  
        Field::inst( 'sub_exec_cashflow.cashflow_amount_paid' )
            ->getFormatter( function($val, $data, $opts) {
                return getFormatterAmount($val);
            })
            ->setFormatter( function($val, $data, $opts) use ($db) {
                $isSubProvider = filter_var($_POST['is_sub_provider']);
                if ( (bool)$data["sub_exec_cashflow"]["is_repayment"] ) {
                    $isSubProvider = $isSubProvider < 1 ? 1 : 0; //return opposite due to repayment
                }
                if ( $val == 0 || $val <= '' || $val == "0,00" ) {
                    $automaticFundsCalls = false;
                    $res = $db->raw()
                        ->bind( ':ctr_id', $_POST['ctr_id'] )  
                        ->exec( 'SELECT automatic_funds_calls
                                   FROM sub_exec   
                                  WHERE ctr_id = :ctr_id' );
                    $row = $res->fetch(PDO::FETCH_ASSOC);
                    if ( ! empty($row) ) {
                        $automaticFundsCalls = (bool)$row["automatic_funds_calls"];
                    }
                    if ( $automaticFundsCalls ) {
                        return setFormatterSubAmount($data["sub_exec_cashflow"]["cashflow_amount"], $isSubProvider);
                    }
                }
                return setFormatterSubAmount($val, $isSubProvider); //are we providing or receiving the subsidy?
            })
    )
    ->leftJoin( 'ctr', 'sub_exec_cashflow.ctr_id', '=', 'ctr.id')
    ->where( function ( $q ) {        
        $q  ->where( 'sub_exec_cashflow.ctr_id', $_POST['ctr_id'] );
    } )
    ->on( 'postCreate', function ( $editor, $id, $values, $row ) {            
        logChange( $editor->db(), 'create', $id, $row, 'sub_exec_cashflow' );
    } )
    ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), 'edit', $id, $row, 'sub_exec_cashflow' );
    } )
    ->on( 'postRemove', function ( $editor, $id, $values ) {
        logChange( $editor->db(), 'delete', $id, $values, 'sub_exec_cashflow' );
    } )
    ->process($_POST)    
    ->json();
}

As you can see there is no options instance in the server code of the child table because my child table isn't a link table. You will need an options instance to select the options of the foreign key from the second parent table!

rf1234

The following editor is for parent child editing (I don't use field type "datatable" on the client side but that doesn't matter for the server side code! I coded this before field type "datatable" was introduced by Allan.)

This code edits a link table between USER and DEPARTMENT for the selected user. The link table is called "govdept_has_user" it contains the link between the two parent tables plus the role the user has for the selected department. The only editable fields in this editor are "govdept_has_user.govdept_id" i.e. the foreign key from the second parent table and "govdept_has_user.role" i.e. the role the user has for that department

if ( ! isset($_POST['user']) || ! is_numeric($_POST['user']) ) {
    echo json_encode( [ "data" => [] ] );
} else {
    if ($lang === 'de') {     
        $msg[0] = 'Feld darf nicht leer sein.';
    } else {
        $msg[0] = 'Field may not be empty.';
    }
    Editor::inst( $db, 'govdept_has_user' )
    ->field(
        Field::inst( 'govdept_has_user.user_id' )->set(Field::SET_CREATE)
                                                 ->setValue($_POST['user']),
        Field::inst( 'govdept_has_user.govdept_id' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
            ->options( Options::inst()
                ->table('govdept, gov')
                ->value('govdept.id')
                ->label( array('gov.name', 'govdept.name') )
                ->render( function ( $row ) {               
                    return $row['gov.name'].' / '.$row['govdept.name']; 
                } )
                ->order( 'gov.name asc' )
                //where clause MUST be a closure function in Options!!!
                ->where( function($q) {
                        $q ->where('govdept.gov_id', 'gov.id', '=', false); //join
                        if (isset($_SESSION['govCredUserId']) ) {
                            $q ->where( 'govdept.id', 
                                        '( SELECT DISTINCT govdept_id FROM govdept_has_user     
                                           WHERE user_id = :id AND role IN ("Administrator", "Principal" )
                                           ORDER BY govdept_id ASC  
                                           )', 'IN', false);
                            $q->bind( ':id', $_SESSION['govCredUserId'] );
                        }
                    } )
            ),
        Field::inst( 'govdept_has_user.role' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
        Field::inst( 'govdept.name' )->set( false ), 
        Field::inst( 'gov.name' )->set( false ), 
        Field::inst( 'gov.regional_8' )->set( false ), 
        Field::inst( 'gov.regional_12' )->set( false ), 
        Field::inst( 'gov.id AS govId' )->set( false ), 
        Field::inst( 'gov.is_client_marketplace' )->set( false ), 
        Field::inst( 'user.id AS userId' )->set( false ),                 
        Field::inst( 'user.type' )->set( false )              
    )
    ->leftJoin( 'user', 'user.id', '=', 'govdept_has_user.user_id' )
    ->leftJoin( 'govdept', 'govdept.id', '=', 'govdept_has_user.govdept_id' )
    ->leftJoin( 'gov', 'gov.id', '=', 'govdept.gov_id' )
    ->where( function ( $q ) {
        $q  ->where( 'govdept_has_user.user_id', $_POST['user'] );
        $q  ->where( 'user.type', 'G' );
        if (isset($_SESSION['govCredUserId']) ) {
            $q ->where( 'govdept_has_user.govdept_id', 
                        '( SELECT DISTINCT govdept_id FROM govdept_has_user     
                           WHERE user_id = :id AND role IN ("Administrator", "Principal" ) 
                           ORDER BY govdept_id ASC  
                           )', 'IN', false);
            $q->bind( ':id', $_SESSION['govCredUserId'] );
        }
    } )
    ->process($_POST)
    ->json();

elmospa

about your last example, does the parent editor server code use an Mjoin instance? Or does the link between the two records get created by the child editor with the two foreign key, that come from the parent editor via the ajax.data function?

I'm still trying to find out a way to set this up, but I'm not confident with the underlying working principle.

Would you mind sharing (the entire/a part) of the javascript of your last example?

Thank you very much for your time, I really appreciate.

elmospa

What I'm worried about is that it's something that can't be done, since on this thread Allan clearly stated it was not possible

rf1234

about your last example, does the parent editor server code use an Mjoin instance? Or does the link between the two records get created by the child editor with the two foreign key, that come from the parent editor via the ajax.data function?

The child editor creates the link! The parent editor does nothing in that regard.

By the way, my parent editor contains an Mjoin. But that is read-only!! Why? because an Mjoin can only edit link tables that consist of 2 fields: both MUST be foreign keys from the respective parent table. So the following Mjoin in the parent Editor is completely irrelevant for parent child editing. It is only used for displaying the content of the child table in the parent table at the front end. That is very important to understand!

//    show user roles        
->join(
    Mjoin::inst( 'govdept' )
        ->link( 'user.id', 'govdept_has_user.user_id' )
        ->link( 'govdept.id', 'govdept_has_user.govdept_id' )
        ->order( 'govdept.name asc' )
        ->fields(
            Field::inst( 'govdept.name AS deptName' )->set( false ),
            Field::inst( 'govdept_has_user.role AS userRole' )->set( false )
        )
    )

What I'm worried about is that it's something that can't be done, since on this thread Allan clearly stated it was not possible

No worries: I've been doing this for 8 years now! With and without field type "datatable". But again: You can't do it with an Mjoin! And that is what Allan is saying in the thread you are linking to:

Unfortunately, at the moment the linking table is that and that alone - a linking table. It doesn't support other columns, and indeed, if you edit a row with an Mjoin, it will delete other fields in the link table, so it is actually quite important that you don't use other columns (data loss).

allan

Amazing information @rf1234 - thank you for sharing this. If this forum had badges like SO, I'd be adding "parent / child editing master" to your account . Looking forward to discussing this type of editing further with you.

What I'm worried about is that it's something that can't be done, since on this thread Allan clearly stated it was not possible

Yes, that is still correct for an Mjoin with a link table. However, based on what I've read above, I think your situation is quite possible using the example @rf1234 linked to and not using Mjoin. The key difference would be that instead of showing a list of all accessories, you show only the accessories that have been assigned.

One question though: what is the table accessori_joined and how does it relate to accessori_prodotti? It is mentioned in your first post, but no where else.

There are so many ways of doing parent / child editing that it is all quite confusing, but in this case, what I would be tempted to do initially is setup regular Editor instance for accessori_prodotti and apply a WHERE filter to specify a single product. Also use Field::inst('product_sku')->setValue(3) (where 3 is changed to be whatever the product primary key is - i.e. the same as your WHERE filter).

Then you can add / edit / remove items from accessori_prodotti making a link to a single product, while setting the values for such as if it is obligatory, min quantity, etc. To select the accessory, you could use tags, select or datatable depending on what interface you want to present to the user.

Once you've got it working for a single product, we just need to generalise it, and that's where the example @rf1234 linked to comes it - you could think of it as just a wrapper to select a different product.

One step at a time - create an Editor for accessori_prodotti with a single product selected! Don't use Mjoin - it isn't needed in this case.

Allan

rf1234

One step at a time - create an Editor for accessori_prodotti with a single product selected! Don't use Mjoin - it isn't needed in this case.

Almost
This picture suggests that the parent Editor is on "accessori" and not on "prodotti". So it is probably the other way round:
- Select an accessory
- then open the child Editor to select a "prodotto"
- and to edit the additional attributes.

In my example above I do the same:
- Select a user
- then open the child Editor to select a "govdept" (department)
- and to edit the role.

This is what my child Editor looks like (again: not field type "datatable" but a "regular" Editor that sits in a bootstrap model with its own little data table.)

This is the child data table in the bootstrap modal:

And the child Editor. Very simple you can select a department and a role. That populates table govdept_has_user above. Done.

Finally I have a column displaying the selected roles in the parent data table. That is populated with the "read-only" Mjoin as described above.

elmospa

Thank you Allan!

Exactly @rf1234 , the parent editor is on "accessori" which is this page that used to show products and related accessories:

@allan I tried to go by steps and I created a table/editor for "accessori_prodotti" (product_accessories) link table that allows me to successfully set the two extra fields:

As a last resort this above might be fine, BUT extra fields get deleted whenever a product changes its accessories associations.

So the best way would be the parent editor (on "accessori") that lists all possible accessories for the selected product (e.g. "AAA"), and the link that is made by the child editor with the two extra fields.

But wouldn't this solutiong have the same issues of deleting extra data when the product-accessories association is edited?

Moreover, when I declare another editor + different ajax.url for the datatable field on the parent editor, I suspect that the options are expected to come from that url, but in my case are coming from the options array of "accessori". This is because the child editor only have the columns of the link table "prodotti_accessori" and doesn't have the list of accessories.

allan

This picture suggests that the parent Editor is on "accessori" and not on "prodotti". So it is probably the other way round

Gah! Thanks for spotting and correcting that.

As a last resort this above might be fine, BUT extra fields get deleted whenever a product changes its accessories associations.

Can you show me some code - what are you using to do this edit? Assuming you aren't using Mjoin, then that should be a simple table with a left join.

Allan