MJoin Alias - Records deleted in lookup table when updating

peterbrowne

I am using multiple Mjoins with aliases. These work OK for creating records, but when updating records, the entries corresponding to the parent table in the the lookup table are deleted.

This was I believe 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

This question relates to a previous question which probably can be removed.

peterbrowne

Relevant code:

unit_outcome_data.php:

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

and edit_unit_outcome.php:

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

peterbrowne

.

allan

I think what is missing here is that you don't have a where condition on the Mjoin instance - just on the options.

Looking at the code will apply a where condition to the delete command, so hopefully all you need to do as add:

->where('discipline_fk', '5' )

Allan

peterbrowne

Thanks Allan, but I'm confused as to where this WHERE should go in the following code:

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

I tried adding ->where('discipline_fk', '5' ) just beneath the ->link lines, but doing an UPDATE still deletes the entries in the lookup table, instead of UPDATING.

If you could clarify how the code above should be changed in more detail please. Also, is the WHERE clause on the options otherwise not needed?

allan

just beneath the ->link

Yes - that should have done it. Can you show me the JSON result from the debug when it added in there?

Also, is the WHERE clause on the options otherwise not needed?

You would still keep it on the options, since that is generated by a separate query (assuming you want the options for this Mjoin to be limited).

Allan

peterbrowne

This is the JSON on adding a new unit_outcome:

{
  "data": [
    {
      "DT_RowId": "row_132",
      "unit_outcome": {
        "unit_outcome": "  Test Only 2<\/p>",
        "modified": "2020-09-25 08:35:13",
        "modified_by": "00082563"
      },
      "unit": [
        {
          "unit_pk": "1",
          "unit_name": "Integrated Medical Systems 1"
        }
      ],
      "program_outcome": [
        {
          "program_outcome_pk": "11",
          "program_outcome": "  11 Scientific Knowledge <\/strong><\/p>  Inform medical practice with critical evaluation and application of biomedical, behavioural, epidemiological, clinical and translational sciences<\/p>"
        }
      ],
      "strand": [
        {
          "strand_pk": "14",
          "strand_name": "Clinical Communication"
        }
      ],
      "ed": [
        
      ],
      "gp": [
        
      ],
      "internal_medicine": [
        
      ],
      "obs_gyn": [
        {
          "discipline_outcome_pk": "82"
        }
      ],
      "paediatrics": [
        {
          "discipline_outcome_pk": "50"
        }
      ],
      "psychiatry": [
        
      ],
      "surgery": [
        
      ]
    }
  ]
}

There are 7 Mjoins with WHERE clauses to correspond to the 7 selects in the Editor for the 7 medical disciplines (ed, gp, internal_medicine, obs_gyn, paediatrics, psychiatry and surgery) which contain their own discipline outcomes in the selects.

peterbrowne

And the table structure and the above insert into the lookup table:

CREATE TABLE IF NOT EXISTS `unit_outcome_discipline_outcome_lookup` (
  `unit_outcome_discipline_lookup_pk` int(8) NOT NULL AUTO_INCREMENT,
  `unit_outcome_fk` int(6) NOT NULL,
  `discipline_outcome_fk` int(2) NOT NULL,
  PRIMARY KEY (`unit_outcome_discipline_lookup_pk`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=99 ;

--
-- Dumping data for table `unit_outcome_discipline_outcome_lookup` 
--

INSERT INTO `unit_outcome_discipline_outcome_lookup` (`unit_outcome_discipline_lookup_pk`, `unit_outcome_fk`, `discipline_outcome_fk`) VALUES
(92, 132, 0),
(93, 132, 0),
(94, 132, 0),
(95, 132, 82),
(96, 132, 50),
(97, 132, 0),
(98, 132, 0);

allan

Apologies, I had thought that debug mode was enabled in your script, but apparently not. Could you add ->debug(true) before the ->process() method call and show me the resulting JSON from an edit again? It will show the SQL that is being executed then.

Allan

peterbrowne

Like this?

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

peterbrowne

In the above JSON with embedded query, this was using only two, not all seven, Mjoins and corresponding selects (to make things simpler...).

I have found that the lookup table has, in this case, created the two records in the lookup table when creating a new record.

When updating that record, I notice that only the last select for the Mjoin has an option selected, not the previous Mjoin select (in this case for 2 Mjoins...). But even though I re-select that option in that first select, the FIRST record (the one corresponding to that select) in the lookup table gets deleted, not the record associated with the last Mjoin select.

With all 7 Mjoin selects in the original files, not all have records yet, so some of those selects have no options. Also, the last select had no options as well.

So, if only the last Mjoin was being updated in the logic, then there would have been no record for that last Mjoin to update. Hence all records for that parent record in the lookup table would show as deleted.

It looks like a bug in your code.

allan

I'm not actually seeing a DELETE statement at all in the queries there. What this for a "create" action or an "edit"? Could you also show me the full PHP that you used for when that trace was taken please?

Thanks,
Allan

peterbrowne

Allan, there is no delete being intentionally done my end. Just an update. As I said, the create works fine. When updating, all the corresponding records in the lookup tabe get deleted (except possibly for the last Mjoin using an alias) when using aliases.

peterbrowne

Code from the edit_unit_outcome.php file (front end). I have simplified this to just have 2 of the selects related to the Mjoins with aliases, not all 7 that I will need.

<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="paediatrics[].discipline_outcome_pk"></editor-field>
            <editor-field name="surgery[].discipline_outcome_pk"></editor-field>
    </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 - Pediactrics:",
                    name: "paediatrics[].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: []
            } );

    if ( permission == 'create_edit' || permission == 'program_admin' || permission == 'super' ) {
                table.button().add( null, {
                    extend: 'create',
                    editor: editor
                } );
                table.button().add( null, {
                    extend: 'edit',
                    editor: editor
                } );
            }

            if ( permission == 'super' || permission == 'program_admin' ) {
                table.button().add( null, {
                    extend: 'remove',
                    editor: editor
                } );
            }
            
        } );

peterbrowne

Code for the unit_outcome_data.php file (server side):

<?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_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' )
            ->where('discipline_fk', '5' )
           // ->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( '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' )
            ->where('discipline_fk', '7' )
           // ->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' )
            )
    )
    )
    ->debug(true)
    ->process($_POST)
    ->json();
?>

peterbrowne

Any ideas??

allan

Apologies for having not been able to reply over the weekend.

When updating, all the corresponding records in the lookup tabe get deleted (except possibly for the last Mjoin using an alias) when using aliases.

Right - but my problem is that I don't see a DELETE statement in the debug information given above. I will try to reproduce the issue locally here.

Allan

allan

I'm starting to get a better handle on this now. The code I linked to above doesn't apply since we are in a proper link table situation here.

When setting up my local example of this I realised that with your code you would only ever have a single option per field - is that correct?

I'm also not clear why you are using discipline_outcome as the name for each of the Mjoin instances? The links don't appear to use a discipline_outcome table. So for example:

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

should be:

        Mjoin::inst( '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' )

I might well be missing something here, but that makes sense does it not?

Allan

peterbrowne

Hi Allan

The selects for the Mjoins contain all options related to the discipline in the WHERE clause. This all comes from one table, discipline_outcome and the options in the selects related to the discipline are matched via the lookup table unit_outcome_discipline_outcome_lookup in the WHERE clause.

One or more options can be selected from those selects.

If I change:

        Mjoin::inst( 'discipline_outcome' )
            ->name( 'surgery' )

to

        Mjoin::inst( 'surgery' )
            ->name( 'surgery' )

I get an error that the table surgery doesn't exist, which it doesn't...and shouldn't as the records are in table discipline_outcomes.

So, using:

->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'paediatrics' )
            ->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' )
            ->where('discipline_fk', '5' )
           // ->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( 'surgery' )
            ->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' )
            ->where('discipline_fk', '7' )
           // ->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' )
            )
    )
    )

The above works for creating the record, but when doing an UPDATE the corresponding records in the lookup table are deleted, EXCEPT for the LAST Mjoin which is left intact. Note that I just used ->link( 'surgery.discipline_outcome_pk', as a test, but makes no difference using that or ->link( 'discipline_outcome.discipline_outcome_pk',, they both work to a point as stated above.

peterbrowne

Any update on this yet?

allan

Apologies for the delay - in not being able to get back to you yesterday. I didn't have a chance to sit down and think about this one properly!

Looking at the code above, the options for each Mjoin are coming from the discipline_outcome with a specific condition (which may have multiple options) - that's fine.

What isn't fine, and I think is the underlying issue, is how unit_outcome_discipline_outcome_lookup is being used as a link / junction table for multiple different Mjoin instances. The Editor libraries don't allow for that. They expect the link table to contain two columns and two columns only: one referencing the host primary key and one referencing the child primary key.

The way Editor's PHP libraries work is that rather than trying to do a 'diff' on the submitted data and the data in the database, it is just deletes all relevant entries for the host key, and then inserts the new data.

The upshot of that is that there can only be one Mjoin instance per link table - any subsequent Mjoins attempting to use the same table would delete what has gone before, which is exactly what you are seeing. There is no way for Editor to tell which Mjoin instance a row in the join table belongs to.

It might be possible to extend Mjoin so it can consider a third column (or more) in the link table and apply constriction conditions to that (also inserting the values when the new items are created), but that isn't how it works at the moment when using a link table I'm afraid.

At this time, if you want multiple Mjoins with link tables, you would need multiple link tables - e.g. unit_outcome_discipline_outcome_lookup_7.

Allan

peterbrowne

OK, thanks Allan.

Is it possible then to have a select in the editor that is not a join on the main parent table? So a separate data script from the main one?

That way there wouldn't need to be a relationship in the main data script for the parent table. So in the discipline_outcome table, there is a foreign_key for the table discipline This way I could have the 7 selects for discipline_outcomes based on the 7 discipline foreign keys.

If this is possible, can the separate data script handle all 7 left joins at once, or would I need 7 separate scripts?

allan

Is it possible then to have a select in the editor that is not a join on the main parent table? So a separate data script from the main one?

You certainly can, but it would just store the value of the select into the parent table, which doesn't sound like what you need.

On the client-side, the select is just a bunch of values - I think the problem here is how to make the server-side aspect work, and it doesn't quite line up with how the libraries operate I'm afraid.

Allan

peterbrowne

OK. I now have 7 lookup tables, one for each discipline, each containing three columns, a primary key column and columns for each of unit_outcome_fk and discipline_outcome_fk

Testing with just 2 of those lookup tables, I notice a few things:

If I have:

        <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_outcome[].discipline_outcome_pk"></editor-field>
            <editor-field name="discipline_outcome[].discipline_outcome_pk"></editor-field>
    </div>

The discipline_outcome select below is repeated in the editor (in the browser) 5 times.

Even though I have just the one discipline_outcome select:

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

This may be due to having 5 fields in the editor section of the script:

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: "discipline_outcome[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }]
            } );

If I change that part of the script to have 2 discipline_outcome selects, I don't get ANY records returned on the page or DB error message.

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: "discipline_outcome[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - General Practice:",
                    name: "discipline_outcome[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }]
            } );

I will add the full server and client code below in the next comment.

peterbrowne

Client side edit_unit_outcome.php:

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: "discipline_outcome[].discipline_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Discipline Outcomes - General Practice:",
                    name: "discipline_outcome[].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

and the server side script, 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( 'discipline_outcome' )
            ->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' )
                        ->where( function ($q) {
                            $q->where( 'discipline_fk', '1' );
                        }
                    ),
                Field::inst( 'discipline_outcome' )
            )
    )
    )
            ->join(
        Mjoin::inst( 'discipline_outcome' )
            ->name( 'discipline_outcome' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_2_lookup.unit_outcome_fk' )
            ->link( 'discipline_outcome.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' )
            )
    )
    )
    ->debug(true)
    ->process($_POST)
    ->json();

peterbrowne

Note also, that if I just have the one discipline_outcome[].discipline_outcome_pk field in the editor code, it works fine, but I can't get it to work with 2.

Do I need to use aliases, if so how?

peterbrowne

OK, I used aliases and now works fine so far, except for one smallish problem. So using:

<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_plactice[].discipline_outcome_pk"></editor-field>
    </div>

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

and server side:

->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_plactice' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_discipline_outcome_2_lookup.unit_outcome_fk' )
            ->link( 'general_plactice.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' )
            )
    )
    )

The smallish problem is when I do an update, and select one of the discipline_outcome select to 'No selection' to remove the selected entry in that select, the corresponding record in the lookup table is not deleted, but gets a zero value for the discipline outcome instead of the previous value.

I know this is the placeholder value, but what should I use to get the record deleted in this case?

peterbrowne

Also, when using aliases, I con't get the Datatables display columns to show the records using:

        <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_plactice[].discipline_outcome_pk"></editor-field>
            <editor-field name="internal_medicine[].discipline_outcome_pk"></editor-field>
            <editor-field name="obstretics_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>

and

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: "emergency_medicine",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "general_plactice",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "internal_medicine",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "obstretics_gynaecology",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "paediatrics",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "psychiatry",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "surgery",
                    render: "[, ].discipline_outcome"
                }, {
                    data: "infection_immunity",
                    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

bump

peterbrowne

I have asked a separate question in the forum on this specific issue.