Edit both fields of linked mjoin table?

bpitoni

I have a table structure similar to the example here: https://editor.datatables.net/manual/php/mjoin#Mjoin-class

My first table is "mechanics" with user_id and trade. The second table ("building_areas") uses building_area_id to identify the areas inside our buildings - it includes the building name and associated area name. The link table is "mechanic coverage" which is the mechanic id's and building_area id's. The mechanic id shows up several times in this table if the mechanic covers several areas.

I have successfully set up the mjoin and the table data is displaying as I had hoped. (The mechanic name, trade, and a concatenated list of the buildings and areas he/she covers). Picture attached.

My issue is that the field I would like to be able to edit (in the link table) is the mechanic id. When a mechanic calls in sick or has time off, a different mechanic covers those areas.

Editor::inst( $db, 'mechanic', 'user_id' )
->field(
        Field::inst( 'mechanic.days' ),
        Field::inst( 'mechanic.shift_start' ),
        Field::inst( 'mechanic.shift_end' ), ...
)
->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 )                                               
            )
        )

I would like to edit mech_coverage.today_mechanic - not the building areas but I haven't been able to figure out how to do it. Is there something simple I'm missing or should I have approached this a completely different way?

A couple of key features I'm hoping not to lose: mechanic shows up only once in display, building_areas are a concatenated list in one column.

Thank you for any help.

allan

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

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

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

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

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

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

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

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

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.