Multiple Mjoin with WHERE clause on same tables - How to taget different editor fields

peterbrowne · September 2020

I have seven (7) similar Mjoins on the same tables, with the only differences being on the WHERE clause, e.g. $q->where( 'discipline_fk', '5' )

Like:

->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'discipline_outcomes_5' )
            ->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' )
            )
    )
    )

I will need to target the similar Mjoins to different Editor fields. Currently I have just the one for the above Mjoin:

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

How can I setup the 7 Editor fields like above for the 7 Mjoins on the same tables, but with different WHERE clause?

peterbrowne · September 2020

.

peterbrowne · September 2020

The title should read Target not Taget...

peterbrowne · September 2020

Basically the question is asking if there is another way of targeting which Editor field a join is sending the results to, e.g. in this instance:

name: "discipline_outcome[].discipline_outcome_pk",

peterbrowne · September 2020

I have also asked on Stack Overflow

rf1234 · September 2020

just create identical views of table "discipline_outcome" with different names. They don't even need to be updatable because you don't update them anyway. Only the link table gets updated. I do this for self-referencing link tables.

My use case is one contract can be the underlying for another contract. E.g. A loan maybe the underlying of a derivative contract.

For internal netting one contract can be the base of another contract as well. So "underlying" is a view on "contract" and "base" is just the same as "underlying", but has a different name.

You could define your views like this. Or specify the fields you really need in the view. Might only be the primary key?!

CREATE VIEW `discipline_outcome_02` AS
   SELECT *
   FROM `discipline_outcome`

CREATE VIEW `discipline_outcome_03` AS
   SELECT *
   FROM `discipline_outcome`

etc.

allan · September 2020

You can use the Mjoin->name() method for what you are looking for. WIth that you can control the JSON / HTTP parameter name that is used, effectively aliasing the table's name to something else.

Allan

peterbrowne · September 2020

Thanks Allan.

I thought that there would be a method for using an alias. However, it's not working for me using that method with:

->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'discipline_outcomes_5' )
...

Using the normal approach without the alias name method populates a single select OK, but I need to have the alias working for multiple WHERE queries and corresponding Editor select fields.

Relevant code is:

unit_outcome_data.php:

    ->join(
        Mjoin::inst( 'discipline_outcome' )
        ->name( 'discipline_outcomes_5' )
            ->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' )
            )
    )
    )

edit_unit_outcome.php:

        <div class='table_container'>
            <table id='datatables_table' class='display' style="width:100%">
                <thead>
                    <tr>
                        <th>Unit Outcome</th>
                        <th>Unit</th>
                        <th>Program Outcome</th>
                        <th>Strand</th>
                        <th>Discipline Outcome</th>
                        <th>Modified</th>
                        <th>Modified By</th>
                        <th>Edit</th>
                    </tr>
                </thead>
                <tbody></tbody>
            </table>
        </div>
        <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="discipline_outcomes_5"></editor-field>
        </div>
    </div>

    <script type="text/javascript">
        var editor; // use a global for the submit and return data rendering in the examples

        $( document ).ready( function () {

            var program = $( '#program_field' ).val();
            var user = $( '#user_field' ).val();
            var permission = $( '#permission_field' ).val();

            $( '#main-menu' ).smartmenus();

            $( '#program_levels' ).change( function ( e ) {
                window.location.href = 'edit_' + this.value + ".php?program=" + program + '&user=' + user;
            } );

            // Edit record
            $( '#datatables_table' ).on( 'click', 'a.editor_edit', function ( e ) {
                e.preventDefault();
                editor.edit( $( this ).closest( 'tr' ), {
                    title: 'Edit record',
                    buttons: 'Update'
                } );
            } );

            $.fn.dataTable.Editor.display.lightbox.conf.windowPadding = 50;

            var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/unit_outcome_data.php",
                table: "#datatables_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: "Strand:",
                    name: "strand[].strand_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcome:",
                    name: "discipline_outcomes_5",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }]
            } );
    
            


            var table = $( '#datatables_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: "strand",
                    render: "[, ].strand_name"
                }, {
                    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: []
            } );

peterbrowne · September 2020

Doh! OK I got it working changing the fields in edit_unit_outcome.php to:

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

and

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

Works great.

Thanks Allan!

peterbrowne · September 2020

Well something else is now the problem.

When created a new unit_outcome record, and selecting some discipline_outcomes, the lookup table has the records inserted OK.

However, on update, all the records associated with the unit_outcome_fk are deleted in lookup table unit_outcome_discipline_outcome_lookup.

Existing records in the lookup table also can't be updated in the lookup table,

I think this is just affecting updates to the lookup table unit_outcome_discipline_outcome_lookup. The unit outcome in the parent table gets updated OK, e.g. if changing the name of the unit_outcome.

peterbrowne · September 2020

This is my current code:

edit_unit_outcome.php:

<div class='table_container'>
            <table id='datatables_table' class='display' style="width:100%">
                <thead>
                    <tr>
                        <th>Unit Outcome</th>
                        <th>Unit</th>
                        <th>Program Outcome</th>
                        <th>Strand</th>
                        <th>Discipline Outcome</th>
                        <th>Modified</th>
                        <th>Modified By</th>
                        <th>Edit</th>
                    </tr>
                </thead>
                <tbody></tbody>
            </table>
        </div>
        <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="ed[].discipline_outcome_pk"></editor-field>
            <editor-field name="gp[].discipline_outcome_pk"></editor-field>
            <editor-field name="internal_medicine[].discipline_outcome_pk"></editor-field>
            <editor-field name="obs_gyn[].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>
        </div>
    </div>

    <script type="text/javascript">
        var editor; // use a global for the submit and return data rendering in the examples

        $( document ).ready( function () {

            var program = $( '#program_field' ).val();
            var user = $( '#user_field' ).val();
            var permission = $( '#permission_field' ).val();

            $( '#main-menu' ).smartmenus();

            $( '#program_levels' ).change( function ( e ) {
                window.location.href = 'edit_' + this.value + ".php?program=" + program + '&user=' + user;
            } );

            // Edit record
            $( '#datatables_table' ).on( 'click', 'a.editor_edit', function ( e ) {
                e.preventDefault();
                editor.edit( $( this ).closest( 'tr' ), {
                    title: 'Edit record',
                    buttons: 'Update'
                } );
            } );

            $.fn.dataTable.Editor.display.lightbox.conf.windowPadding = 50;

            var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/unit_outcome_data.php",
                table: "#datatables_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: "Strand:",
                    name: "strand[].strand_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - Emergency Medicine:",
                    name: "ed[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - General Practice:",
                    name: "gp[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - Internal Medicine:",
                    name: "internal_medicine[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - Obs & Gyn:",
                    name: "obs_gyn[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - Pediactrics:",
                    name: "paediatrics[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - Psychiatry:",
                    name: "psychiatry[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - Surgery:",
                    name: "surgery[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }]
            } );
    
            


            var table = $( '#datatables_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: "strand",
                    render: "[, ].strand_name"
                }, {
                    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: []
            } );

peterbrowne · September 2020

And in

unit_outcome_data.php:

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( 'ed' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
            ->link( 'ed.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', '1' );
                        }
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    )
        ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'gp' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
            ->link( 'gp.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', '2' );
                        }
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    )
        ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'internal_medicine' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
            ->link( 'internal_medicine.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', '3' );
                        }
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    )
        ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'obs_gyn' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
            ->link( 'obs_gyn.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', '4' );
                        }
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    )
    ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'paediatrics' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
            ->link( 'paediatrics.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' )
            )
    )
    )
    ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'psychiatry' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
            ->link( 'psychiatry.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', '6' );
                        }
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    )
        ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'surgery' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_lookup.unit_outcome_fk' )
            ->link( 'surgery.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', '7' );
                        }
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    )
    ->process($_POST)
    ->json();

peterbrowne · September 2020

I suspect, not at all sure, that the UPDATE on unit_outcome_discipline_outcome_lookup is failing on the WHERE clauses for the discipline_outcome Mjoins...

peterbrowne · September 2020

I think this issue of not updating the lookup table, instead deleting the associated records in the lookup, was the subject of this post:

https://datatables.net/forums/discussion/46958/why-do-multiple-mjoins-on-tbl-with-alias-work-when-creating-records-breaks-when-updating-records

At that time, Allan, you indicated it would be fixed v1.8...

I'm using https://cdn.datatables.net/responsive/2.2.3/js/dataTables.responsive.min.js

peterbrowne · September 2020

.

allan · September 2020

I've posted a reply in your other thread asking this.

Allan

Multiple Mjoin with WHERE clause on same tables - How to taget different editor fields

Multiple Mjoin with WHERE clause on same tables - How to taget different editor fields

Answers

Howdy, Stranger!

Categories

DataTables

Multiple Mjoin with WHERE clause on same tables - How to taget different editor fields

Multiple Mjoin with WHERE clause on same tables - How to taget different editor fields

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables