Multiple row insert based on multiple select inputs
Multiple row insert based on multiple select inputs
I have an interesting scenario where I would like to perform one or more inserts simultaneously, depending on the number of options selected within two multiple select inputs (we'll call them Input A and Input B ). I know the multiple option is available for the select field type, so I'm OK with setting up the Editor form. However, I do not want to insert an array of data (i.e., all of the selected options) into a single row. Rather, I want to create a new row for each selected option in Input A. I want Input B to have the same functionality. The result should be an insert of X rows , where X is the product of the number of options selected in Input A and Input B.
Example:
Input A selected options: 1, 2, 3
Input B selected options: a, b, c
3 selected options in Input A * 3 selected options in Input B should result in 9 newly inserted rows:
Row | Input A Destination Column | Input B Destination Column
-------------------------------------------------------------
1 | 1 | a
2 | 1 | b
3 | 1 | c
4 | 2 | a
5 | 2 | b
6 | 2 | c
7 | 3 | a
8 | 3 | b
9 | 3 | c
Maybe there is a way to do this via rows.add()
. Would this involve modifying the server side PHP script (I am using server-side processing)? I understand this functionality may not be part of the core DataTables code, but I was primarily looking for some direction on how to achieve something like this.
Edit: I found this page, which states:
Create new rows
Multi-row actions are limited to only editing in Editor, 1.0+ has supported multi-row deletion, but alongside the multi-row editing of 1.5 it is also possible to create multiple rows with a single call. The number of rows to be created is defined by the optional count parameter given to the create() method.When using multi-row editing field values can be addressed individually through the row id, as discussed above - however, when creating new rows the rows do not have ids assigned until submitted to the server, so the rows are assigned indexes 0...N where N is the number of rows being created.
When creating multiple rows the UI behaviour is identical to the edit behaviour - i.e. input values via the UI are assigned to all items being created. Therefore, the API is far more likely to be useful when creating multiple row.
With that stated, I should be able to loop through my selected items and build a multi-row insert using Editor's create() API? Are there any examples I could look at where someone has done something similar? I didn't find anything that fit my exact use case in the Examples section.
Thanks in advance!
This question has an accepted answers - jump to answer
Answers
That, I'm sorry to say, is not a feature that is provided by the Editor PHP and .NET libraries. The multi-row create information that you mention above allows you to create multiple rows, but as it also says, it is virtually useless via the UI since all rows would contain the same values.
A multi-value select element is considered to be a single value in terms of the Editor form and would be inserted into a single row (with the exception of a one-to-many join - but that still needs a single parent row).
If you need multiple rows to be created based on the values in a multi-select element there are two options I can think of:
Either way, I think there is going to be a little bit of work that needs to be done .
Allan
Thanks for the quick reply, Allan. I think I'll go with option 2, as I have already written both scripts to insert multiple rows into a table with distinct values from a form. I should still be able to access the values in the Editor via some event handler like preSubmit, then split the arrays into distinct insert values, like this:
Thanks for confirming what I already thought though!
Yes you could do it that way. Although personally I think I'd probably do the split at the server-side rather than the client.
I don't have a good reason for that though! Just a gut feeling .
Allan
The only reason I decided to do client-side data manipulation is because of this statement as the purpose of the preSubmit event:
Doing this allows me to split the array of values for the two multiple select inputs into their own objects, with other attributes (which are the same for all inserted rows), and pass them in an object which is easily consumable by a server-side script:
On the server:
One other thing I will mention here is that separate Editor instances should be used: one for create (with the multiple select dropdown(s)) and one for edit/delete (with single select dropdowns). The concept of multiple select does not really apply in an editing context, and you may run into some errors if you try to edit a single row which has multiple select inputs, as your destination table will be expecting a single string or integer or whatever and you are passing an array of values, which is likely not the intended behavior. In your Datatables initialization, make sure the buttons refer to the appropriate Editor instance, e.g.:
Confirmed the rows were created successfully in the destination table. But there are many ways to skin this cat, as you mentioned earlier. Hopefully this example can help anyone else trying to achieve something similar!
Awesome - thanks for posting your code. I'm sure others will find it useful!
Allan