Mjoin issue - Field not included in editor field list

peterbrowne

This is the error:

{"sError":"Join was performed on the field 'unit_outcome_pk' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance."}

Code for editor:

  <div class='table_container'>
    <table id='assessment_table' class='display' style="width:100%">
      <thead>
        <tr>
          <th>Assessment</th>
          <th>Unit</th>
          <th>Weighting</th>
          <th>Assessment Type</th>
          <th>Unit Outcome</th>
          <th>Modified</th>
          <th>Modified By</th>
        </tr>
      </thead>
      <tbody>
      </tbody>
    </table>
  </div>
  <div id="assessment_form">
    <editor-field name="assessment.assessment"></editor-field>
    <editor-field name="unit[].unit_pk"></editor-field>
    <editor-field name="assessment.weighting"></editor-field>
    <editor-field name="assessment_type[].assessment_type_pk"></editor-field>
    <editor-field name="unit_outcome[].unit_outcome_pk"></editor-field>
  </div>

            var editor = new $.fn.dataTable.Editor( {
                ajax: "program_data/assessment_data.php",
                table: "#assessment_table",
                "autoWidth": true,
                template: '#assessment_form',
                fields: [ {
                    label: "Assessment:",
                    name: "assessment.assessment",
                    type: "ckeditor"
                }, {
                    label: "Unit:",
                    name: "unit[].unit_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Weighting:",
                    name: "assessment.weighting"
                }, {
                    label: "Assessment type:",
                    name: "assessment_type[].assessment_type_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }, {
                    label: "Unit Outcome:",
                    name: "unit_outcome[].unit_outcome_pk",
                    type: "select",
                    placeholder: 'No selection',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                    multiple: true
                }]
            } );


            var table = $( '#assessment_table' ).DataTable( {
                responsive: true,
                ajax: "program_data/assessment_data.php",
                dom: "Blfrtip",
                columns: [ {
                    data: "assessment.assessment"
                }, {
                    data: "unit",
                    render: "[, ].unit_name"
                }, {
                    data: "assessment.weighting"
                }, {
                    data: "assessment_type",
                    render: "[, ].assessment_type"
                }, {
                    data: "unit_outcome",
                    render: "[, ].unit_outcome"
                }, {
                    data: "assessment.modified"
                }, {
                    data: "assessment.modified_by"
                } ],
                select: {
                    style: 'os',
                    selector: 'td:first-child'
                },
                buttons: [ ]
            } );

Data code:

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, 'assessment', 'assessment_pk' )    
    ->field(
        Field::inst( 'assessment.assessment' ),
        Field::inst( 'assessment.weighting' ),
        Field::inst( 'assessment.modified' ),
        Field::inst( 'assessment.modified_by' )->setValue( $user )
    )
     ->join(
        Mjoin::inst( 'unit' )
            ->link( 'assessment.assessment_pk', 'unit_assessment_lookup.assessment_fk' )
            ->link( 'unit.unit_pk', 'unit_assessment_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( 'assessment_type' )
            ->link( 'assessment.assessment_pk', 'assessment_assessment_type_lookup.assessment_fk' )
            ->link( 'assessment_type.assessment_type_pk', 'assessment_assessment_type_lookup.assessment_type_fk' )
            ->order( 'assessment_type.assessment_type asc' )
            ->fields(
                Field::inst( 'assessment_type_pk' )
                    ->options( Options::inst()
                        ->table( 'assessment_type' )
                        ->value( 'assessment_type_pk' )
                        ->label( 'assessment_type' )                  
                    ),
                Field::inst( 'assessment_type' )
            )
    )
     ->join(
        Mjoin::inst( 'unit_outcome' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_assessment_lookup.unit_outcome_fk' )
            ->link( 'assessment.assessment_pk', 'unit_outcome_assessment_lookup.assessment_fk' )
            ->order( 'unit_outcome.unit_outcome asc' )
            ->fields(
                Field::inst( 'unit_outcome_pk' )
                    ->options( Options::inst()
                        ->table( 'unit_outcome' )
                        ->value( 'unit_outcome_pk' )
                        ->label( 'unit_outcome' )                 
                    ),
                Field::inst( 'unit_outcome' )
            )
    )
    ->process($_POST)
    ->json();

peterbrowne

OK, fixed that issue by swapping:

->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_assessment_lookup.unit_outcome_fk' )
->link( 'assessment.assessment_pk', 'unit_outcome_assessment_lookup.assessment_fk' )

to

->link( 'assessment.assessment_pk', 'unit_outcome_assessment_lookup.assessment_fk' )
->link( 'unit_outcome.unit_outcome_pk', 'unit_outcome_assessment_lookup.unit_outcome_fk' )

peterbrowne

But now I get:

"A system error has occured."

The error message is:

Notice: Array to string conversion in /var/www/html/curriculum_mapper/datatables/lib/Database/Driver/MysqlQuery.php on line 97 {"data":[{"DT_RowId":"row_1","assessment":{"assessment":"

IMS1 Written Examination MCQ - Wk 5<\/p>","weighting":"10%","modified":"2020-03-06 14:32:24","modified_by":"00082563","unit_fk":"0"},"unit":{"unit_name":null},"assessment_type":[{"assessment_type_pk":"1","assessment_type":"Written Examination - MCQ"}],"unit_outcome":[{"unit_outcome_pk":"1","unit_outcome":"

Display professional behaviour in the educational and clinical settings and outline some challenges to professionalism<\/p>"},{"unit_outcome_pk":"39","unit_outcome":"

Display professional, concise and accurate oral, written and electronic biomedical communication skills<\/p>"}]}]}

peterbrowne

Can I please get an answer on this?

allan

Hi,

Sorry I was unable to get back to you yesterday, I was wrapped up with other things. Unfortunately today has ram away from me as well, but I'll post back having considered the issue properly tomorrow.

Allan

allan

The one thing in the error message tat looks odd to me is that unit is not an array, but it should be based on the configuration.

Could you change:

    ->process($_POST)
    ->json();

to be:

    ->debug(true)
    ->process($_POST)
    ->json();

and then show me the JSON return from the server (it might still include the error message).

Also, I presume that this error message is appearing on create or edit. Not on the initial population of the table?

Thanks,
Allan

peterbrowne

Hi Allan

Thanks for looking into this. Yes, the error only happens on create and edit. I suspect that something might not be escaped properly.

Here is what is returned:

Notice: Array to string conversion in /var/www/html/curriculum_mapper/datatables/lib/Database/Driver/MysqlQuery.php on line 97 {"data":[{"DT_RowId":"row_1","assessment":{"assessment":"

IMS1 Written Examination MCQ - Wk 5<\/p>","weighting":"10%","modified":"2020-03-06 14:32:24","modified_by":"00082563","unit_fk":"0"},"unit":{"unit_name":null},"assessment_type":[{"assessment_type_pk":"1","assessment_type":"Written Examination - MCQ"}],"unit_outcome":[{"unit_outcome_pk":"93","unit_outcome":"

Display effective self-assessment skills, seek and effectively respond to constructive feedback, provide constructive feedback to others, and evaluate different assessment methods and strategies<\/p>"}]}],"debug":[{"query":"SELECT `assessment`.`assessment_pk` as 'assessment.assessment_pk' FROM `assessment` WHERE `assessment`.`assessment_pk` = :where_0 ","bindings":[{"name":":where_0","value":"1","type":null}]},{"query":"UPDATE `assessment` SET `assessment` = :assessment, `weighting` = :weighting, `modified_by` = :modified_by, `unit_fk` = :unit_fk WHERE `assessment`.`assessment_pk` = :where_0 ","bindings":[{"name":":assessment","value":"

IMS1 Written Examination MCQ - Wk 5<\/p>","type":null},{"name":":weighting","value":"10%","type":null},{"name":":modified_by","value":"00082563","type":null},{"name":":unit_fk","value":["3"],"type":null},{"name":":where_0","value":"1","type":null}]},{"query":"DELETE FROM `assessment_assessment_type_lookup` WHERE `assessment_fk` = :where_0 ","bindings":[{"name":":where_0","value":"1","type":null}]},{"query":"INSERT INTO `assessment_assessment_type_lookup` ( `assessment_fk`, `assessment_type_fk` ) VALUES ( :assessment_fk, :assessment_type_fk )","bindings":[{"name":":assessment_fk","value":"1","type":null},{"name":":assessment_type_fk","value":"1","type":null}]},{"query":"DELETE FROM `unit_outcome_assessment_lookup` WHERE `assessment_fk` = :where_0 ","bindings":[{"name":":where_0","value":"1","type":null}]},{"query":"INSERT INTO `unit_outcome_assessment_lookup` ( `assessment_fk`, `unit_outcome_fk` ) VALUES ( :assessment_fk, :unit_outcome_fk )","bindings":[{"name":":assessment_fk","value":"1","type":null},{"name":":unit_outcome_fk","value":"93","type":null}]},{"query":"SELECT `assessment`.`assessment_pk` as 'assessment.assessment_pk', `assessment`.`assessment` as 'assessment.assessment', `assessment`.`weighting` as 'assessment.weighting', `assessment`.`modified` as 'assessment.modified', `assessment`.`modified_by` as 'assessment.modified_by', `assessment`.`unit_fk` as 'assessment.unit_fk', `unit`.`unit_name` as 'unit.unit_name' FROM `assessment` LEFT JOIN `unit` ON `unit`.`unit_pk` = `assessment`.`unit_fk` WHERE `assessment`.`assessment_pk` = :where_0 ","bindings":[{"name":":where_0","value":"1","type":null}]},{"query":"SELECT DISTINCT `assessment`.`assessment_pk` as 'dteditor_pkey', `assessment_type`.`assessment_type_pk` as 'assessment_type_pk', `assessment_type`.`assessment_type` as 'assessment_type' FROM assessment as assessment JOIN `assessment_assessment_type_lookup` ON `assessment`.`assessment_pk` = `assessment_assessment_type_lookup`.`assessment_fk` JOIN `assessment_type` ON `assessment_type`.`assessment_type_pk` = `assessment_assessment_type_lookup`.`assessment_type_fk` WHERE `assessment`.`assessment_pk` IN (:wherein1) ORDER BY `assessment_type`.`assessment_type` asc ","bindings":[{"name":":wherein1","value":"1","type":null}]},{"query":"SELECT DISTINCT `assessment_type_pk` as 'assessment_type_pk', `assessment_type` as 'assessment_type' FROM `assessment_type` ","bindings":[]},{"query":"SELECT DISTINCT `assessment`.`assessment_pk` as 'dteditor_pkey', `unit_outcome`.`unit_outcome_pk` as 'unit_outcome_pk', `unit_outcome`.`unit_outcome` as 'unit_outcome' FROM assessment as assessment JOIN `unit_outcome_assessment_lookup` ON `assessment`.`assessment_pk` = `unit_outcome_assessment_lookup`.`assessment_fk` JOIN `unit_outcome` ON `unit_outcome`.`unit_outcome_pk` = `unit_outcome_assessment_lookup`.`unit_outcome_fk` WHERE `assessment`.`assessment_pk` IN (:wherein1) ORDER BY `unit_outcome`.`unit_outcome` asc ","bindings":[{"name":":wherein1","value":"1","type":null}]},{"query":"SELECT DISTINCT `unit_outcome_pk` as 'unit_outcome_pk', `unit_outcome` as 'unit_outcome' FROM `unit_outcome` ","bindings":[]}]}

peterbrowne

Note that the record is created in the assessment table, but the assessment.unit_fk is not populated with the unit.unit_pk.

The records are created in the two lookup tables correctly.

The tables structure:

CREATE TABLE IF NOT EXISTS `assessment` (
  `assessment_pk` int(4) NOT NULL AUTO_INCREMENT,
  `assessment` text NOT NULL,
  `weighting` varchar(30) NOT NULL,
  `unit_fk` int(2) NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` varchar(10) NOT NULL,
  PRIMARY KEY (`assessment_pk`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;


CREATE TABLE IF NOT EXISTS `unit` (
  `unit_pk` int(11) NOT NULL AUTO_INCREMENT,
  `unit_code` varchar(20) NOT NULL,
  `unit_name` varchar(100) NOT NULL,
  `points` int(2) NOT NULL,
  `year_fk` int(1) NOT NULL,
  `assessment` text NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified_by` varchar(50) NOT NULL,
  PRIMARY KEY (`unit_pk`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;


CREATE TABLE IF NOT EXISTS `unit_outcome` (
  `unit_outcome_pk` int(4) NOT NULL AUTO_INCREMENT,
  `unit_outcome` text NOT NULL,
  `modified` datetime NOT NULL,
  `modified_by` varchar(10) NOT NULL,
  PRIMARY KEY (`unit_outcome_pk`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=123 ;


CREATE TABLE IF NOT EXISTS `unit_outcome_assessment_lookup` (
  `unit_outcome_assessment_lookup_pk` int(8) NOT NULL AUTO_INCREMENT,
  `unit_outcome_fk` int(4) NOT NULL,
  `assessment_fk` int(4) NOT NULL,
  PRIMARY KEY (`unit_outcome_assessment_lookup_pk`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;


CREATE TABLE IF NOT EXISTS `assessment_assessment_type_lookup` (
  `assessment_assessment_type_lookup_pk` int(12) NOT NULL AUTO_INCREMENT,
  `assessment_fk` int(2) NOT NULL,
  `assessment_type_fk` int(2) NOT NULL,
  PRIMARY KEY (`assessment_assessment_type_lookup_pk`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

peterbrowne

OK, I think I found the the problem, but not sure what exactly is causing the error:

If I change:

{
    label: "Unit:",
    name: "assessment.unit_fk",
    type: "select",
    placeholder: 'No selection',
    placeholderDisabled: false,
    placeholderValue: 0,
    multiple: true
                }

to:

{
    label: "Unit:",
    name: "assessment.unit_fk",
    type: "select"
                }

I don't get any errors and the assessment.unit_fk is populated correctly from unit.unit_pk and everything else is at it should be in the db.

peterbrowne

I actually don't want multiple selections anyway from the unit select...

allan

Ah I see - so on the assessment table there is a single unique reference to the unit table through unit_fk. In that case a leftJoin would be the way to do with with a select which is, as you say, not a multiple selection (single by default).

Allan