Mjoin - Not inserting or updating, but delete works

peterbrowne

I have multiple Mjoins in a script, they all work properly except for one, which doesn't do any inserts or updates into the lookup joined table.

If I manually add the data through phpMyAdmin into the lookup table, datatables and the editor field see this OK. I can also do a delete through datatables, and the record is deleted, including in the associated lookup table.

The problem Mjoin is Mjoin::inst( 'learning_event_type' ) The others work fine...

Client side:

<div class='table_container'>
            <table id='learning_event_table' class='display' style="width:100%">
                <thead>
                    <tr>
                        <th>Learning Event</th>
                        <th>Outline</th>
                        <th>Program Outcome</th>
                        <th>Unit Group</th>
                        <th>Presentations</th>
                        <th>Conditions</th>
                        <th>Type</th>
                        <th>Modified</th>
                        <th>Modified By</th>
                        <th>Edit</th>
                    </tr>
                </thead>
                <tbody>
                </tbody>
            </table>
        </div>
        <div id="learning_event_form">
            <editor-field name="learning_event.learning_event_name"></editor-field>
            <editor-field name="learning_event.outline"></editor-field>
            <editor-field name="program_outcome[].program_outcome_pk"></editor-field>
            <editor-field name="learning_event.unit_group_fk"></editor-field>
            <editor-field name="learning_event.learning_event_outcome"></editor-field>
            <editor-field name="presentation[].presentation_pk"></editor-field>
            <editor-field name="mcondition[].mcondition_pk"></editor-field>
            <editor-field name="learning_event_type[].learning_event_type_pk"></editor-field>
        </div>
    </div>

    <script type="text/javascript">
        var editor; // use a global for the submit and return data rendering in the examples

        $( document ).ready( function () {

            var program = $( '#program_field' ).val();
            var user = $( '#user_field' ).val();
            var permission = $( '#permission_field' ).val();

            $( '#main-menu' ).smartmenus();

            $( '#program_levels' ).change( function ( e ) {
                window.location.href = 'edit_' + this.value + ".php?program=" + program + '&user=' + user;
            } );

            // Edit record
            $( '#learning_event_table' ).on( 'click', 'a.editor_edit', function ( e ) {
                e.preventDefault();
                editor.edit( $( this ).closest( 'tr' ), {
                    title: 'Edit record',
                    buttons: 'Update'
                } );
            } );

            $.fn.dataTable.Editor.display.lightbox.conf.windowPadding = 50;

            var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/learning_event_data.php",
                table: "#learning_event_table",
                template: '#learning_event_form',
                fields: [ {
                    label: "Learning Event:",
                    name: "learning_event.learning_event_name"
                }, {
                    label: "Outline:",
                    name: "learning_event.outline",
                    type: "ckeditor"
                }, {
                    label: "Program Outcome:",
                    name: "program_outcome[].program_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Unit Group:",
                    name: "learning_event.unit_group_fk",
                    type: "select",
                    placeholder: "Select Unit Group..."
                }, {
                    label: "Presentations:",
                    name: "presentation[].presentation_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Conditions:",
                    name: "mcondition[].mcondition_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Type:",
                    name: "learning_event_type[].learning_event_type_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: false
                } ]
            } );


            var table = $( '#learning_event_table' ).DataTable( {
                responsive: true,
                "autoWidth": false,
                "lengthMenu": [
                    [ 5, 10, 25, 50, -1 ],
                    [ 5, 10, 25, 50, "All" ]
                ],
                columnDefs: [ {
                    targets: 1,
                    render: $.fn.dataTable.render.ellipsis( 150, true )
                } ],
                ajax: "program_data/learning_event_data.php",
                dom: "Blfrtip",
                columns: [ {
                    data: "learning_event.learning_event_name",
                    width: '15%'
                }, {
                    data: "learning_event.outline",
                    width: '15%'
                }, {
                    data: "program_outcome",
                    render: "[, ].program_outcome"
                }, {
                    data: "unit_group.unit_group"
                }, {
                    data: "presentation",
                    render: "[, ].presentation_name"
                }, {
                    data: "mcondition",
                    render: "[, ].mcondition_name"
                },  {
                    data: "learning_event_type",
                    render: "[, ].learning_event_type_name"
                }, {
                    data: "learning_event.modified"
                }, {
                    data: "learning_event.modified_by"
                }, {
                    data: null,
                    className: "center",
                    defaultContent: '<a href="" class="editor_edit">Edit</a>'
                } ],
                select: {
                    style: 'os',
                    selector: 'td:first-child'
                },
                buttons: []
            } );

peterbrowne

Server side:

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_cm_dopt, 'learning_event', 'learning_event_pk' )    
    ->field(
        Field::inst( 'learning_event.learning_event_name' ),
        Field::inst( 'learning_event.outline' ),
        Field::inst( 'learning_event.modified' ),
        Field::inst( 'learning_event.modified_by' )->setValue( $user),
        Field::inst( 'learning_event.unit_group_fk' )
            ->options( Options::inst()
                ->table( 'unit_group' )
                ->value( 'unit_group_pk' )
                ->label( 'unit_group' )
                ->where( function ($q) {
            $q->where( 'unit_group.type', 'learning_event' );
                })
            ),
        Field::inst( 'unit_group.unit_group' )
    )
     ->leftJoin( 'unit_group', 'unit_group.unit_group_pk', '=', 'learning_event.unit_group_fk' )
        ->join(
        Mjoin::inst( 'program_outcome' )
            ->link( 'learning_event.learning_event_pk', 'learning_event_program_outcome_lookup.learning_event_fk' )
            ->link( 'program_outcome.program_outcome_pk', 'learning_event_program_outcome_lookup.program_outcome_fk' )
            ->order( 'program_outcome.program_outcome asc' )
            ->fields(
                Field::inst( 'program_outcome_pk' )
                    ->options( Options::inst()
                        ->table( 'program_outcome' )
                        ->value( 'program_outcome_pk' )
        ->label( array('type', 'program_outcome') )
        ->render( function ( $row ) {
            return $row['type'] . ' - ' . $row['program_outcome'];
        } )
                        ->order( 'type' )
                    ),
                Field::inst( 'program_outcome' )
            )
    )
    ->join(
        Mjoin::inst( 'presentation' )
            ->link( 'learning_event.learning_event_pk', 'learning_event_presentation_lookup.learning_event_fk' )
            ->link( 'presentation.presentation_pk', 'learning_event_presentation_lookup.presentation_fk' )
            ->order( 'presentation.presentation_name asc' )
            ->fields(
                Field::inst( 'presentation_pk' )
                    ->options( Options::inst()
                        ->table( 'presentation' )
                        ->value( 'presentation_pk' )
                        ->label( 'presentation_name' )                
                    ),
                Field::inst( 'presentation_name' )
            )
    )
    ->join(
        Mjoin::inst( 'mcondition' )
            ->link( 'learning_event.learning_event_pk', 'learning_event_mcondition_lookup.learning_event_fk' )
            ->link( 'mcondition.mcondition_pk', 'learning_event_mcondition_lookup.mcondition_fk' )
           ->order( 'mcondition.mcondition_name asc' )
            ->fields(
                Field::inst( 'mcondition_pk' )
                    ->options( Options::inst()
                        ->table( 'mcondition' )
                        ->value( 'mcondition_pk' )
                        ->label( 'mcondition_name' )          
                    ),
                Field::inst( 'mcondition_name' )
            )
    )
    ->join(
        Mjoin::inst( 'learning_event_type' )
            ->link( 'learning_event.learning_event_pk', 'learning_event_type_lookup.learning_event_fk' )
            ->link( 'learning_event_type.learning_event_type_pk', 'learning_event_type_lookup.learning_event_type_fk' )
            ->fields(
                Field::inst( 'learning_event_type_pk' )
                    ->options( Options::inst()
                        ->table( 'learning_event_type' )
                        ->value( 'learning_event_type_pk' )
                        ->label( 'learning_event_type_name' )         
                    ),
                Field::inst( 'learning_event_type_name' )
            )
    )
    ->process($_POST)
    ->json();

peterbrowne

The response from XHR:

As you can see the learning_event_type data (at the bottom) is empty, even though the select in the editor was populated from the problematic Mjoin, and an option in that select list selected.

data    […]
0   {…}
DT_RowId    row_77
learning_event  {…}
learning_event_name Lecture - Adaptive immunity
outline 
modified    2021-07-29 16:25:19
modified_by 82563
unit_group_fk   5
unit_group  {…}
unit_group  Learning Events - OPTM5101
program_outcome […]
0   {…}
program_outcome_pk  1
program_outcome 1.1 Maintains develops and audits optometric knowledge, clinical expertise and skills
presentation    […]
0   {…}
presentation_pk 14
presentation_name   Acute angle closure glaucoma
mcondition  […]
0   {…}
mcondition_pk   1
mcondition_name Acute glaucoma
learning_event_type []

allan

Hi,

Thanks for all the details! Could you also show me the request parameters that Editor is sending to the server please? I just want to make sure that learning_event_type is being submitted.

Thanks,
Allan

peterbrowne

Like this?

action  edit
data[row_118][learning_event_type]  1
data[row_118][learning_event][learning_event_name]  Lecture+-+Glaucoma+risk+assessment+and+diagnosis
data[row_118][learning_event][outline]  
data[row_118][learning_event][unit_group_fk]    5
data[row_118][mcondition-many-count]    3
data[row_118][mcondition][0][mcondition_pk] 20
data[row_118][mcondition][1][mcondition_pk] 19
data[row_118][mcondition][2][mcondition_pk] 21
data[row_118][presentation-many-count]  0
data[row_118][program_outcome-many-count]   1
data[row_118][program_outcome][0][program_outcome_pk]   1

allan

Thank you. That shows that the problem is client-side - the:

data[row_118][learning_event_type]  1

is wrong for an mjoin submit.

I suspect the issue is with:

multiple: false

for that field. As an experiment, can you remove that please?

Allan

peterbrowne

Thanks Allan. Removing multiple:false fixed it. So I now have:

, {
                    label: "Type:",
                    name: "learning_event_type[].learning_event_type_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0
                }

But how can I make the select single select only?

allan

Add:

attr: {
  size: 1
}

To the field's object, and that will do the trick.

Allan

peterbrowne

Sorry Allan, but now the code I thought was working above (last post) doesn't (no update or insert for learning_event_type[].learning_event_type_pk) work unless I change it to include multiple: true

, {
                    label: "Type:",
                    name: "learning_event_type[].learning_event_type_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }

Adding the attr still has multiple select options...as I would expect.

, {
                    label: "Type:",
                    name: "learning_event_type[].learning_event_type_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true,
                    attr: {
                        size: 1
                        }
                }

Seems like a simple thing I have had working in the past...

allan

Can you show me what the client is submitting when you have:

                    multiple: true,
                    attr: {
                        size: 1
                        }

please?

Thanks,
Allan

peterbrowne

Two options selected in the learning_event_type multiple select list with

, {
                    label: "Type:",
                    name: "learning_event_type[].learning_event_type_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true,
                    attr: {
                        size: 1
                        }
                }

Result is the two options added to the database:

action  edit
data[row_161][learning_event_type-many-count]   2
data[row_161][learning_event_type][0][learning_event_type_pk]   4
data[row_161][learning_event_type][1][learning_event_type_pk]   5
data[row_161][learning_event][learning_event_name]  +Workshop+-+Spectacle+frames+and+effective+diameter+
data[row_161][learning_event][outline]  <ul><li>Materials<ul><li>metal/plastic+(steel,+titanium,+coated,+alloy)</li><li>cell+acetate,+nitrate,+optyl,+SPX</li><li>rimless</li><li>supra</li><li>allergies</li></ul></li><li>Components<ul><li>front+(bevel)</li><li>sides+(hockey-end+etc.)</li><li>screws</li><li>joints</li><li>nosepads</li><li>supra+nylon</li><li>rimless+inserts</li><li>sleeves+(sides)</li><li>markings</li></ul></li><li>Frame+and+facial+gauges</li><li>Measurements<ul><li>facial+v.+frame</li><li>box+v.+datum</li><li>include:<ul><li>eye-size,+bridge+parameters,+length+to+bend,+angle+of+bend,+pantoscopic+angle,+temple+width,+frontal+angle,+dihedral+angle,+PD+(horizontal,+vertical,+monocular)+etc.</li></ul></li></ul></li></ul>
data[row_161][learning_event][unit_group_fk]    6
data[row_161][mcondition-many-count]    0
data[row_161][presentation-many-count]  0
data[row_161][program_outcome-many-count]   1
data[row_161][program_outcome][0][program_outcome_pk]   1

allan

Apologies - the size attribute actually only effects the display height of the element (and can be overridden by CSS).

Digging through it, at this time there isn't a way to show a select with a dropdown (i.e. not multiple) for use with an Mjoin - which is specifically designed to expect multiple options.

Would a valid option be to add a server-side validator to ensure that [only one option is selected](https://editor.datatables.net/manual/php/validation#One-to-many-(Mjoin)?

Allan

peterbrowne

Thanks Allan for the clarification. It's not really an issue having a multiple select, just a preference for having a single select option. I can live with the multiple.

Thanks for your help!

Peter