Dependant Select problems

peterbrowne

I have a dependant select to populate the select learning_event.week_fk

            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: "Week:",
                    name: "learning_event.week_fk",
                    type: "select",
                    placeholder: "Select Week..."
                }, {
                    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: true,
                        attr: {
                            size: 1
                        }
                } ]
            } );
            
            
            
  editor.dependent('learning_event.week_fk', function(val, data, callback) {
        editor.field('learning_event.unit_group_fk').input().on('change', function(e, d) {
            //check if user or editor initiated update
            if (d && d.editorSet) return;
            $.ajax({
                url: 'program_data/get_weeks.php',
                data: {
                    "unit": editor.get('learning_event.unit_group_fk')
                },
                type: 'POST',
                dataType: 'JSON',
                success: function(data) {
                    callback(data);
                }
            });
        });
    });


            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",
                }, {
                    data: "learning_event.outline",
                }, {
                    data: "program_outcome",
                    render: "[, ].program_outcome"
                }, {
                    data: "unit_group.unit_group"
                }, {
                    data: "week.week"
                }, {
                    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: []
            } );

more in next post...

peterbrowne

The dependant is not getting populated using this in the get_weeks.php:

$unit = $_POST['unit'];

$data = array();

$query = "SELECT * FROM week WHERE unit_group_fk = '$unit'";
$result = $connection->query( $query );

while ($row = mysqli_fetch_array($result)) {
    $data[] = array("label"=>$row['week'], "value"=>$row['week_pk']);
}

$temp = array('week[].week_pk'=>$data);
$json = array('options'=>$temp);
echo json_encode($json);

hard coding the value for $unit makes no difference. The above script reurns:

{"options":{"week[].week_pk":[{"label":"Week 1","value":"1"},{"label":"Week 2","value":"2"},{"label":"Week 3","value":"3"},{"label":"Week 4","value":"4"},{"label":"Week 5","value":"5"},{"label":"Week 6","value":"6"},{"label":"Week 7","value":"7"},{"label":"Week 8","value":"8"},{"label":"Week 9","value":"9"},{"label":"Week 10","value":"10"},{"label":"Week 11","value":"11"},{"label":"Week 12","value":"12"}]}}

peterbrowne

.

allan

Hi,

Your options object contains the key week[].week_pk, but there is no field in the Editor list of fields which matches that. Should it actually be learning_event.week_fk?

Allan

peterbrowne

I'm not clear, sorry Allan. Can you include more of the code context so I can pinpoint?

There is in the Editor list:

, {
                  label: "Week:",
                  name: "learning_event.week_fk",
                  type: "select",
                  placeholder: "Select Week..."
              },

peterbrowne

The week[].week_pk is coming from the script get_weeks.php as shown above, called in the dependant part for the editor.

This should populate the dependant select in the editor with related records from table week:

, {
                  label: "Week:",
                  name: "learning_event.week_fk",
                  type: "select",
                  placeholder: "Select Week..."
              },

What am I doing wrong?

allan

In the JSON return you use {"options":{"week[].week_pk". But there is no field with that name. If you want to populate the list of options for the learning_event.week_fk field then update your PHP to be:

$temp = array('learning_event.week_fk'=>$data);

Editor (client-side) will find a field with that name and then populate its options with that data.

Allan

peterbrowne

Thanks Allan.
I now have the dependant getting the options OK. However the create and update is not happening, it just displays the progress indicator and doesn't do the insert or update.

This is the client page:

<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>Week</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.week_fk"></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: "Week:",
                    name: "learning_event.week_fk",
                    type: "select",
                    placeholder: "Select Week..."
                }, {
                    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: true,
                        attr: {
                            size: 1
                        }
                } ]
            } );
            
            
            
  editor.dependent('learning_event.week_fk', function(val, data, callback) {
        editor.field('learning_event.unit_group_fk').input().on('change', function(e, d) {
            //check if user or editor initiated update
            if (d && d.editorSet) return;
            $.ajax({
                url: 'program_data/get_weeks.php',
                data: {
                    "unit": editor.get('learning_event.unit_group_fk')
                },
                type: 'POST',
                dataType: 'JSON',
                success: function(data) {
                    callback(data);
                }
            });
        });
    });



            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",
                }, {
                    data: "learning_event.outline",
                }, {
                    data: "program_outcome",
                    render: "[, ].program_outcome"
                }, {
                    data: "unit_group.unit_group"
                }, {
                    data: "week.week"
                }, {
                    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

and the server side script:

include( "../../../datatables/lib/DataTables.php" );
// Alias Editor classes so they are easy to use
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' ), 
        Field::inst( 'learning_event.week_fk' )
            ->options( Options::inst()
                ->table( 'week' )
                ->value( 'week_pk' )
                ->label( 'week' )
            ),
        Field::inst( 'week.week' )  
    )
     ->leftJoin( 'unit_group', 'unit_group.unit_group_pk', '=', 'learning_event.unit_group_fk' )
     ->leftJoin( 'week', 'week.week_pk', '=', 'learning_event.week_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' )
           ->order( 'learning_event_type.learning_event_type_name asc' )
            ->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

Note that if I comment out the dependant section in the client script, then the update and create works fine. The problem then s of course that the weeks select just contains all records from table week.

kthorngren

However the create and update is not happening, it just displays the progress indicator and doesn't do the insert or update.

Have you checked for errors in the browser's console?

Have you checked the XHR request and response, assuming that it happens before any errors.

Kevin

peterbrowne

I have checked Network - XHR and it doesn't show any response or errors as the action isn't being performed.

kthorngren

So is the XHR request sent? If so then look at your server logs for errors.

Kevin

peterbrowne

This is the editor showing the progress indicator in the top corner. This doesn't complete when dependant part of the script is active. It seems to hang. Commenting out that dependant script then the request completes.

peterbrowne

It could be to do with the callback in the dependant script not being fired, there is no XHR.

peterbrowne

This is the result from get_weeks.php to populate the dependant select learning_event.week_fk

{"options":{"learning_event.week_fk":[{"label":"Week 1 - OPTM4101","value":"1"},{"label":"Week 2 - OPTM4101","value":"2"},{"label":"Week 3 - OPTM4101","value":"3"},{"label":"Week 4 - OPTM4101","value":"4"},{"label":"Week 5 - OPTM4101","value":"5"},{"label":"Week 6 - OPTM4101","value":"6"},{"label":"Week 7 - OPTM4101","value":"7"},{"label":"Week 8 - OPTM4101","value":"8"},{"label":"Week 9 - OPTM4101","value":"9"},{"label":"Week 10 - OPTM4101","value":"10"},{"label":"Week 11 - OPTM4101","value":"11"},{"label":"Week 12 - OPTM4101","value":"12"}]}}

To reiterate, the dependant gets populated fine, but with the dependant part of the client code below, editor does not fire the update or create, it hangs. If I comment out the code below, the update or create works.

  editor.dependent('learning_event.week_fk', function(val, data, callback) {
        editor.field('learning_event.unit_group_fk').input().on('change', function(e, d) {
            //check if user or editor initiated update
            if (d && d.editorSet) return;
            $.ajax({
                url: 'program_data/get_weeks.php',
                data: {
                    "unit": editor.get('learning_event.unit_group_fk')
                },
                type: 'POST',
                dataType: 'JSON',
                success: function(data) {
                    callback(data);
                }
            });
        });
    });

allan

I think I see the problem - you are adding a change event listener inside dependent() and it is adding a new listener everytime dependent is triggered. What complicates this is that the callback doesn't execute until the unit_group_fk field's value changes after the dependent action.

So if dependent were to trigger, but the unit_group_fk field doesn't change value, then the callback will never happen, leaving error in an undefined state. That is what I think you are seeing here.

We need to step back a little to see what your goal is here. I suspect this approach isn't quite the right way to go about it. Could you clarify what you want the interaction between week_fk and unit_group_fk to be please?

Thanks,
Allan

peterbrowne

Thanks Allan.

I need to populate the select for learning_event.week_fk based on the value set in the select for learning_event.unit_group_fk.

This should be for create and update.

For create, the user selects a value from learning_event.unit_group_fk, which then populates learning_event.week_fk.

For update, it would be ideal if learning_event.unit_group_fk was automatically populated according to the value set in learning_event.unit_group_fk. If learning_event.unit_group_fk is not set, then the user selects an option from that, and learning_event.week_fk is populated based on that selection.

I did notice significant lag when populating the dependant, so your explanation of multiple listeners may be correct.

colin

This example may help - it's disabling or enabling a field depending on whether it's an edit or a create. This is similar to your scenario, so you could set the value if it's an edit.

Colin

peterbrowne

Thanks Colin, but that doesn't help with my dependant select issue...

colin

It was meant to be in addition to Allan's statement, for how you want different behaviour for the create and the update - it was just some code to give you an idea of how that could be done.

Colin

allan

Thanks for the clarification.

I need to populate the select for learning_event.week_fk based on the value set in the select for learning_event.unit_group_fk

You want to use:

editor.dependent('learning_event.unit_group_fk', function(val, data, callback) {
  // ...
});

In that case. The first parameter of dependent() should be the field that the condition is dependent upon.

You could could also have Editor make the Ajax call for you:

editor.dependent('learning_event.unit_group_fk', 'program_data/get_weeks.php');

Then in get_weeks.php you would use $_POST['values']['learning_event']['unit_group_fk'] to get the value for the unit group and base the return on that.

To set the options for learning_event.week_fk you then need to do:

echo json_encode( [
  "options" => [
    "learning_event.week_fk" => // ... options
  ]
]);

Allan

peterbrowne

Thanks Allan, very helpful.

I tried:

editor.dependent('learning_event.unit_group_fk', 'program_data/get_weeks_test.php');

with:

$unit = $_POST['values']['learning_event']['unit_group_fk'];

$data = array();

$query = "SELECT * FROM week WHERE unit_group_fk = '$unit'";
$result = $connection->query( $query );

while ($row = mysqli_fetch_array($result)) {
    $data[] = array("label"=>$row['week_full_name'], "value"=>$row['week_pk']);
}

$temp = array('learning_event.week_fk'=>$data);
$json = array('options'=>$temp);
echo json_encode($json);

But it's not sending the value, at least not the learning_event.unit_group_fk value. Using the above and hardcoding a value in the query for $unit in get_weeks.php works though.

Using the following works, though I need to have the dependant select automatically populate based on the value of learning_event.unit_group_fk (on change and otherwise...):

            //editor.dependent('learning_event.unit_group_fk', 'program_data/get_weeks_test.php');
            
  editor.dependent('learning_event.unit_group_fk', function(val, data, callback) {
        editor.field('learning_event.unit_group_fk').input().on('change', function(e, d) {
            //check if user or editor initiated update
            if (d && d.editorSet) return;
            $.ajax({
                url: 'program_data/get_weeks_test.php',
                data: {
                    "unit": editor.get('learning_event.unit_group_fk')
                },
                type: 'POST',
                dataType: 'JSON',
                success: function(data) {
                    callback(data);
                }
            });
        });
    });

peterbrowne

With editor.dependent('learning_event.unit_group_fk', 'program_data/get_weeks.php'); and using:

$unit = $_POST['values']['learning_event']['unit_group_fk'];
 
$data = array();
 
$query = "SELECT * FROM week WHERE unit_group_fk = '$unit'";
$result = $connection->query( $query );
 
while ($row = mysqli_fetch_array($result)) {
    $data[] = array("label"=>$row['week_full_name'], "value"=>$row['week_pk']);
}
 
$temp = array('learning_event.week_fk'=>$data);
$json = array('options'=>$temp);
echo json_encode($json);

the following empty array is returned:

{"options":{"learning_event.week_fk":[]}}

Hardcoding the value for $unit works fine...

peterbrowne

Any ideas why the following returns an empty array?

editor.dependent('learning_event.unit_group_fk', 'program_data/get_weeks.php');

allan

It suggests to me that the query isn't matching anything. If you

print_r($_POST);

in your script, what does the return from the server show? It will be invalid JSON, but that doesn't matter, it is the return that is of interest.

Allan

peterbrowne

Sorry Allan, where do I put print_r($_POST);? In get_weeks.php? If so, then how and where is that print_r going to show when it is run from edit_learning_event.php?

allan

In get_weeks.php?

Yes. Basically anywhere in there. Then when the dependent action is triggered an Ajax request is made to get_weeks.php and the response will be in there. As I say, it will be invalid JSON, but for a quick debug test, that doesn't matter.

This tech note explains how you can see the response from the server.

Allan

peterbrowne

Thanks Allan. This is what I get:

{"options":{"learning_event.week_fk":[]}}Array
(
    [values] => Array
        (
            [learning_event.learning_event_name] => test only
            [learning_event.outline] => 
            [program_outcome[] => 0
            [learning_event.unit_group_fk] => 3
            [learning_event.week_fk] => 
            [learning_event.low_vision] => 0
            [learning_event.contact_lenses] => 0
            [learning_event.paediatrics] => 0
            [learning_event.binocular_vision] => 0
            [learning_event.vision_therapy] => 0
            [learning_event.therapeutics] => 0
            [learning_event.research] => 0
            [learning_event.clinical_practice] => 0
            [learning_event.optics] => 0
            [learning_event.broad_vision_sciences] => 0
            [learning_event.other] => 0
            [presentation[] => 0
            [mcondition[] => 0
            [learning_event_type[] => 0
        )

)

peterbrowne

The above was produced with editor.dependent('learning_event.unit_group_fk', 'program_data/get_weeks_test.php'); in edit_learning_event.php

and get_weeks.php:

//$unit = $_POST['unit'];

$unit = $_POST['values']['learning_event']['unit_group_fk'];

$data = array();

$query = "SELECT * FROM week WHERE unit_group_fk = '$unit'";
$result = $connection->query( $query );

while ($row = mysqli_fetch_array($result)) {
    $data[] = array("label"=>$row['week_full_name'], "value"=>$row['week_pk']);
}

$temp = array('learning_event.week_fk'=>$data);
$json = array('options'=>$temp);
echo json_encode($json);

print_r($_POST);

peterbrowne

Without print_r($_POST);

{"options":{"learning_event.week_fk":[]}}