Mjoin - Not inserting or updating, but delete works

Mjoin - Not inserting or updating, but delete works

peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
edited July 2021 in Editor

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: []
            } );

This question has an accepted answers - jump to answer

Answers

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    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();
    
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited July 2021

    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 []
    
  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    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
    
  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    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?

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    Add:

    attr: {
      size: 1
    }
    

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

    Allan

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited August 2021

    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...

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

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

                        multiple: true,
                        attr: {
                            size: 1
                            }
    

    please?

    Thanks,
    Allan

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    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
    
  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    Answer ✓

    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

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited August 2021

    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

Sign In or Register to comment.