mjoin options field more than 2 fields on insert/update

atrego

Hello,

I'm currently using an mjoin options field to add / remove users to usergroups and I'd like to be able to also write the id of the user who created that record along with the userId and groupId to the MM table so far I've not been able to find how to do that with the editor php class.

        ->join(
            Mjoin::inst('User')
                ->link('userGroups.id', 'usersGroupsMM.userGroupId')
                ->link('users.id', 'usersGroupsMM.userId')
                ->order('id asc')
                ->fields(
                    Field::inst('id')
                        ->options(function ($d) {
                            return $d->select('users', array('id,firstname,secondname,username, id AS value'))->fetchAll();
                        })
                        ?->validator(Validate::dbValues(null, 'id', 'users')),
                )
        )

javascript part
new $.fn.dataTable.Editor( {
ajax: "{{ viewGroupsDataTable }}",
table: "#groupTable",
fields: [{
label: "Benutzer",
name: "users[].id",
type: "datatable",
multiple: true,
config: {
paging: false,
scrollY: 150,
scrollCollapse: true,
dom: '<"row"<"col-12"f>>rt<"row"<"col-12"p>>',
columns: [
{
title: 'Vorname',
data: 'firstname',
},
{
title: 'Nachname',
data: 'secondname',
},
{
title: 'Benutzer',
data: 'username',
}
]
} ]} );

rf1234

As far as I know you can't do that. A link table should not consist of more than two columns. If your link table has more than two columns you will have to update the other columns outside the MJoin.

One option could be to use the new field type "datatable". That would allow you to do a lot more editing. I am using it myself but only for things that @allan hasn't designed it for ... but it works

Your use case sounds much close to what field type "datatable" was designed for.

If you use "nested editing" for example https://editor.datatables.net/examples/datatables/nested.html
you could easily add the user id of the updater / creator in your back end Editor instance and insert $_SESSION['id'] for example.

field type "datatable" gives you a lot more flexibility because each field of that type basically has its own Editor instance client side. And it can also have its own Editor instance server side (but I don't think Allan has a use case for that but I do in my own coding).

atrego

I'm already using field type "datatable" and getting it to work with the mjoin by making sure I provide the value field by "cheating" in the select with id AS value. Since you are using field type pretty extensivly do you know if all the normal datatable options are available for it? Because I was trying to group rows in said fieldType and that would not work at all. Also column width seems to have some issues.

Width working after resizing browser window below not working on inital opening of the modal..

rf1234

I am not using everything to be honest. I know those datatables have a couple of limitations compared with the "original" ones.

But I am not using it with an Mjoin but completely independently of the parent table. What I basically do with them is parent - child editing. In my opinion that is far more flexible. Hence my field types "datatable" have their own Editor instance also on the back end.

I can share a code example and what it is about.
I have something called a "subsidy" (this is about German local government). The states and the federal government give those subsidies to counties and municipalities.

If you have received a subsidy for - say - a new bridge that grant is usually earmarked. Meaning you can't spend the money on something else.
This earmarking can be quite detailed with various periods and what have you. German bureaucracy if you know what I mean

When editing the subsidy you can edit all of the earmarking periods and the like on the fly using field type "datatable".

This is that field as part of the parent Editor instance ("subTablesEditor"):

...
}, {
    label: lang === 'de' ? 'Zweckbindungsfrist(en):' : 'Earmarking Period(s):',
    name: 'sub_earmark[].id',
    type: 'datatable',
    editor: subEarmarkEditor,
    config: {
        searching: false,
        fixedHeader: false,
        paging: false,
//                scrollY: "150px",
//                scrollCollapse: true,
        ajax: {
            url: 'actions.php?action=tblSubEarmark',
            type: 'POST',
            data: function ( d ) {
                d.ctr_id = parentId;
                d.is_sub_provider = parentIsSubProvider;
            }
        },
        language: languageEditorDts,
        buttons: [
            { extend: 'create', editor: subEarmarkEditor },
            { extend: 'edit',   editor: subEarmarkEditor },
            { extend: 'remove', editor: subEarmarkEditor }
        ],
        order: [[1, 'asc']],
        columns: [
            {   title: lang === 'de' ? 'Gegenstand' : 'Subject',
                data: 'sub_earmark.earmark_subject'         },                    
            {   title: lang === 'de' ? 'Zweckbindungsfrist' : 'Earmarking Period',
                data: 'sub_earmark.earmark_exp_date'         },
            {   title: lang === 'de' ? 'Zweckgebundener Betrag' : 'Earmarked Amount',
                data: 'sub_earmark.earmark_amount'           }
        ]
    }    
}, {
...

This is the client side Editor instance mentioned above as "editor".

var subEarmarkEditor = new $.fn.dataTable.Editor({
    ajax: {
        url: 'actions.php?action=tblSubEarmark',
        data: function ( d ) {
            d.ctr_id = parentId;
            d.is_sub_provider = parentIsSubProvider;
        }
    },
    formOptions: { main: { focus: null } },
    fields: [ {   
            label: lang === 'de' ? 'Gegenstand der Zweckbindung:' : 'Earmark Subject:',
            name:  "sub_earmark.earmark_subject",        
        }, {
            label: lang === 'de' ? 'Zweckbindungsfrist:' : 'Earmarking Period:',
            name: "sub_earmark.earmark_exp_date",
            attr: {
                class: dateMask
            },
            type: "datetime",
            format: 'L',
            opts: {
                showWeekNumber: true,
                momentLocale: momentLocale
            }
        }, {
            label: lang === 'de' ? 'Zweckgebundener Betrag:' : 'Earmarked Amount:',
            name:  "sub_earmark.earmark_amount",
            attr: {
                class: amountMask,
                placeholder: "optional"
            }
        }
    ]
});

As designed by Allan the parent table's Editor expects to pass the field of type "datatable" to the server to be processed in that parent Editor. Since I don't want that because I want true parent - child editing I have to delete that stuff so that it doesn't get passed to the server with the parent Editor's data.

subTablesEditor
    .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_earmark;
        }
    })        

Finally my field type "datatable" Editor has its own Editor instance in PHP as well:

if ($lang === 'de') {     
    $msg[0] = 'Feld darf nicht leer sein.';
} else {
    $msg[0] = 'Field may not be empty.';
}
if ( ! isset($_POST['ctr_id']) || ! is_numeric($_POST['ctr_id']) ) {
    echo json_encode( [ "data" => [] ] );
} else {
    Editor::inst( $db, 'sub_earmark' )
    ->field(
        Field::inst( 'sub_earmark.ctr_id' )->set(Field::SET_CREATE)
                                                 ->setValue( filter_var($_POST['ctr_id']) ),
        Field::inst( 'sub_earmark.earmark_subject' ),
        Field::inst( 'sub_earmark.earmark_exp_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_earmark.earmark_amount' )
            ->getFormatter( function($val, $data, $opts) {
                return getFormatterAmount($val);
            })
            ->setFormatter( function($val, $data, $opts) {
                return setFormatterSubAmount($val, filter_var($_POST['is_sub_provider'])); //are we providing or receiving the subsidy?
            }),
        Field::inst( 'sub_earmark.earmark_amount as unformattedAmount' )->set( false )
    )
    ->leftJoin( 'ctr', 'sub_earmark.ctr_id', '=', 'ctr.id')
    ->where( function ( $q ) {        
        $q  ->where( 'sub_earmark.ctr_id', $_POST['ctr_id'] );
    } )
    ->on( 'postCreate', function ( $editor, $id, $values, $row ) {            
        logChange( $editor->db(), 'create', $id, $row, 'sub_earmark' );
    } )
    ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), 'edit', $id, $row, 'sub_earmark' );
    } )
    ->on( 'postRemove', function ( $editor, $id, $values ) {
        logChange( $editor->db(), 'delete', $id, $values, 'sub_earmark' );
    } )
    ->on( 'postGet', function ( $e, &$data, $id ) use ( $lang ) { 
        if ( count($data) <= 0 ) {
            return;
        }
        $sum = array_sum(array_column($data, "unformattedAmount"));
        $keys = array_keys($data);
        foreach ( $keys as $key ) {
            unset($data[$key]["unformattedAmount"]);
        }
        if ( $sum != 0 ) {
            $data[] = [ "DT_RowId" => "row_0", //in reality there is no row 0 because it is derived from the SQL id
                        "sub_earmark" => 
                            [ "ctr_id"           => $_POST['ctr_id'], 
                              "earmark_subject"  => "",
                              "earmark_exp_date" => $lang === "de" ? "Summe" : "Total",
                              "earmark_amount"   => getFormatterAmount($sum) ] ];
        }
    })
    ->process($_POST)            
    ->json();
}

rf1234

What you also need to do if you use field type "datatable" kind of "independently" is reloading those tables on "select" of the parent table. Otherwise you might have outdated data or even nothing in your tables.

Like this for example with multiple "datatable" fields:

var editorDts = ["sub_exec_cashflow[].id", "sub_earmark[].id", "sub_proof_schedule[].id"];
for (var i=0; i < editorDts.length; i++) {
    subTablesEditor.field(editorDts[i]).dt().ajax.reload();
}