Select multiple options for a one-to-many relationship.

MickB

I have been following this:

https://editor.datatables.net/manual/php/mjoin

And my join is working correctly:

->join(
                Mjoin::inst( 'data_sources' )//table to link to
                ->link('widgets.id','data_source_widget.widget_id' ) //left table id, name in link table
                ->link('data_sources.id','data_source_widget.data_source_id' ) //right table id, name in link table

                ->order('data_sources.description asc')// order of joined data - optional
                ->fields(
                    Field::inst( 'id' ) //first field read from joined table
                    ->options( 'data_sources', 'id', 'description' )
                        ->validator( 'Validate::notEmpty' ),
                    Field::inst( 'description' ) //second field read from joined table
                )
            )

I have a widgets table and a data_sources table which are linked by a data_source_widget table.

I am working on the Widgets editor page. I have the an array of data_sourecs being displayed in my Datatable.

Now I want the Editor to display a select which lists the description field from the data_sources table and allows the user to be able to select multiple data sources.

I guess I need to add something like this (this is for a simple select I am doing, using a foreign key):

Field::inst("widgets.widget_type_id") //foreign key in this table
                ->options('widget_types','id','description') //linked table, key (id) , value (text to display)
                ->validator( 'Validate::dbValues' ),
                //this is needed to display it joined table.field_name
                Field::inst('widget_types.description')->validator('Validate::unique'),

Is there an example of this anywhere?

Mick

allan

Hi Mick,

It sounds like the one-to-many join example might be useful here.

Looking at the above, I suspect that your server is already returning the required list of options (if you could show me the returned JSON on data load I can confirm that). So what might possibly be missing is something on the client-side - are you using checkbox? That's the best field type for multiple selection items, although select can also be configured for it.

Regards,
Allan

MickB

Just looking at the AJAX request when I click Update. There isn't a data_source_widget element, this is the link table.

Should Editor be able to update the link table?

I have am able to display the array using checkboxes but when I choose a select, I am unable to select multiple.

This is what I added

    type:     'select',
    multiple: true,
    separator: ',',

I have uploaded this to the debugger, in case that helps. ediyoh

Mick

allan

Should Editor be able to update the link table?

Yes. What it will do is delete any existing records that link to the parent table (for that row!) and then insert the new links (rather than attempting to do a diff).

Could you show me your full Editor Javascript configuration please? Or even better, could you PM me a link to the page so I can debug it directly? The code you show above looks like it should work okay!

Thanks,
Allan

MickB

Sorry Allan, as usual, it's internal only.

I am setting Editor up like this (I have created a generic Vue component for my Datatable, which gets configured via AJAX):

 //setup the editor
       let editor = new $.fn.dataTable.Editor({
                 "ajax": {
                "url": self.dataUrl,
                "type": "POST",
                data: {table: self.table}
            },

    table: "#datatables-table",
    idSrc: self.table + '.id',
    fields: [self.editorFields]
        });

Those variables get assigned via this JSON.

{  
   "panelTitle":"Widgets",
   "table":"widgets",
   "tableHeaders":[  
      "",
      "Title",
      "Widget Type",
      "X",
      "Y",
      "Height",
      "Width",
      "Refresh Interval",
      "Data Sources"
   ],
   "editorFields":[  
      {  
         "label":"Title",
         "name":"widgets.title"
      },
      {  
         "label":"Type",
         "name":"widgets.widget_type_id",
         "type":"select",
         "placeholder":"Select A Widget Type"
      },
      {  
         "label":"X",
         "name":"widgets.default_x"
      },
      {  
         "label":"Y",
         "name":"widgets.default_y"
      },
      {  
         "label":"Height",
         "name":"widgets.default_height"
      },
      {  
         "label":"Width",
         "name":"widgets.default_width"
      },
      {  
         "label":"Refresh Interval",
         "name":"widgets.refresh_interval"
      },
      {  
         "label":"Data Source",
         "name":"data_sources[].id",
         "type":"checkbox",
         "multiple":"true",
         "separator":",",
         "placeholder":"Select A Data Source"
      }
   ],
   "tableColumns":[  
      {  
         "data":"null",
         "className":"select-checkbox",
         "orderable":false,
         "defaultContent":""
      },
      {  
         "data":"widgets.title"
      },
      {  
         "data":"widget_types.description",
         "editField":"widgets.widget_type_id"
      },
      {  
         "data":"widgets.default_x"
      },
      {  
         "data":"widgets.default_y"
      },
      {  
         "data":"widgets.default_height"
      },
      {  
         "data":"widgets.default_width"
      },
      {  
         "data":"widgets.refresh_interval"
      },
      {  
         "data":"data_sources",
         "render":"[,].description"
      }
   ]
}

allan

"multiple":"true",

Should be:

"multiple":true,

It expects a boolean value, not a string. When you do that it should show a multiple select box - is that the case?

Thanks,
Allan

allan

Oh and remove this:

"separator":",",

You want it to submit as an array, not a single string since you are using an Mjoin at the server-side.

When you do that, and select two items in this list, what is the data that is submitted to the server?

Thanks,
Allan

MickB

Multiple select now working!

Data now saving to the link table!

Brilliant.

Many thanks Allan.

Mick