Mjoin with Dependent Select

peterbrowne

This is a rephrased question from here

I have a select list populated via an Mjoin (see code in following comment). The select is:

, {
    label: "Learning Events:",
    name: "learning_event[].learning_event_pk",
    type: "select",
    placeholder: "Select Learning Event...",
    placeholderDisabled: false,
    placeholderValue: 0,
    multiple: true
                }

This select has data that will need to be created, updated and deleted as per Editor functions and the Mjoin.

Instead of showing all records for table learning_event in the select above, I want to show only those records that relate to a selected option in another select unit_group.unit_group_pk.

, {
                label: "Unit Group:",
                name: "unit_group.unit_group_pk",
                type: "select",
                placeholder: "Select Unit Group..."
            }

The unit_group.unit_group_pk select is populated by a separate AJAX function:

var unit_groups = [];
           
        $.getJSON("program_data/get_unit_groups.php", function(data) {
            var option = {};
            $.each(data, function(i,e) {
                option.label = e.text;
                option.value = e.id;
                unit_groups.push(option);
                option = {};
            });
        }).done(function(){editor.field('unit_group.unit_group_pk').update(unit_groups);
                          });

I have tried using the following editor.dependant code, but it causes problems with what seems to be an infinite loop causing refresh issues in the learning_event[].learning_event_pk select and it won't do a create or an update.

editor.dependent('learning_event[].learning_event_pk', 'program_data/get_learning_events.php');

I assume I need the Mjoin to facilitate the DB transactions.

Any ideas on how this can be achieved?

peterbrowne

This is the server-side code:

Editor::inst( $db_cm_mcp, 'program_outcome', 'program_outcome_pk' )->fields(
    Field::inst( 'program_outcome.program_outcome' ),
    Field::inst( 'program_outcome.type' ),
    Field::inst( 'program_outcome.modified' ),
    Field::inst( 'program_outcome.modified_by' )->setValue( $user ),
    Field::inst( 'program_outcome.program_outcome_group_fk' )
            ->options( Options::inst()
                ->table( 'program_outcome_group' )
                ->value( 'program_outcome_group_pk' )
                ->label( 'program_outcome_group' )
            ),
        Field::inst( 'program_outcome_group.program_outcome_group' )
    )
     ->leftJoin( 'program_outcome_group', 'program_outcome_group.program_outcome_group_pk', '=', 'program_outcome.program_outcome_group_fk' )
     ->join(
        Mjoin::inst( 'learning_event' )
            ->link( 'program_outcome.program_outcome_pk', 'learning_event_program_outcome_lookup.program_outcome_fk' )
            ->link( 'learning_event.learning_event_pk', 'learning_event_program_outcome_lookup.learning_event_fk' )       
            ->fields(
                Field::inst( 'learning_event_pk' )
                    ->options( Options::inst()
                        ->table( 'learning_event' )
                        ->value( 'learning_event_pk' )
                        ->label( 'learning_event_name') 
                    ),
                Field::inst( 'learning_event_name' )
            )
    )
->process( $_POST )
->json();

peterbrowne

This is the full client side code:

var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/program_outcome_data_test.php",
                table: "#program_outcome_table",
                template: '#program_outcome_form',
                fields: [ {
                    label: "Program Outcome:",
                    name: "program_outcome.program_outcome"
                }, {
                    label: "Type:",
                    type:  "select",
                    placeholder: 'Select a Type...',
                    name:  "program_outcome.type",
                    options: [
                        "PAC Competencies",
                        "AIMS Communication",
                        "UWA"
                    ]
                }, {
                    label: "Program Outcome Group:",
                    name: "program_outcome.program_outcome_group_fk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: false
                }, {
                    label: "Unit Group:",
                    name: "unit_group.unit_group_pk",
                    type: "select",
                    placeholder: "Select Unit Group..."
                }, {
                    label: "Learning Events:",
                    name: "learning_event[].learning_event_pk",
                    type: "select",
                    placeholder: "Select Learning Event...",
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }  ]
            } );
            
            
            
        var unit_groups = [];   
              
        $.getJSON("program_data/get_unit_groups.php", function(data) {
            var option = {};
            $.each(data, function(i,e) {
                option.label = e.text;
                option.value = e.id;
                unit_groups.push(option);
                option = {};
            });
        }).done(function(){editor.field('unit_group.unit_group_pk').update(unit_groups);
                          });
         
        //editor.dependent('learning_event[].learning_event_pk', 'program_data/get_learning_events.php');


            var table = $( '#program_outcome_table' ).DataTable( {
                responsive: true,
                columnDefs: [ {
                    targets: 0,
                    render: $.fn.dataTable.render.ellipsis( 100, true )
                } ],
                ajax: "program_data/program_outcome_data_test.php",
                dom: "Blfrtip",
                columns: [ {
                    data: "program_outcome.program_outcome"
                }, {
                    data: "program_outcome.type"
                }, {
                    data: "program_outcome_group.program_outcome_group"
                }, {
                    data: "program_outcome.modified"
                }, {
                    data: "program_outcome.modified_by"
                } ],
                select: {
                    style: 'os',
                    selector: 'td:first-child'
                },
                buttons: []
            } );

peterbrowne

is there any more information I can add to help get an answer?

peterbrowne

The image below shows the two select lists. So, I just want to have the top select unit_group.unit_group_pk populate (or repopulate or filter) the bottom select learning_event[].learning_event_pk.

Given that the data in the two selects are related of course, which they are, what editor functionality can I use to achieve this??

rf1234

Given that the data in the two selects are related of course, which they are, what editor functionality can I use to achieve this??

You can use "dependent" and "update" if you are using "select" fields. Here is an example from my own coding. "planning_level_1" and "planning_level_2" are "select" fields which are interdependent. Same applies to "planning_level_3" that is dependent on both of the above.

This code is a bit longer than it needs to be because I added some code to optimize the number of field settings due to issues with Editor when to many field settings / updates occur.

Here are the Editor field definitions:

fields: [{
        label: lang === 'de' ? 'Planungsebene 1:' : 'Planning level 1:',
        name: "planning_level_1",
        className: "pl-fields",
        type: "select",
        options: planningLevelOptions
    }, {
        label: lang === 'de' ? 'Planungsebene 2:' : 'Planning level 2:',
        name: "planning_level_2",
        className: "pl-fields",
        type: "select",
        options: planningLevelOptions
    }, {
        label: lang === 'de' ? 'Planungsebene 3:' : 'Planning level 3:',
        name: "planning_level_3",
        className: "pl-fields",
        type: "select",
        options: planningLevelOptions
    }

"planningLevelOptions" is a global variable that contains the default planning levels as an array of objects (label / value pairs).

Depending on the planning level you choose in level 1 you can only choose the remaining options in level 2. Same applies for level 3. I use "slice()" to make shallow copies of the initial options and "splice()" to delete options that have been chosen on the previous planning level(s) already. Then I use "update()" to update the respective "select" options.

editor
    .dependent(['planning_level_1', 'planning_level_2'], function (val, data, callback) {    
        //make sure we do as few field settings as possible due to Editor issue
        //when too many field updates!
        var eraseHidePl_2 = true;
        var eraseHidePl_3 = true;
        var classPlFields = false;
        
        if ( this.val('planning_level_1') > 0 ) {
            eraseHidePl_2 = false;
            classPlFields = true;
            var plo = planningLevelOptions.slice(); //shallow copy of array of objects
            var i;
            for (i=0; i < plo.length; i++) {
                if ( plo[i].value === this.val('planning_level_1') ) {
                    plo.splice(i, 1);
                    break;
                }
            }
            if ( plo != ploSavedLevel_1 ) {
                this.field('planning_level_2').update(plo);
                ploSavedLevel_1 = plo.slice();
            }
            this.show(['planning_level_2']);
            if ( this.val('planning_level_2') > 0 ) {
                eraseHidePl_3 = false;
                for (i=0; i < plo.length; i++) {
                    if ( plo[i].value === this.val('planning_level_2') ) {
                        plo.splice(i, 1);
                        break;
                    }
                }
                if ( plo != ploSavedLevel_2 ) {
                    this.field('planning_level_3').update(plo);
                    ploSavedLevel_2 = plo.slice();
                }
                this.show(['planning_level_3']);  
            }
        }
        if ( classPlFields ) {
            $('.pl-fields').addClass("bg-info");
        } else {
            $('.pl-fields').removeClass("bg-info");
        }        
        //avoid too many field updates due to Editor bug
        if ( eraseHidePl_2 ) {
            if (this.val('planning_level_2') != 0) {
                this.set({'planning_level_2': 0});
            }
            this.hide(['planning_level_2']);   
        }
        if ( eraseHidePl_3 ) {
            if (this.val('planning_level_3') != 0) {
                this.set({'planning_level_3': 0});
            }
            this.hide(['planning_level_3']);   
        }
        callback({});
    });

peterbrowne

Thanks. But I I was actually hoping for a simpler Editor functionality. I will give it a shot if inherent Editor functions don't exist.

Allan?

allan

Hi,

Sorry for the delay. Traveling this week - back to normal on Tuesday! Have you read over this blog post on the topic?

I'll have a more detailed look at your code when I'm back

Allan

rf1234

Yep, it's all about the same thing: You get options from somewhere (either ajax in your blog post or otherwise like in my example) and then you update the options "dependent" on your individual use case's conditions.

I think that is already rather simple. Depending on how complex your use case is and what you are doing on the server (in my case: nothing is done on the server) you have more or hardly any Javasript code.

Quoting your blog post, Allan:

The reference documentation for dependent() can be a little daunting at first, since it havs so many options available

That's the understatement of the year

peterbrowne

Thanks @rf1234 and @allan. I have looked at the code provided from both suggestions, but just don't quite understand how to apply to my Editor code.

allan

I guess the first thing to check is that the blog post does show the kind of thing you want?

If so, at which part do you get stuck with how to apply it to your own page?

Allan

rf1234

Instead of showing all records for table learning_event in the select above, I want to show only those records that relate to a selected option in another select unit_group.unit_group_pk.

I think with "all records" you mean "all options" to choose from.

If that is true you have pretty much exactly my use case. So you could use my code.

You seem to be loading the options from the server though: Hence loading the updated options of "learning event" from the server "dependent" on a change of selection of "unit group" using Allan's blog post example is probably the better option for you.

peterbrowne

@rf1234 Possibly, although I did try that before and it had issues with endless loops and not doing an update/create etc.

I will give the blog answer another go...and post back.

peterbrowne

I'm still not sure how to implement the blog code in my solution.

Is in that solution:

editor.dependent( 'continent', '/api/countries' );

referring to the following script?

include_once( $_SERVER['DOCUMENT_ROOT']."/php/DataTables.php" );
 
$countries = $db
    ->select( 'country', ['id as value', 'name as label'], ['continent' => $_REQUEST['values']['continent']] )
    ->fetchAll();
 
echo json_encode( [
    'options' => [
        'country' => $countries
    ]
] );

I assume 'id as value', 'name as label' need to be changed to the actual column names of the 'country' table.

Also in my case, I would still need to populate the parent select separately, as there is no lookup table for that relationship.

, {
                    label: "Unit Group:",
                    name: "unit_group.unit_group_pk",
                    type: "select",
                    placeholder: "Select Unit Group..."
                },

So I have been using:

var unit_groups = [];   
              
        $.getJSON("program_data/get_unit_groups.php", function(data) {
            var option = {};
            $.each(data, function(i,e) {
                option.label = e.text;
                option.value = e.id;
                unit_groups.push(option);
                option = {};
            });
        }).done(function(){editor.field('unit_group.unit_group_pk').update(unit_groups);
                          });

rf1234

I have never used the blog post example. I always do it differently using my own proprietary ajax calls to retrieve the options and then use "update()" client side to update the options.

In order to make "update()" work without further modifications you need to return "label" / "value" pairs from the server. Like in this simple example:

$dbh->query('SELECT DISTINCT value AS label, id AS value 
               FROM category_value_range 
              WHERE ctr_category_id = :categoryId 
           ORDER BY 1 ASC');
$dbh->bind(':categoryId', $categoryId); 

return $dbh->resultsetAssoc();     

"value AS label, id AS value" looks confusing but it works because the label is usually some kind of name which I called "value" in this case and the value is mostly an id.