Creating drop down menus with select2 from arrays

tablo

Hi all,

In a table I have a column which contains arrays of different sizes. It looks like this:

Col A
["a","b","c"]
["d","e"]
["f","g","h","i"]

How can I create drop down menus with select2 (multiselect) for this column?

allan

Do you mean you have different options for that field for each row? If so, use field().update() to update the options for the Select2 field.

Allan

tablo

@allan thanks for the response!

Do you mean you have different options for that field for each row?

Yes! But this looks like is fix. I mean each record in the table of the DB is an array and each one of them has different items. How can I make dropdowns for each row in the datatable out of these items?

Also, as I have seen select2 requires a specific format. How can I get each array of the DB, convert it to the required format and integrate it into DataTables/Editor? I don't understand how to proceed only by reading the manual:
https://editor.datatables.net/plug-ins/field-type/editor.select2

tablo

I forgot to mention that I'm using Node.js...

allan

field().update() is still the way to do it. You could use:

editor.on('initCreate initEdit', function () {
  var rowData = table.row({selected:true}).data();

  editor.field('mySelect2Field').update( rowData.myOptionsData );
});

where myOptionsData would be changed of course (and the field name). It will use label / value objects in an array by default.

Allan

tablo

@allan it's getting better...

I'm now able to see the list items in the Editor, one item in each row, but as soon as I try to select one of them and click Update I get an error which points me to this page:
https://datatables.net/manual/tech-notes/12

The error I see on the server terminal is this (same as in Browser/Dev Tools/Network/Preview):

2019-07-29T14:09:58+02:00 [info] : "::ffff:127.0.0.1 - - [29/Jul/2019:12:09:58 +0000] \"POST /api/v1/myapi HTTP/1.1\" 500 899\n"
error: invalid input syntax for type json
    at Connection.parseE (/home/user/node_modules/pg/lib/connection.js:604:11)
    at Connection.parseMessage (/home/user/node_modules/pg/lib/connection.js:401:19)
    at Socket.<anonymous> (/home/user/node_modules/pg/lib/connection.js:121:22)
    at Socket.emit (events.js:189:13)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)

When I output the myArray in the Browser console like this:

editor.on('initCreate initEdit', function () {
    var rowData = table.row({selected: true}).data();
    console.log(rowData["myTable"]["myArray"]);
    editor.field('myTable.myArray').update(rowData["myTable"]["myArray"]);
});

I can see myArray with all of its items (myArray = [item1, item2, ...]). But what about the data format of Select2? Shouldn't be like this?

[
    {
      "id": 1,
      "text": "item 1"
    },
    {
      "id": 2,
      "text": "item 2"
    }
  ]

tablo

Searching for the error I found threads referring to PostgreSQL. E.g.:

https://stackoverflow.com/questions/53599729/error-invalid-input-syntax-for-type-json

I have created the column in the DB Table as of type jsonb in order to get knex to work with the PostgreSQL Array.

tablo

I have tried with $.getJSON inside the editor.on('initCreate initEdit' block and I didn't see the previous error but still doesn't work.
Now 2 things come to my mind:
1. Do I need a junction table for this to work?
2. How can I get the selected elements from the dropdown menu?

allan

have created the column in the DB Table as of type jsonb in order to get knex to work with the PostgreSQL Array.

Another option would be to store the data as a comma separated string in a text field. You can have the Select2 plug-in for Editor work with character separated strings by using its separator option - e.g. separator: ','.

The disadvantage of this method is that you don't have data referential integrity in the database. For that you would need a junction table as you say.

1. How can I get the selected elements from the dropdown menu?

The Select2 docs say:

Calling select2('data') will return a JavaScript array of objects representing the current selection.

So you could do: editor.field('myField').input().select2('data'). Or to just get the value(s) use editor.field('myField').val().

Allan

tablo

@allan Thanks for the answers!

So if jsonb is OK, having arrays in the columns is OK and I don't need necessarily a junction table, then why I'm getting the error:
error: invalid input syntax for type json

I've created a new simple project with Generator, where one column of the DB Table id of type jsonb, to check the problem.
The JSON from the API looks like this:

{
    "data": [{
        "DT_RowId": "row_1",
        "field1": "fruits",
        "field2": [
            "Apple",
            "Orange"
        ]
    }],
    "fieldErrors": [],
    "files": {},
    "options": {}
}

and DataTables works fine, I can see the items of list ("Apple", "Orange") one in each row. As soon as I try to edit or create a new record in this field I'm getting this error:

Unhandled promise error:  [object Promise]error: insert into "myTable" ("field1", "field2") values ($1, $2) returning "id" - invalid input syntax for type json
stack: error: invalid input syntax for type json
    at Connection.parseE (/home/user/DataTablesEditor_Generator_20190730b/node_modules/pg/lib/connection.js:604:11)
    at Connection.parseMessage (/home/user/DataTablesEditor_Generator_20190730b/node_modules/pg/lib/connection.js:401:19)
    at Socket.<anonymous> (/home/user/DataTablesEditor_Generator_20190730b/node_modules/pg/lib/connection.js:121:22)
    at Socket.emit (events.js:189:13)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)

Could you please check what is going with jsonb in PostgreSQL and Editor?

allan

error: invalid input syntax for type json

Ah! I think I hit that just a little while ago while working with JSON objects that had top level arrays with Knex and Postgres.

My solution there was to use a setFortmatter to convert the array to a string. Postgres will see that and work with it fine, and likewise when loading it node-pg will give it as an array. So try:

new Field("MyField")
    .setFormatter( (val, data) => JSON.stringify(val) )
)

Allan

tablo

My solution there was to use a setFortmatter to convert the array to a string

@allan thanks! I can write now in the column, but what is the meaning of having it in jsonb format when the Array is being converted into a String, like this:

["Apple", "Orange"] -> "Apple,Orange"

Besides that, you mentioned this before:

Another option would be to store the data as a comma separated string in a text field. You can have the Select2 plug-in for Editor work with character separated strings by using its separator option - e.g. separator: ','.

The disadvantage of this method is that you don't have data referential integrity in the database. For that you would need a junction table as you say.

So, what should I do in order to have Select2 properly working? Is it better to create the column of type Text right from the beginning or jsonb, although the items will be converted into a string?

allan

but what is the meaning of having it in jsonb format when the Array is being converted into a String, like this:

If its a jsonb column, it should actually take a string such as "['Apple','Orange']" and just store it as JSON.

So, what should I do in order to have Select2 properly working?

To simply get things working in the shortest amount of time possible, I'd suggest using a text column, remove the setFormatter and add separator: ',' to the field configuration for the Select2 field.

That will store the value(s) selected, comma separated as a string.

Allan

tablo

To simply get things working in the shortest amount of time possible, I'd suggest using a text column, remove the setFormatter and add separator: ',' to the field configuration for the Select2 field.

I was thinking the same...
Many thanks!