MJoin alias and displaying results

peterbrowne

I have a number of Mjoins using aliases. How do I display the results in Datatables using the alias names? E,g.

        ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'emergency_medicine' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.unit_outcome_fk' )
            ->link( 'emergency_medicine.discipline_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.discipline_outcome_fk' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->where('discipline_fk', '1' )
            ->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' )
            )
    )
    )

and HTML:

<editor-field name="emergency_medicine[].discipline_outcome_pk"></editor-field>

and Editor field:

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

and Datatables:

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

peterbrowne

I have no problem with the editor parts, just the display in the Datatable. The results are being returned OK as I can see the comma separators, but not the record names.

allan

That looks like what I would expect to be used in terms of the configuration. Could you show me the JSON that is being returned please?

Thanks,
Allan

peterbrowne

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

peterbrowne

You can see below from the JSON (link above) that there are results for unit_outcome_pk = 88 for 'paediatrics':

..."paediatrics":[{"discipline_outcome_pk":"50"},{"discipline_outcome_pk":"22"},{"discipline_outcome_pk":"2"}],"psychiatry":[],"surgery":[],"infection_immunity":[]},

{"DT_RowId":"row_88","unit_outcome":{"unit_outcome":"Apply a quality framework to medical practice and display a commitment to continued improvement of clinical performance<\/p>","modified":"2020-09-22 13:31:11","modified_by":"00082563"},"unit":[{"unit_pk":"4","unit_name":"Integrated Medical Practice 2"}],"program_outcome":[{"program_outcome_pk":"12","program_outcome":"12 Patient Assessment and Clinical Reasoning<\/strong><\/p>Perform an accurate, relevant, timely and prioritised patient assessment; apply justifiable diagnostic reasoning strategies to formulate a relevant and prioritized differential diagnosis and diagnostic strategy for core presentations and medical conditions; and apply logical clinical judgement and decision-making in individual clinical situations<\/p>"}],"strand":[{"strand_pk":"15","strand_name":"Quality Care"}],"emergency_medicine":[{"discipline_outcome_pk":"133"}],"general_practice":[],"internal_medicine":[],"obstetrics_gynaecology":[{"discipline_outcome_pk":"95"}],"paediatrics":[{"discipline_outcome_pk":"50"},{"discipline_outcome_pk":"22"},{"discipline_outcome_pk":"2"}],"psychiatry":[],"surgery":[],"infection_immunity":[]},

In Datatables in the browser, this just shows 2 commas, being the separators for the above 3 discipline_outcomes. The discpline_outcome name is not in the results, note that column is 'discipline_outcome'...

peterbrowne

Likewise for unit_outcome_pk = 112. There are 4 discipline_outcomes related to that unit_outcome for "obstetrics_gynaecology" which is correct, but Datatables just shows 3 commas in the browser...i.e. the records are returned, but no names.

,{"DT_RowId":"row_112","unit_outcome":{"unit_outcome":"Apply a quality framework to medical practice and display a commitment to continued improvement of clinical performance.<\/p>","modified":"2020-10-27 14:17:09","modified_by":"00082563"},"unit":[{"unit_pk":"5","unit_name":"Integrated Medical Practice 3"}],"program_outcome":[{"program_outcome_pk":"16","program_outcome":"16 Quality Care<\/strong><\/p>Apply a quality framework to medical practice and display a commitment to high quality clinical standards<\/p>"}],"strand":[{"strand_pk":"15","strand_name":"Quality Care"}],"emergency_medicine":[],"general_practice":[],"internal_medicine":[],"obstetrics_gynaecology":[{"discipline_outcome_pk":"125"},{"discipline_outcome_pk":"121"},{"discipline_outcome_pk":"95"},{"discipline_outcome_pk":"106"}],"paediatrics":[{"discipline_outcome_pk":"22"}],"psychiatry":[{"discipline_outcome_pk":"40"}],"surgery":[{"discipline_outcome_pk":"132"}],"infection_immunity":[]},

allan

Yes, searching through the JSON I only see a single row which has an entry for the emergency_medicine data point:

        "emergency_medicine": [{
            "discipline_outcome_pk": "133"
        }],

That would certainly explain why the render: "[, ].discipline_outcome" is not showing anything - there isn't a discipline_outcome value.

What I'm not understanding is why emergency_medicine.discipline_outcome_pk isn't being used in the SQL. I presume at least that the code is what is used for this JSON output?

Allan

peterbrowne

What I'm not understanding is why emergency_medicine.discipline_outcome_pk isn't being used in the SQL. I presume at least that the code is what is used for this JSON output?

Well, the 7 alias tables on the Mjoins must be using .discipline_outcome_pk to return the correct records as I mentioned in my last post. There are 2 results for emergency_medicine.discipline_outcome_pk :

,{"**DT_RowId":"row_150",**"unit_outcome":{"unit_outcome":"test no end<\/p>","modified":"2020-10-27 11:57:21","modified_by":"00082563"},"unit":[{"unit_pk":"4","unit_name":"Integrated Medical Practice 2"}],"program_outcome":[{"program_outcome_pk":"1","program_outcome":"1 Professional Behaviours <\/strong><\/p>Display individual, interpersonal and work based professionalism in all aspects of professional life<\/p>"}],"strand":[{"strand_pk":"7","strand_name":"Aboriginal Health"}],"**emergency_medicine":[{"discipline_outcome_pk":"133"}**],

and

{"**DT_RowId":"row_88",**"unit_outcome":{"unit_outcome":"Apply a quality framework to medical practice and display a commitment to continued improvement of clinical performance<\/p>","modified":"2020-09-22 13:31:11","modified_by":"00082563"},"unit":[{"unit_pk":"4","unit_name":"Integrated Medical Practice 2"}],"program_outcome":[{"program_outcome_pk":"12","program_outcome":"12 Patient Assessment and Clinical Reasoning<\/strong><\/p>Perform an accurate, relevant, timely and prioritised patient assessment; apply justifiable diagnostic reasoning strategies to formulate a relevant and prioritized differential diagnosis and diagnostic strategy for core presentations and medical conditions; and apply logical clinical judgement and decision-making in individual clinical situations<\/p>"}],"strand":[{"strand_pk":"15","strand_name":"Quality Care"}],"**emergency_medicine":[{"discipline_outcome_pk":"133"}**],

The server code in unit_outcome_data.php is the following which clearly is expecting discipline_outcome to be returned in the results. I would hazard a guess that the alias tables might be confusing your PHP library in returning discipline_outcome in the results?

peterbrowne

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_outcome' )
            ->name( 'emergency_medicine' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.unit_outcome_fk' )
            ->link( 'emergency_medicine.discipline_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.discipline_outcome_fk' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->where('discipline_fk', '1' )
            ->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' )
            )
    )
    )
            ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'general_practice' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_2_lookup.unit_outcome_fk' )
            ->link( 'general_practice.discipline_outcome_pk', 'unit_outcome_discipline_outcome_2_lookup.discipline_outcome_fk' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->where('discipline_fk', '2' )
            ->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', '2' );
                        }
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    )
                ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'internal_medicine' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_3_lookup.unit_outcome_fk' )
            ->link( 'internal_medicine.discipline_outcome_pk', 'unit_outcome_discipline_outcome_3_lookup.discipline_outcome_fk' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->where('discipline_fk', '3' )
            ->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', '3' );
                        }
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    )
                    ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'obstetrics_gynaecology' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_4_lookup.unit_outcome_fk' )
            ->link( 'obstetrics_gynaecology.discipline_outcome_pk', 'unit_outcome_discipline_outcome_4_lookup.discipline_outcome_fk' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->where('discipline_fk', '4' )
            ->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', '4' );
                        }
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    )
    ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'paediatrics' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_5_lookup.unit_outcome_fk' )
            ->link( 'paediatrics.discipline_outcome_pk', 'unit_outcome_discipline_outcome_5_lookup.discipline_outcome_fk' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->where('discipline_fk', '5' )
            ->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' )
            )
    )
    )
        ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'psychiatry' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_6_lookup.unit_outcome_fk' )
            ->link( 'psychiatry.discipline_outcome_pk', 'unit_outcome_discipline_outcome_6_lookup.discipline_outcome_fk' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->where('discipline_fk', '6' )
            ->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', '6' );
                        }
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    )
            ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'surgery' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_7_lookup.unit_outcome_fk' )
            ->link( 'surgery.discipline_outcome_pk', 'unit_outcome_discipline_outcome_7_lookup.discipline_outcome_fk' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->where('discipline_fk', '7' )
            ->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', '7' );
                        }
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    )
    ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'infection_immunity' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_8_lookup.unit_outcome_fk' )
            ->link( 'infection_immunity.discipline_outcome_pk', 'unit_outcome_discipline_outcome_8_lookup.discipline_outcome_fk' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->where('discipline_fk', '8' )
            ->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', '8' );
                        }
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    )
    ->debug(true)
    ->process($_POST)
    ->json();
?>

peterbrowne

The relevant tables with data at https://www.db-fiddle.com/f/7B8pRJKmyX321khhzydeBN/0

peterbrowne

.

allan

This:

->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.unit_outcome_fk' )
->link( 'emergency_medicine.discipline_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.discipline_outcome_fk' )  

I would have expected to result in a join something like:

JOIN emergency_medicine
  ON emergency_medicine.discipline_outcome_pk = unit_outcome_discipline_outcome_1_lookup.discipline_outcome_fk

in the SQL being executed. That it isn't there is what has been confused / concerned.

My apologies, I've had to rebuild my DataTables development VM, while will take a while on my current connection. I'll either try this at home this evening or get back to you tomorrow when I've had a chance to execute the exact code from above and see if I can debug what is going on.

Thanks,
Allan

peterbrowne

OK thanks Allan. I need to have a resolution soon as a lot of work depends on this working one way or another.

As I said in my previous posts, there is data being returned as I can see the correct number of comma separators, just no names. See:

https://paste.pics/296516038de2cc192fa950e7e9b7380b

I will probably combine those columns later when this is working.

peterbrowne

BTW, the screenshot in the link above is for unit_outcome_pk = 88 (unit_outcome_fk = 88) and the 3 associated discipline_outcome (pks 22, 50 and 2) for paediatics (unit_outcome_discipline_outcome_5_lookup and paediatrics[].discipline_outcome_pk) as you can see in the db fiddle link above.

allan

Hi,

Been looking at the problem this morning and it is caused by:

        Mjoin::inst( 'discipline_outcome' )

because discipline_outcome is not a table that is mentioned in the links that are defined:

            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.unit_outcome_fk' )
            ->link( 'emergency_medicine.discipline_outcome_pk', 'unit_outcome_discipline_outcome_1_lookup.discipline_outcome_fk' )

Looking at your SQL above I don't see emergency_medicine defined as a table anywhere. Is it? I wonder if that should actually be replaced with discipline_outcome in the above?

Thanks,
Allan

peterbrowne

I'm using aliases for the table discipline_outcomes.

I though that was pretty clear from the start.

Did you look at the db fiddle with the tables and data? https://www.db-fiddle.com/f/7B8pRJKmyX321khhzydeBN/0

Am I using aliases in Mjoins incorrectly?

If I use the following code, nothing seems to change. No errors and still no names showing in the paediatrics column in datatables, just comma separators.

    ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'paediatrics' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_5_lookup.unit_outcome_fk' )
            ->link( 'discipline_outcome.discipline_outcome_pk', 'unit_outcome_discipline_outcome_5_lookup.discipline_outcome_fk' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->where('discipline_fk', '5' )
            ->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' )
            )
    )
    )

and

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

peterbrowne

e.g. with the above code, I just get <td class="sorting_1">, , , </td>

peterbrowne

The second WHERE clause in the Mjoin is probably wrong as there is no discipline_fk in the table discipline_outcome, just in the lookup table:

->where( function ($q) {
    $q->where( 'discipline_fk', '5' );
        }

So the Mjoin probably should be:

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

Still make no difference though as the same number of comma separators show with no field names...

allan

I'm using aliases for the table discipline_outcomes.

Either I'm missing something, or I think you might have misunderstood how the name parameter works. Looking in the SQL file (which I did look through as I mentioned above) I couldn't see any mention of emergency_medicine (I'm picking on that one just for consistency). So I'm guessing you are looking for ->name( 'emergency_medicine' ) to do the SQL alias?

I'm afraid it doesn't - it is purely a PHP construct that. It will read the information from the database using the table name given for the Mjoin and the link data. Then the name option is used to write that to the JSON that is sent tot he client-side.

However, I think I've spotted the issue . The code inside ->fields() is wrong. The way it is at the moment it is passing the second Field::inst into ->options() as the second parameter. It is easier to see if the code is reformatted a little:

            ->fields(
                Field::inst( 'discipline_outcome_pk' )
                    ->options(
                        Options::inst()
                            ->table( 'discipline_outcome' )
                            ->value( 'discipline_outcome_pk' )
                            ->label( 'discipline_outcome' ),
                        Field::inst( 'discipline_outcome' )
                    )
            )

It should be:

            ->fields(
                Field::inst( 'discipline_outcome_pk' )
                    ->options(
                        Options::inst()
                            ->table( 'discipline_outcome' )
                            ->value( 'discipline_outcome_pk' )
                            ->label( 'discipline_outcome' )
                    ),
                Field::inst( 'discipline_outcome' )
            )

Sure do wish I'd spotted that earlier...! That should fix the missing discipline_outcome field though .

Allan

peterbrowne

I'm afraid it doesn't - it is purely a PHP construct that. It will read the information from the database using the table name given for the Mjoin and the link data. Then the name option is used to write that to the JSON that is sent tot he client-side.

Yep, that is what I thought it's purpose was, and how I'm using it.

Your spot on re the Mjoin though, and it's all working with that change, e.g:

        ->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' )
            ->order( 'discipline_outcome.discipline_outcome asc' )
            ->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' )
            )
    )

and

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

Phew.!

Thanks Allan!

Peter

peterbrowne

Looks like I didn't test this enough.

With that change, the 7 selects based on the 7 Mjoins no longer filter on the WHERE clauses on those joins.

The change fixed the showing of names in the datatables OK, but now the 7 selects are all populated with the exact same options.

Current code:

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

one select in the editor:

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

and datatables:

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

peterbrowne

and in the server data 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

https://paste.pics/92762506751b2d8ab1143c5167765f1e

and the selects with exact same populated options despite having WHERE clauses which work on the results in datatables

https://paste.pics/266e64be08d75f6f1ee5b29e99f574c4

allan

                Field::inst( 'discipline_outcome_pk' )
                    ->options( Options::inst()
                        ->table( 'discipline_outcome' )
                        ->value( 'discipline_outcome_pk' )
                        ->label( 'discipline_outcome' ) 

will get all of the options from the discipline_outcome_pk table. If you want to limit it to just a specific set of options from that table, you need to add a where condition to it.

Allan