Dependant select list - How to add first (parent) select list in data file?

peterbrowne

I have a dependent select list. I know how to add the first (parent) select list that feeds the child dependent select when there is a left join on the first select. But how to add the parent select list to the server data file when there is no join, just a standalone list.

So, the parent select list just gets its values from a table. Selecting a value from that list populates the dependent, discipline_outcome[].discipline_outcome_pk

Basically, I just need to know how to add the parent select, discipline[].discipline_pk to the server file, unit_outcome_data. The discipline table just have the table fields:

discipline_pk
discipline

edit_unit_outcome.php

var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/unit_outcome_data.php",
                table: "#unit_outcome_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: "Discipline:",
                    name: "discipline[].discipline_pk",
                    type: "select",
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcome:",
                    name: "discipline_outcome[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }]
            } );
            
            
            
            
            
            
                editor.dependent('discipline_outcome[].discipline_outcome_pk', function(val, data, callback) {
        editor.field('discipline[].discipline_pk').input().on('change', function(e, d) {
            //check if user or editor initiated update
            if (d && d.editorSet) return;
            $.ajax({
                url: 'program_data/get_discipline_outcomes.php',
                data: {
                    "unit": editor.get('discipline[].discipline_pk')
                },
                type: 'POST',
                dataType: 'JSON',
                success: function(data) {
                    callback(data);
                }
            });
        });
    });
            
            
            


            var table = $( '#unit_outcome_table' ).DataTable( {
                responsive: true,
                "autoWidth": false,
                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: "discipline",
                    render: "[, ].discipline"
                }, {
                    data: "discipline_outcome",
                    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: []
            } );

unit_outcome_data.php

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_md, 'unit_outcome', 'unit_outcome_pk' )    
    ->field(
        Field::inst( 'unit_outcome.unit_outcome' ),
        Field::inst( 'unit_outcome.modified' ),
        Field::inst( 'unit_outcome.modified_by' )->setValue( $user )
    )
     ->join(
        Mjoin::inst( 'unit' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_unit_outcome_lookup.unit_outcome_fk' )
            ->link( 'unit.unit_pk', 'unit_unit_outcome_lookup.unit_fk' )
            ->order( 'unit.unit_name asc' )
            ->fields(
                Field::inst( 'unit_pk' )
                    ->options( Options::inst()
                        ->table( 'unit' )
                        ->value( 'unit_pk' )
                        ->label( 'unit_name' )                
                    ),
                Field::inst( 'unit_name' )
            )
    )
     ->join(
        Mjoin::inst( 'program_outcome' )
            ->link( 'unit_outcome.unit_outcome_pk', 'program_outcome_unit_outcome_lookup.unit_outcome_fk' )
            ->link( 'program_outcome.program_outcome_pk', 'program_outcome_unit_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( 'program_outcome' )
                        ->render( function ( $row ) {
            return strip_tags($row['program_outcome']);
        } )
                        ->order( 'program_outcome' )
                    ),
                Field::inst( 'program_outcome' )
            )
    )
    ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
            ->link( 'discipline_outcome.discipline_outcome_pk', 'unit_outcome_discipline_outcome_lookup.discipline_outcome_fk' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->fields(
                Field::inst( 'discipline_outcome_pk' )
                    ->options( Options::inst()
                        ->table( 'discipline_outcome' )
                        ->value( 'discipline_outcome_pk' )
                        ->label( 'discipline_outcome' )
                        ->order( 'discipline_outcome' )
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    ->process($_POST)
    ->json();

get_discipline_outcomes.php

$discipline = $_POST['discipline'];

$data = array();

$query = "SELECT * FROM discipline, discipline_outcome, discipline_discipline_outcome_lookup WHERE discipline_discipline_outcome_lookup.discipline_outcome_fk = discipline_outcome.discipline_outcome_pk AND discipline_discipline_outcome_lookup.discipline_fk = discipline.discipline_pk AND discipline.discipline_pk = '$discipline'";
$result = $connection->query( $query );

while ($row = mysqli_fetch_array($result)) {
    $data[] = array("label"=>substr(strip_tags($row['discipline_outcome']), 0, 100) . '...', "value"=>$row['discipline_outcome_pk']);
}

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

allan

To populate a select list, you need the server to respond with a label/value array inside an options parameter that matches the same name - e.g.:

"options": {
  "discipline[].discipline_pk": [
    // label / value
  ]
}

I don't see anything in the PHP that will do that though - there isn't an Mjoin onto discipline_outcome - should your client-side field actually be called discipline_outcome[].discipline_pk?

Allan

peterbrowne

I though this might be a relatively simple thing...?

peterbrowne

Hi Allan

I have now updated the server file for the data, see code for unit_outcome_data.php below.

I have a lookup table discipline_discipline_outcome_lookup which contains:

discipline_outcome_fk
discipline_fk

Which correspond to the tables primary keys:

discipline_outcome.discipline_outcome_pk
discipline.discipline_pk

I am now getting an error:

DataTables warning: table id=unit_outcome_table - Join was performed on the field 'discipline_outcome_pk' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance.

```
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_md, 'unit_outcome', 'unit_outcome_pk' )
->field(
Field::inst( 'unit_outcome.unit_outcome' ),
Field::inst( 'unit_outcome.modified' ),
Field::inst( 'unit_outcome.modified_by' )->setValue( $user )
)
->join(
Mjoin::inst( 'unit' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_unit_outcome_lookup.unit_outcome_fk' )
->link( 'unit.unit_pk', 'unit_unit_outcome_lookup.unit_fk' )
->order( 'unit.unit_name asc' )
->fields(
Field::inst( 'unit_pk' )
->options( Options::inst()
->table( 'unit' )
->value( 'unit_pk' )
->label( 'unit_name' )
),
Field::inst( 'unit_name' )
)
)
->join(
Mjoin::inst( 'program_outcome' )
->link( 'unit_outcome.unit_outcome_pk', 'program_outcome_unit_outcome_lookup.unit_outcome_fk' )
->link( 'program_outcome.program_outcome_pk', 'program_outcome_unit_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( 'program_outcome' )
->render( function ( $row ) {
return strip_tags($row['program_outcome']);
} )
->order( 'program_outcome' )
),
Field::inst( 'program_outcome' )
)
)
->join(
Mjoin::inst( 'strand' )
->link( 'unit_outcome.unit_outcome_pk', 'strand_unit_outcome_lookup.unit_outcome_fk' )
->link( 'strand.strand_pk', 'strand_unit_outcome_lookup.strand_fk' )
->order( 'strand.strand_name asc' )
->fields(
Field::inst( 'strand_pk' )
->options( Options::inst()
->table( 'strand' )
->value( 'strand_pk' )
->label( 'strand_name' )
->order( 'strand_name' )
),
Field::inst( 'strand_name' )
)
)
->join(
Mjoin::inst( 'discipline' )
->link( 'discipline_outcome.discipline_outcome_pk', 'discipline_discipline_outcome_lookup.discipline_outcome_fk' )
->link( 'discipline.discipline_pk', 'discipline_discipline_outcome_lookup.discipline_fk' )
->order( 'discipline.discipline asc' )
->fields(
Field::inst( 'discipline_pk' )
->options( Options::inst()
->table( 'discipline' )
->value( 'discipline_pk' )
->label( 'discipline' )
->order( 'discipline' )
),
Field::inst( 'discipline' )
)
)
->join(
Mjoin::inst( 'discipline_outcome' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
->link( 'discipline_outcome.discipline_outcome_pk', 'unit_outcome_discipline_outcome_lookup.discipline_outcome_fk' )
->order( 'discipline_outcome.discipline_outcome asc' )
->fields(
Field::inst( 'discipline_outcome_pk' )
->options( Options::inst()
->table( 'discipline_outcome' )
->value( 'discipline_outcome_pk' )
->label( 'discipline_outcome' )
->order( 'discipline_outcome' )
),
Field::inst( 'discipline_outcome' )
)
)
->process($_POST)
->json();

<?php > ``` ?>

peterbrowne

Removing the following results in no errors, but that doesn't help me populate the discipline select list...which I want to use for the discipline_outcome select...

    ->join(
        Mjoin::inst( 'discipline' )
            ->link( 'discipline_outcome.discipline_outcome_pk', 'discipline_discipline_outcome_lookup.discipline_outcome_fk' )
            ->link( 'discipline.discipline_pk', 'discipline_discipline_outcome_lookup.discipline_fk' )
            ->order( 'discipline.discipline asc' )
            ->fields(
                Field::inst( 'discipline_pk' )
                    ->options( Options::inst()
                        ->table( 'discipline' )
                        ->value( 'discipline_pk' )
                        ->label( 'discipline' )
                        ->order( 'discipline' )
                    ),
                Field::inst( 'discipline' )
            )
    )

allan

Thanks for the updates. So the problem here is that it looks like you are trying to do an Mjoin onto an Mjoin. Unfortunately the PHP libraries do not support that.

With the Mjoin->link method, one of the fields used in the condition needs to link back to the host table - e.g.:

            ->link( 'discipline_outcome.discipline_outcome_pk', 'discipline_discipline_outcome_lookup.discipline_outcome_fk' )
            ->link( 'discipline.discipline_pk', 'discipline_discipline_outcome_lookup.discipline_fk' )

In this case that would be discipline_outcome.discipline_outcome_pk. But that doesn't exist in the parent table - it is in a different Mjoin.

Are any of these one-to-one links that you could do with a left join, or are they all one-to-many?

Allan

peterbrowne

Hi Allan

I don't really need that Mjoin in my last comment. Its only purpose was to get a select list containing disciplines, that would be then used to populate the dependent select list beneath it for discipline outcomes: disciplines -> discipline outcomes, with a discipline having many discipline outcomes.

You will notice that I already have the separate query for the discipline_outcomes dependent list based on the option selected in the discipline select list in the edit_unit_outcome.php code in my original question.

Is there a way to include the discipline table in the above unit_outcome_data.php server file (and repeated below) without having any join (back to my original question...)?

unit_outcome_data.php

```
<?php
session_start();

$user = str_replace("'", '', $_SESSION['cm_user']['user']);

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_md, 'unit_outcome', 'unit_outcome_pk' )
->field(
Field::inst( 'unit_outcome.unit_outcome' ),
Field::inst( 'unit_outcome.modified' ),
Field::inst( 'unit_outcome.modified_by' )->setValue( $user )
)
->join(
Mjoin::inst( 'unit' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_unit_outcome_lookup.unit_outcome_fk' )
->link( 'unit.unit_pk', 'unit_unit_outcome_lookup.unit_fk' )
->order( 'unit.unit_name asc' )
->fields(
Field::inst( 'unit_pk' )
->options( Options::inst()
->table( 'unit' )
->value( 'unit_pk' )
->label( 'unit_name' )
),
Field::inst( 'unit_name' )
)
)
->join(
Mjoin::inst( 'program_outcome' )
->link( 'unit_outcome.unit_outcome_pk', 'program_outcome_unit_outcome_lookup.unit_outcome_fk' )
->link( 'program_outcome.program_outcome_pk', 'program_outcome_unit_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( 'program_outcome' )
->render( function ( $row ) {
return strip_tags($row['program_outcome']);
} )
->order( 'program_outcome' )
),
Field::inst( 'program_outcome' )
)
)
->join(
Mjoin::inst( 'strand' )
->link( 'unit_outcome.unit_outcome_pk', 'strand_unit_outcome_lookup.unit_outcome_fk' )
->link( 'strand.strand_pk', 'strand_unit_outcome_lookup.strand_fk' )
->order( 'strand.strand_name asc' )
->fields(
Field::inst( 'strand_pk' )
->options( Options::inst()
->table( 'strand' )
->value( 'strand_pk' )
->label( 'strand_name' )
->order( 'strand_name' )
),
Field::inst( 'strand_name' )
)
)
->join(
Mjoin::inst( 'discipline' )
->link( 'discipline_outcome.discipline_outcome_pk', 'discipline_discipline_outcome_lookup.discipline_outcome_fk' )
->link( 'discipline.discipline_pk', 'discipline_discipline_outcome_lookup.discipline_fk' )
->order( 'discipline.discipline asc' )
->fields(
Field::inst( 'discipline_pk' )
->options( Options::inst()
->table( 'discipline' )
->value( 'discipline_pk' )
->label( 'discipline' )
->order( 'discipline' )
),
Field::inst( 'discipline' )
)
)
->join(
Mjoin::inst( 'discipline_outcome' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
->link( 'discipline_outcome.discipline_outcome_pk', 'unit_outcome_discipline_outcome_lookup.discipline_outcome_fk' )
->order( 'discipline_outcome.discipline_outcome asc' )
->fields(
Field::inst( 'discipline_outcome_pk' )
->options( Options::inst()
->table( 'discipline_outcome' )
->value( 'discipline_outcome_pk' )
->label( 'discipline_outcome' )
->order( 'discipline_outcome' )
),
Field::inst( 'discipline_outcome' )
)
)
->process($_POST)
->json();

<?php > ``` ?>

peterbrowne

I guess I could just hard code values of the parent dependent select, given there are only 7 or so...

{
                    label: "Discipline:",
                    name: "discipline[].discipline_pk",
                    type: "select",
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    options: [
                        { label: 'Emergency Medicine', value: 1 },
                        { label: 'General Practice', value: 2 },
                        { label: 'Internal Medicine', value: 3 },
                        { label: 'Obstretics & Gynaecology', value: 4 },
                        { label: 'Paediatrics', value: 5 },
                        { label: 'Psychiatry', value: 6 },
                        { label: 'Surgery', value: 7 }      
                    ]
                }

peterbrowne

Also, an aside matter, I have noticed that with a dependent select, it seems to load increasing slower the more the parent of the dependent is selected.

The dependent is populated quickly the first few times, but then the load times are noticeably longer after each selection of the parent. Possibly needs a new question.

allan

For this latter issue of dependent speed - if you look and the browser's network panel, is it sending multiple requests after you do it a few times? Or is the server taking a while to respond? I think I'd probably need a link to a test case to be able to help with that one.

On the other point of the options - you have:

    ->join(
        Mjoin::inst( 'discipline' )
            ->link( 'discipline_outcome.discipline_outcome_pk', 'discipline_discipline_outcome_lookup.discipline_outcome_fk' )
            ->link( 'discipline.discipline_pk', 'discipline_discipline_outcome_lookup.discipline_fk' )
            ->order( 'discipline.discipline asc' )
            ->fields(
                Field::inst( 'discipline_pk' )
                    ->options( Options::inst()
                        ->table( 'discipline' )
                        ->value( 'discipline_pk' )
                        ->label( 'discipline' )
                        ->order( 'discipline' )
                    ),
                Field::inst( 'discipline' )
            )
    )

That should be returning a list of options for your discipline[].discipline_pk field. Can you show me the JSON return from the server please?

Thanks,
Allan

peterbrowne

Thanks Allan.

Actually a dependent select doesn't seem to be able to do what I need.

The problem is that I need to have the ability to select discipline outcomes from a number of disciplines in order to enable adding multiple discipline outcomes to a unit outcome.

With the single dependent select, choosing a discipline repopulates the discipline outcomes select list.

If the user selects one discipline outcome, then chooses another discipline, and selects another discipline outcome from the dependent and saves, the lookup table is updated based on the key of unit_outcome_pk in the lookup table, so only one discipline outcome can be added to a unit outcome.

What I need, is to have multiple one to many records for unit_outcome_pk -> discipline_outcome_pk.

Possibly a solution is not to use a dependent select for discipline outcomes, but to use a separate select for each discipline that has it's own discipline outcomes. Not sure how I would do that though...

Is it possible to have a WHERE condition in the query and name the fields accordingly? Or some other method? At the moment all discipline outcomes, for all disciplines, from the lookup table are being added to the single discipline outcomes select.

->join(
        Mjoin::inst( 'discipline_outcome' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
            ->link( 'discipline_outcome.discipline_outcome_pk', 'unit_outcome_discipline_outcome_lookup.discipline_outcome_fk' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->fields(
                Field::inst( 'discipline_outcome_pk' )
                    ->options( Options::inst()
                        ->table( 'discipline_outcome' )
                        ->value( 'discipline_outcome_pk' )
                        ->label( 'discipline_outcome' )
                        ->order( 'discipline_outcome' )
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )

peterbrowne

OK, I have tried a WHERE on the Mjoin but not getting any results:

->join(
        Mjoin::inst( 'discipline_outcome' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
            ->link( 'discipline_outcome.discipline_outcome_pk', 'unit_outcome_discipline_outcome_lookup.discipline_outcome_fk' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->fields(
                Field::inst( 'discipline_outcome_pk' )
                    ->options( Options::inst()
                        ->table( 'discipline_outcome' )
                        ->value( 'discipline_outcome_pk' )
                        ->label( 'discipline_outcome' )
                ->where( 'discipline_outcome.discipline_fk', '5' )
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )

Without ->where( 'discipline_outcome.discipline_fk', '5' ) I get all discipline outcomes OK....

peterbrowne

Got it working with:

    ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
            ->link( 'discipline_outcome.discipline_outcome_pk', 'unit_outcome_discipline_outcome_lookup.discipline_outcome_fk' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->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', '5' );
                        }
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    )

So rather than using a dependent select, I will use seven select lists, one for each discipline. Probably a leaner solution may exist, but this would be OK.

peterbrowne

Next problem is how do I target the different Mjoins with WHERE clauses to the front end editor? I will need to have separate versions of the following for each of the seven disciplines:

 {
                    label: "Discipline Outcome:",
                    name: "discipline_outcome[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }

allan

I don't quite understand - sorry.

Are there different outcomes for each discipline? If so, they you do have a dependency, since you'll need to display different options for each discipline.

Sorry if I'm just not getting it - I don't have the wider view of what the project is trying to do.

Allan