Answers

• peterbrowne Posts: 314Questions: 54Answers: 0

  October 2019 edited October 2019

  UPDATE

  OK, I looked at the documenation on using a linked table at:

  https://editor.datatables.net/examples/advanced/joinLinkTable.html

  So now I have the following which is not producing any errors and working correctly.

  Editor::inst( $db2, 'learning_event', 'learning_event_pk' )    
      ->field(
       Field::inst( 'learning_event.learning_event_name' ),
       Field::inst( 'learning_event.learning_event_outcome' ),
      Field::inst( 'presentation.presentation_name' ),
       Field::inst( 'learning_event.rdb_group_fk' )
              ->options( Options::inst()
                  ->table( 'rdb_group' )
                  ->value( 'rdb_group_pk' )
                  ->label( 'rdb_group_name' )
              )
              ->validator( 'Validate::notEmpty' ),
          Field::inst( 'rdb_group.rdb_group_name' ),
          Field::inst( 'learning_event.rotation_discipline_block_fk' )
              ->options( Options::inst()
                  ->table( 'rotation_discipline_block' )
                  ->value( 'rotation_discipline_block_pk' )
                  ->label( 'rotation_discipline_block_name' )
              )
              ->validator( 'Validate::dbValues' ),
          Field::inst( 'rotation_discipline_block.rotation_discipline_block_name' )
  
      )
      ->leftJoin( 'rdb_group', 'rdb_group.rdb_group_pk', '=', 'learning_event.rdb_group_fk' )
      ->leftJoin( 'rotation_discipline_block', 'rotation_discipline_block.rotation_discipline_block_pk', '=', 'learning_event.rotation_discipline_block_fk' )
      ->leftJoin( 'learning_event_presentation_lookup', 'learning_event.learning_event_pk', '=', 'learning_event_presentation_lookup.learning_event_fk' )
      ->leftJoin( 'presentation', 'learning_event_presentation_lookup.presentation_fk', '=', 'presentation.presentation_pk' )
      ->process($_POST)
      ->json();
  
• peterbrowne Posts: 314Questions: 54Answers: 0

  October 2019 edited October 2019

  Except that in the editor template, presentation.presentation_name is not showing in the select.

  

  with

  <div id='learning_event_table_container'>
              <table id='learning_event_table' class='display' style="width:100%">
                  <thead>
                      <tr>
                          <th>Learning Event</th>
                          <th>Learning Event Outcomes</th>
                          <th>Presentations</th>
                          <th>Rotation/Discipline/Block Sub-group</th>
                          <th>Rotation/Discipline/Block</th>
                      </tr>
                  </thead>
                  <tbody>
                  </tbody>
              </table>
          </div>
          <div id="learning_event_form">
              <editor-field name="learning_event.learning_event_name"></editor-field>
              <editor-field name="learning_event.learning_event_outcome"></editor-field>
              <editor-field name="presentation.presentation_name"></editor-field>
              <editor-field name="learning_event.rdb_group_fk"></editor-field>
              <editor-field name="learning_event.rotation_discipline_block_fk"></editor-field>
          </div>
  

  and

  var editor = new $.fn.dataTable.Editor( {
                  ajax: "program_data/learning_event_data.php",
                  table: "#learning_event_table",
                  template: '#learning_event_form',
                  fields: [ {
                      label: "Learning Event:",
                      name: "learning_event.learning_event_name"
                  }, {
                      label: "Learning Event Outcomes:",
                      name: "learning_event.learning_event_outcome",
                      type: "ckeditorClassic"
                  }, {
                      label: "Presentations:",
                      name: "presentation.presentation_name",
                      type: "select"
                  },{
                      label: "Rotation/Discipline/Block Sub-group:",
                      name: "learning_event.rdb_group_fk",
                      type: "select",
                      placeholder: 'No selection',
                      placeholderDisabled: false,
                      placeholderValue: 0
                  }, {
                      label: "Rotation/Discipline/Block:",
                      name: "learning_event.rotation_discipline_block_fk",
                      type: "select"
                  } ]
              } );
  
• peterbrowne Posts: 314Questions: 54Answers: 0

  October 2019 edited October 2019

  UPDATE 2

  Now using the following, which is good on the editor template side, but still can't get the names to show in the table for two columns from the link table:

  Editor::inst( $db2, 'learning_event', 'learning_event_pk' )    
      ->field(
       Field::inst( 'learning_event.learning_event_name' ),
       Field::inst( 'learning_event.learning_event_outcome' ),
      Field::inst( 'learning_event_presentation_lookup.presentation_fk' )
              ->options( Options::inst()
                  ->table( 'presentation' )
                  ->value( 'presentation_pk' )
                  ->label( 'presentation_name' )
              )
              ->validator( 'Validate::notEmpty' ),
          Field::inst( 'learning_event_mcondition_lookup.mcondition_fk' )
              ->options( Options::inst()
                  ->table( 'mcondition' )
                  ->value( 'mcondition_pk' )
                  ->label( 'mcondition_name' )
              )
              ->validator( 'Validate::notEmpty' ),
       Field::inst( 'learning_event.rdb_group_fk' )
              ->options( Options::inst()
                  ->table( 'rdb_group' )
                  ->value( 'rdb_group_pk' )
                  ->label( 'rdb_group_name' )
              )
              ->validator( 'Validate::notEmpty' ),
          Field::inst( 'rdb_group.rdb_group_name' ),
          Field::inst( 'learning_event.rotation_discipline_block_fk' )
              ->options( Options::inst()
                  ->table( 'rotation_discipline_block' )
                  ->value( 'rotation_discipline_block_pk' )
                  ->label( 'rotation_discipline_block_name' )
              )
              ->validator( 'Validate::dbValues' ),
          Field::inst( 'rotation_discipline_block.rotation_discipline_block_name' )
  
      )
      ->leftJoin( 'rdb_group', 'rdb_group.rdb_group_pk', '=', 'learning_event.rdb_group_fk' )
      ->leftJoin( 'rotation_discipline_block', 'rotation_discipline_block.rotation_discipline_block_pk', '=', 'learning_event.rotation_discipline_block_fk' )
      ->leftJoin( 'learning_event_presentation_lookup', 'learning_event.learning_event_pk', '=', 'learning_event_presentation_lookup.learning_event_fk' )
      ->leftJoin( 'presentation', 'learning_event_presentation_lookup.presentation_fk', '=', 'presentation.presentation_pk' )
      ->leftJoin( 'learning_event_mcondition_lookup', 'learning_event.learning_event_pk', '=', 'learning_event_mcondition_lookup.learning_event_fk' )
      ->leftJoin( 'mcondition', 'learning_event_mcondition_lookup.mcondition_fk', '=', 'mcondition.mcondition_pk' )
      ->process($_POST)
      ->json();
  

  and

          var editor = new $.fn.dataTable.Editor( {
                          ajax: "program_data/learning_event_data.php",
                          table: "#learning_event_table",
                          template: '#learning_event_form',
                          fields: [ {
                              label: "Learning Event:",
                              name: "learning_event.learning_event_name"
                          }, {
                              label: "Learning Event Outcomes:",
                              name: "learning_event.learning_event_outcome",
                              type: "ckeditorClassic"
                          }, {
                              label: "Presentations:",
                              name: "learning_event_presentation_lookup.presentation_fk",
                              type: "select",
                              placeholder: 'No selection',
                              placeholderDisabled: false,
                              placeholderValue: 0
                          }, {
                              label: "Conditions:",
                              name: "learning_event_mcondition_lookup.mcondition_fk",
                              type: "select",
                              placeholder: 'No selection',
                              placeholderDisabled: false,
                              placeholderValue: 0
                          }, {
                              label: "Rotation/Discipline/Block Sub-group:",
                              name: "learning_event.rdb_group_fk",
                              type: "select",
                              placeholder: 'No selection',
                              placeholderDisabled: false,
                              placeholderValue: 0
                          }, {
                              label: "Rotation/Discipline/Block:",
                              name: "learning_event.rotation_discipline_block_fk",
                              type: "select"
                          } ]
                      } );
  

  and

  $( '#learning_event_table' ).DataTable( {
                  ajax: "program_data/learning_event_data.php",
                  dom: "Bfrtip",
                  columns: [ {
                      data: "learning_event.learning_event_name"
                  }, {
                      data: "learning_event.learning_event_outcome"
                  }, {
                      data: "learning_event_presentation_lookup.presentation_fk"
                  }, {
                      data: "learning_event_mcondition_lookup.mcondition_fk"
                  },{
                      data: "rdb_group.rdb_group_name"
                  }, {
                      data: "rotation_discipline_block.rotation_discipline_block_name"
                  } ],
                  select: {
                      style: 'os',
                      selector: 'td:first-child'
                  },
                  buttons: [ {
                      extend: "create",
                      editor: editor
                  }, {
                      extend: "edit",
                      editor: editor
                  }, {
                      extend: "remove",
                      editor: editor
                  } ]
              } );
  

  However, in the table, only the ID (pk) of the prersentation and mcondition are showing, not the names. How do I get the names to display?

  

  In the Editor template everything shows OK:

  
• allan Posts: 61,650Questions: 1Answers: 10,094 Site admin

  October 2019 Answer ✓

  Going back to the original for a moment, the issue was with the use of

  ->leftJoin( 'learning_event', 'learning_event.learning_event_pk', '=', 'learning_event_presentation_lookup.learning_event_fk' )
  

  which is a self referencing join. That's possible, but you need to use an alias.

  However, in the table, only the ID (pk) of the prersentation and mcondition are showing, not the names. How do I get the names to display?

  You want to reference the joined data in your columns.data property. e.g.:

  data: 'presentation.presentation_name'
  

  note you'll need to add a Field::inst() on the server-side to get that field's data.

  Allan