Answers

• allan Posts: 61,665Questions: 1Answers: 10,096 Site admin

  February 2018

  Hi,

  Could you show me the client-side code you are using to initialise both your DataTable and Editor please? Your server-side code looks okay there.

  Thanks,
  Allan
• bpitoni Posts: 21Questions: 7Answers: 2

  February 2018

  Hi Allan -

  Below is the client side code.

  I think the crux of the problem is that I am unsure (in the server side code) how to connect the mechanic and person tables (for a select dropdown) and the mech_coverage table (for the edit). Here is the client side code - I'll post a more complete server side code also.

  var mc_dash_editor; 
  
  $(document).ready(function() {
  
  mc_dash_editor = new $.fn.dataTable.Editor( {
              ajax: "./queries/mc_dashboard2.php",
              table: "#mc_datatable",
              fields: [           
                  {   
                      label: "Today's Mechanic",
                      name: "mech_coverage.today_mechanic",           
                      type: "select"              
                  }, {
                      label: "Notes",
                      name: "mechanic.notes",
                      type: "textarea",
                      placeholder: "",
                      placeholderDisabled: false              
                  }           
              ]
          }); 
  
  var mc_dash_table = $('#mc_datatable').DataTable( {         
              pageLength: 10,     
              order: [[0, "asc"]],
              stateSave: false,   
              dom: "Bfrtlip",                 
                  ajax: {
                  url: "./queries/mc_dashboard2.php", 
                  type: 'POST'
              },
          
      columns: [    
              //(0) craft
              { data: "crafts",
                  render: function( data, type, row ) {
                     return data.craft_name;
                  },
                  editField: "mechanic.craft_id" 
              },  
                          
              //(1)detail opener
              { className: "noselect details-control3",
                orderable: false,
                data: null,
                defaultContent: '' },           
              
              //(2)today's mechanic
              { data: 'person', className: 'bold',                      
                    render: function( data, type, row ) {
                  return data.last_name + ", " + data.first_name;
                },
            editField: "mech_coverage.today_mechanic" 
              },      
                
      //(3) buildings / areas
              { data: null,
                render: function(data, type, row) {             
                  var building_areas = '';
                       for (var i=0; i<row.building_areas.length; i++) {
                          var num_areas = row.building_areas.length;
                          var bldg_area_array = row.building_areas[i];
                          var bldg = ''; var area = '';
                          bldg = bldg_area_array.building_name;
                          if(bldg_area_array.area_name) { area = ': ' + bldg_area_array.area_name; }
                          building_areas = building_areas + bldg + area;
                          if(num_areas > 1) { building_areas = building_areas + ' / '; }
                      }
                      
                      return building_areas;              
                  }   
              },
  
              //(4) specialties           
              { data: "mechanic",
                render: function( data, type, row ) {
                return '';
                }         
             },
  
      //(5)days and hours
              { data: 'mechanic',
                render: function( data, type, row ) {
                  return data.days + " / " + data.shift_start + " - " + data.shift_end;
              } },       
  
      //(6) notes
              { data: 'mechanic.notes' }                  
          ],
          
          createdRow: function ( row, data, index ) {
              //if this mechanic is not a service mechanic, make row blue
              if( data['mechanic']['service_mechanic'] == 0) {
                  $(row).addClass('specialty_mech');              
              }               
          },
          select: {
              style: 'single',
              selector: 'td:not(.noselect)'           },
          buttons: [
              //{ extend: "create", editor: mc_dash_editor },
              { extend: "edit",   text: "Edit Row(s)", editor: mc_dash_editor },
              { extend: "selectNone", text: "Deselect Row(s)" },
              { extend: "refresh", text: "Refresh Table" }
              //{ extend: "remove", editor: mc_dash_editor }
          ]
  
      } );
  
• bpitoni Posts: 21Questions: 7Answers: 2

  February 2018

  And the complete server side code:

  Editor::inst( $db, 'mechanic', 'user_id' )  
      ->field(
          Field::inst( 'mechanic.days' ),
          Field::inst( 'mechanic.shift_start' ),
          Field::inst( 'mechanic.shift_end' ),
          Field::inst( 'mechanic.notes' ),
          Field::inst( 'mechanic.service_mechanic' ),
          Field::inst( 'mechanic.specials_mechanic' ),
          Field::inst( 'mechanic.craft_id' )
              ->options( Options::inst()
                  ->table('crafts') 
                  ->value('craft_id') 
                  ->label('craft_name')
                  ->where( function($q) {
                      $q->where( 'weekend', 0, '=')
                        ->where( 'craft_id', 186, '!=')
                        ->where( 'craft_id', 216, '!=')
                        ->where( 'craft_id', 446, '!=');                                                               
                  } ) 
              ),
      Field::inst( 'crafts.craft_name'),  
  
  //this is issue I think because I do not want to edit 'mechanic' table
  //but I can't figure out how to link to mech_coverage...
          Field::inst( 'mechanic.user_id' )       
          ->validator( 'Validate::required' )
          ->options( Options::inst()
              ->table('person') 
              ->value('user_id') 
              ->label( array('last_name', 'first_name') )
                  ->where( function($r) {
                      $r->where( 'disabled', 0, '=')
                      ->where( 'mechanic', 1, '=' )                     
                      ->where_group( function ($r) {
                        $r->where( 'campus', '%MC%', 'LIKE')
                        ->or_where('campus', 'ALL', '=');
                      } );                                                                 
                  } ) 
              ),
              
          Field::inst( 'person.user_id' ), 
          Field::inst( 'person.last_name' ),   
          Field::inst( 'person.first_name' ),
      Field::inst( 'person.work_phone' ),
          Field::inst( 'person.pager_number' ),
          Field::inst( 'person.work_cell' ),
          Field::inst( 'person.home_phone' ),
          Field::inst( 'person.home_cell' ),
          Field::inst( 'person.disabled' )
  
  ->leftJoin( 'person', 'person.user_id', '=', 'mechanic.user_id' )
  ->leftJoin( 'crafts', 'crafts.craft_id', '=', 'mechanic.craft_id' ) 
  ->join(
          Mjoin::inst('building_areas')
              ->link('mechanic.user_id', 'mech_coverage.today_mechanic')          
              ->link('building_areas.area_id', 'mech_coverage.building_area_id')
              ->fields(
              Field::inst( 'area_id' )
                      ->validator( 'Validate::required' )
                      ->options( Options::inst() 
                          ->table( 'building_areas' )
                          ->value( 'area_id' )
                          ->label( array('building_name', 'area_name') )                          
                      ),
                  Field::inst('building_name'),
                  Field::inst('area_name')
                      ->setFormatter( 'Format::ifEmpty', null )                                                       
              )
          )
  
  //limit to only those mechanics that are scheduled
  ->where( function($s) {
          $s->where('mechanic.user_id', "(SELECT DISTINCT today_mechanic FROM mech_coverage)", 'IN', false);
      })
  
• allan Posts: 61,665Questions: 1Answers: 10,096 Site admin

  February 2018

  You don't have a building_areas field in your Editor field list. I expected there to be something like the Permissions field in this example.

  Perhaps something like:

              {
                  "label": "Building areas:",
                  "name": "building_areas[].area_id",
                  "type": "checkbox"
              }
  

  or if you prefer a select input:

              {
                  "label": "Building areas:",
                  "name": "building_areas[].area_id",
                  "type": "select",
                  "multiple": true
              }
  

  Regards,
  Allan
• bpitoni Posts: 21Questions: 7Answers: 2

  February 2018

  Hi Allan - thank you for quick reply.

  But I do not want to edit the buildings_areas - I want to edit the other field in the link table (mech_coverage.today_mechanic) as a select input.

  Is that possible?

  I thought because Editor deletes old references before inserting new ones when updating one to many linked data, that it would be just as easy to update the "one" as it is to update the "many" - but maybe not...

  If not, any hints on an alternative approach?

  Thank you again
• allan Posts: 61,665Questions: 1Answers: 10,096 Site admin

  February 2018

  Yes, that is actually want Editor's Mjoin does - it doesn't alter the secondary joined table, but rather the link table. This example demonstrates that, and the documentation discusses it.

  Regards,
  Allan
• bpitoni Posts: 21Questions: 7Answers: 2

  February 2018

  Hi Allan, I am sorry, I am not sure if I'm not explaining well or if I just don't get it...

  I have read through all the documentation and examples several times, but, *** the examples show how to change the permissions of a user. But I want to change the user who has those permissions. ***

  I still want the datatable to show one user with multiple permissions - but the only edit I want to provide is the ability to edit the user.

  My own tables are slightly different obviously but the idea is the same.

  I'll keep plugging away at it, but might need to re-arrange my data if there's no easy way to do it using mjoin.

  Thank you by the way - I do appreciate all the responses.
• allan Posts: 61,665Questions: 1Answers: 10,096 Site admin

  February 2018

  I fear it might be me misunderstand - sorry. If we take the Mjoin example, and you want to edit the user information but not the permission information for that user, you'd drop the permissions information in the Javascript Editor (i.e. don't have that checkbox field). The user information would still be editable.

  Allan
• bpitoni Posts: 21Questions: 7Answers: 2

  February 2018

  Thanks Allan -

  Because I want to edit the "user_permission" table user_id and not the "user" table user_id, I ended up adding a third table (using your example it would be something like "substitute_staff" that holds only the regular user_id and the substitute_user_id.)

  The user_permissions table does not get edited at all, but I can show one user with multiple permissions and I can temporarily change the user by changing the user in the "substitute_staff" table.

  I can post some code if it would help someone else but I suspect I am the oddball case.

  Thank you for all your help with this - much appreciated.