MJoin alias and displaying results

MJoin alias and displaying results

peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    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.

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    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

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

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

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited October 2020

    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":[]},
    
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    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

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    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?

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    ```
    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();

    <?php > ``` ?>
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

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

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    .

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    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

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited November 2020

    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.

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited November 2020

    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.

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    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

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    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
                    },
    
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

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

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

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

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    Answer ✓

    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

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
    edited November 2020
    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

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    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"
                    },
    
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    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' )
                )
        )
    
  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    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

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
                    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

This discussion has been closed.