Mjoin alias - Select lists not filtering on WHERE clause

peterbrowne

This question relates to a previous question at https://datatables.net/forums/discussion/65165/mjoin-alias-and-displaying-results#latest

I have 7 Mjoins which use alias names and have WHERE clauses.

The records from those Mjoins display fine in datatables, and are correctly filtered according to the WHERE clauses in those Mjoins.

There are 7 selects in the editor which are each populated by those Mjoins. However, those selects are not being filtered by the WHERE clauses, as all have identical options in the select, i.e. all records without filtering.

Any ideas? See code in the following comments.

peterbrowne

HTML:

<div id="unit_outcome_form">
<editor-field name="unit_outcome.unit_outcome"></editor-field>
<editor-field name="unit[].unit_pk"></editor-field>
<editor-field name="program_outcome[].program_outcome_pk"></editor-field>
<editor-field name="strand[].strand_pk"></editor-field>
<editor-field name="emergency_medicine[].discipline_outcome_pk"></editor-field>
<editor-field name="general_practice[].discipline_outcome_pk"></editor-field>
<editor-field name="internal_medicine[].discipline_outcome_pk"></editor-field>
<editor-field name="obstetrics_gynaecology[].discipline_outcome_pk"></editor-field>
<editor-field name="paediatrics[].discipline_outcome_pk"></editor-field>
<editor-field name="psychiatry[].discipline_outcome_pk"></editor-field>
<editor-field name="surgery[].discipline_outcome_pk"></editor-field>
<editor-field name="infection_immunity[].discipline_outcome_pk"></editor-field>
</div>

peterbrowne

Datatables example:

, {
     data: "emergency_medicine",
     render: "[, ].discipline_outcome"
 }

Editor select list example:

{
    label: "Discipline Outcomes - Emergency Medicine:",
    name: "emergency_medicine[].discipline_outcome_pk",
    type: "select",
    placeholder: 'No selection',
    placeholderDisabled: false,
    placeholderValue: 0,
    multiple: true
               }

peterbrowne

Server side code in unit_outcome_data.php:

->join(
Mjoin::inst( 'discipline_outcome' )
   ->name( 'emergency_medicine' )
   ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.unit_outcome_fk' )
->link( 'discipline_outcome.discipline_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.discipline_outcome_fk' )
>where('discipline_fk', '1' )
->fields(
Field::inst( 'discipline_outcome_pk' )
    ->options( Options::inst()
    ->table( 'discipline_outcome' )
    ->value( 'discipline_outcome_pk' )
    ->label( 'discipline_outcome' ) 
                    ),
Field::inst( 'discipline_outcome' )
            )
)

peterbrowne

And some images:

Selects with the same options:

https://paste.pics/1a045ad6e3c8ac7c4b18f9662a36e123

Records in Datatables showing the discipline_outcomes correctly filtered on the WHERE clauses from the 7 Mjoins:

https://paste.pics/479117198b9e1cfe139c31e4df8c9c27

peterbrowne

And the JSON with queries:

https://www.dropbox.com/s/jpm3uyxwsq4dg2m/json.txt?dl=0

And the relevant tables and data in dbfiddle:

https://www.db-fiddle.com/f/7B8pRJKmyX321khhzydeBN/0

peterbrowne

OK, got it working with:

->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'emergency_medicine' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.unit_outcome_fk' )
            ->link( 'discipline_outcome.discipline_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.discipline_outcome_fk' )
            ->fields(
                Field::inst( 'discipline_outcome_pk' )
                    ->options( Options::inst()
                        ->table( 'discipline_outcome' )
                        ->value( 'discipline_outcome_pk' )
                        ->label( 'discipline_outcome' ) 
                              ->where( function ($q) {
                            $q->where( 'discipline_fk', '1' );
                        }
                    )
                
            ),
            Field::inst( 'discipline_outcome' ) 
    )
    )

peterbrowne

Spoke to soon again... When updating I get:

Warning: Invalid argument supplied for foreach() in /var/www/html/curriculum_mapper/datatables/lib/Editor.php on line 974 Warning: Invalid argument supplied for foreach() in /var/www/html/curriculum_mapper/datatables/lib/Editor.php on line 810 Warning: Invalid argument supplied for foreach() in /var/www/html/curriculum_mapper/datatables/lib/Editor.php on line 999 {"data":[],"debug":[]}

I noticed that the unit_outcome name, while showing in datatables, is not showing in the editor field when editing. Not sure though if this is related to the above error.

The issue appears to be just with editing, creating a new record works OK...

allan

Hi,

Sorry for the delay in replying in your other thread on this topic, I've done so now, but I see you are ahead of that already having found the where() method for the Mjoin.

What version of the libraries are you using? On line 974 (Editor.php) in the 1.9.5 release, there isn't a foreach.

Thanks,
Allan

peterbrowne

Hi Allan

I was using 1.9.0. I have now updated to the latest version 1.9.5.

Still getting a system error has occurred (More information).

The error is:

Warning: Invalid argument supplied for foreach() in /var/www/html/curriculum_mapper/datatables/lib/Editor.php on line 1006

Warning: Invalid argument supplied for foreach() in /var/www/html/curriculum_mapper/datatables/lib/Editor.php on line 830

Warning: Invalid argument supplied for foreach() in /var/www/html/curriculum_mapper/datatables/lib/Editor.php on line 1031
{"data":[],"debug":[]}

The error still occurs only on update/edit, no problems creating a new record.

The unit_outcome name I note is missing on the editor model. The field is there, but it is empty. See https://paste.pics/77e88c2bfb6d5e884ff69dde67e12271

You can see in the image in that link that the unit_outcome name field is empty. You can also see just above the modal the record being edited (with the red expander) which shows the unit_outcome name. I don't know if that is a result of the error.

peterbrowne

OK, the error only happens using the inline edit button, not the main edit button at the top of the page. Also, using the main edit button, the unit_outcome name field is populated OK - it was empty using the inline edit button.

    <div id='content'>
        <div class='table_container'>
            <table id='datatables_table' class='display' style="width:100%">
                <thead>
                    <tr>
                        <th>Unit Outcome</th>
                        <th>Unit</th>
                        <th>Program Outcome</th>
                        <th>Strand</th>
                        <th>Discipline Outcomes - ED</th>
                        <th>Discipline Outcomes - GP</th>
                        <th>Discipline Outcomes - IM</th>
                        <th>Discipline Outcomes - O&G</th>
                        <th>Discipline Outcomes - Paed</th>
                        <th>Discipline Outcomes - Psych</th>
                        <th>Discipline Outcomes - Surg</th>
                        <th>Discipline Outcomes - I&I</th>
                        <th>Modified</th>
                        <th>Modified By</th>
                        <th>Edit</th>
                    </tr>
                </thead>
                <tbody></tbody>
            </table>
        </div>
        <div id="unit_outcome_form">
            <editor-field name="unit_outcome.unit_outcome"></editor-field>
            <editor-field name="unit[].unit_pk"></editor-field>
            <editor-field name="program_outcome[].program_outcome_pk"></editor-field>
            <editor-field name="strand[].strand_pk"></editor-field>
            <editor-field name="emergency_medicine[].discipline_outcome_pk"></editor-field>
            <editor-field name="general_practice[].discipline_outcome_pk"></editor-field>
            <editor-field name="internal_medicine[].discipline_outcome_pk"></editor-field>
            <editor-field name="obstetrics_gynaecology[].discipline_outcome_pk"></editor-field>
            <editor-field name="paediatrics[].discipline_outcome_pk"></editor-field>
            <editor-field name="psychiatry[].discipline_outcome_pk"></editor-field>
            <editor-field name="surgery[].discipline_outcome_pk"></editor-field>
            <editor-field name="infection_immunity[].discipline_outcome_pk"></editor-field>
    </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
            $( '#datatables_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/unit_outcome_data.php",
                table: "#datatables_table",
                template: '#unit_outcome_form',
                fields: [ {
                    label: "Unit Outcome:",
                    name: "unit_outcome.unit_outcome",
                    type: "ckeditor"
                }, {
                    label: "Units:",
                    name: "unit[].unit_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Program Outcomes:",
                    name: "program_outcome[].program_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Strand:",
                    name: "strand[].strand_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - Emergency Medicine:",
                    name: "emergency_medicine[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - General Practice:",
                    name: "general_practice[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - Internal Medicine:",
                    name: "internal_medicine[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - Obstretics & Gynaecology:",
                    name: "obstetrics_gynaecology[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - Paediatrics:",
                    name: "paediatrics[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - Psychiatry:",
                    name: "psychiatry[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - Surgery:",
                    name: "surgery[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - Infection & Immunity:",
                    name: "infection_immunity[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }]
            } );
    
        

            var table = $( '#datatables_table' ).DataTable( {
                responsive: true,
                ajax: "program_data/unit_outcome_data.php",
                dom: "Blfrtip",
                columns: [ {
                    data: "unit_outcome.unit_outcome",
                    width: '50%'
                }, {
                    data: "unit",
                    render: "[, ].unit_name"
                }, {
                    data: "program_outcome",
                    render: "[, ].program_outcome"
                }, {
                    data: "strand",
                    render: "[, ].strand_name"
                }, {
                    data: "emergency_medicine",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "general_practice",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "internal_medicine",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "obstetrics_gynaecology",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "paediatrics",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "psychiatry",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "surgery",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "infection_immunity",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "unit_outcome.modified"
                }, {
                    data: "unit_outcome.modified_by"
                }, {
                    data: null,
                    className: "center",
                    defaultContent: '<a href="" class="editor_edit">Edit</a>'
                }],
                select: {
                    style: 'os',
                    selector: 'td:first-child'
                },
                buttons: []
            } );

    if ( permission == 'create_edit' || permission == 'program_admin' || permission == 'super' ) {
                table.button().add( null, {
                    extend: 'create',
                    editor: editor
                } );
                table.button().add( null, {
                    extend: 'edit',
                    editor: editor
                } );
            }

            if ( permission == 'super' || permission == 'program_admin' ) {
                table.button().add( null, {
                    extend: 'remove',
                    editor: editor
                } );
            }
            
        } );
    </script>

peterbrowne

Well, it appears that the issue only occurs when using the inline edit button AND the responsive kicks in with the ability to expand to see the inline edit button.

With all columns visible (e.g. on a wide monitor) and no responsive needed to see the inline edit button, then inline edit works fine, no error.

peterbrowne

BTW, re 'inline' edit, I mean having an edit button on the final column of each row...

allan

Do you mean like what is shown in this example?

I think I'd need to be able to see a running example showing the error in this case if you can give me a link to the page?

Allan

peterbrowne

Sort of. That link you provided has responsive, but no edit button/link on the last column. It is that Edit button/link that when shown by responsive, causes the update error. On a naturally expanded page, without requiring responsive to show that Edit button in the last column, that Edit button/link in the last column works fine.

I have sent to you a message re access.

Peter

allan

Ah! I see what you mean now - thanks.

The issue is here:

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

More specifically the $( this ).closest( 'tr' ). That is not selecting the row you want to edit when your click is in the child row.

What you can do however, is pass in the row to the DataTable row selector which will return the row required:

let row = table.row( $( this ).closest( 'tr' ) ).index();

And then pass row into the editor.edit( row, .... ) call.

Allan

peterbrowne

Thanks Allan.

Using:

        // Edit record
            $( '#unit_outcomes_table' ).on( 'click', 'a.editor_edit', function ( e ) {
                e.preventDefault();
                let row = table.row( $( this ).closest( 'tr' ) ).index();
                //alert(row);
                editor.edit(row, {
                    title: 'Edit record',
                    buttons: 'Update'
                } );
            } );

the alert shows undefined and the editor fields show "multiple values".

allan

Sorry yes - that was a mistake (I was thinking of an implementation that hasn't been deployed yet). You need to determine if your link element is in the child row or not:

let row = table.row(this).index();
if (typeof row === 'undefined') {
  row = table.row( $( this ).closest( 'tr' ) ).index();
}

Thanks,
Allan

peterbrowne

Thanks Allan that works fine.

Peter