SELECT type defaults

jmguillen

Hello,
I am using DT with some select type fields.
I can get the data from the database with (for example):

[code]
$.ajax({
url: "data_script.php",
type: "post",
data: {some: 'variables'}
}).done(function (response, textStatus, jqXHR){
editor.add({
"type": "select",
"label": "Office:",
"name": "OFFICE",
"ipOpts": response.data
});
})
[/code]

Everything ok with that.

Now the thing is that when editing the option is not selected on the editing form.
It comes with all select combo boxes on the first option of the list.

Reading the docs it seems that to do that I would need to do something like this:

[code]
{
"sClass": "center",
"mData": "done",
"mRender": function (val, type, row) {
return (val == 0) ? "To do" : "Done";
}
}
[/code]

Which seems odd to me. I would expect to get the default from the "label" that comes on the table data instead of matching the "value".
That is, IMHO, the most common case when dealing with relational tables as we do not show the "id" to the user but the label.

I am right this is the way it works?
Is there are way to make the default to match from label?

Thanks,
Juan Martín.

allan

Hi Juan,

I'd agree - using the id as the value and a label to show the user is the most common method of working with selects in Editor.

Is your Ajax call being fired off after the edit form is shown? If so you'll need to use the `set()` method to select the value. However, if it is being fired off before the edit is shown, it should work as expected. If that is the case, are you able to link me to the page so I can take a look and see what is going wrong?

Thanks,
Allan

jmguillen

Allan,
Here is the code. It is not working even without the AJAX call:

[code]
var editor;

$(document).ready(function() {
editor = new $.fn.dataTable.Editor({
"ajaxUrl": "Backend/DataTables/Edit.php",
"domTable": "#dtt",
"dbTable": "",
"fields": [
{
"label": "User Name:",
"name": "USERNAME"
},
{
"label": "Full Name:",
"name": "FULL_NAME"
},
{
"label": "email:",
"name": "EMAIL"
}, {
"label": "Language:",
"name": "LANGUAGE",
"type": "select",
"ipOpts": [{"label": "Español", "value": "2"},{"label": "Português", "value": "4"},{"label": "English", "value": "1"},{"label": "中文", "value": "3"}]
}
],
"events": {
"onPreSubmit": function (data) {
data["type"] = 'SQL';
data["process"] = 'grid_security_users';
data["binds"] = ['#ID', '_USERNAME', '_FULL_NAME', '_EMAIL', '_OFFICE', '_LANGUAGE', $('#tllan').val()];
}
},
});

var oTable = $('#dtt').dataTable({
"sDom": 'Tlfrtip',
"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "Backend/DataTables/Data.php",
"sServerMethod": "POST",
"fnServerParams": function (aoData) {
aoData.push(
{ "name": "sSQLFunction", "value": "db_app_security_users_sql" }
);
},
"aoColumns": [
{ "mData": "ID" },
{ "mData": "USERNAME" },
{ "mData": "FULL_NAME" },
{ "mData": "EMAIL" },
{ "mData": "LANGUAGE" }
],
"oTableTools": {
"sSwfPath": "plugins/tables/dataTables/swf/copy_csv_xls.swf",
"sRowSelect": "single",
"aButtons": [
"csv",
"print",
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor },
{ "sExtends": "text", "sButtonText": "Refresh", "fnClick": function(){ oTable.fnDraw(); } }
]
}
});
});
[/code]

Will try to make the page available on Internet.

Thanks,
Juan Martín.

jmguillen

In case it is worth, this is the JSON for the data:

[code]
{
"sEcho": 1,
"iTotalRecords": 4,
"iTotalDisplayRecords": 4,
"aaData": [
{
"DT_RowId": "row_1",
"ID": 1,
"USERNAME": "Alessandro",
"FULL_NAME": "Alessandro Del Piero",
"EMAIL": "adelpiero@mail.com",
"LANGUAGE": "English"
},
{
"DT_RowId": "row_2",
"ID": 2,
"USERNAME": "Gennaro",
"FULL_NAME": "Gennaro Gatusso",
"EMAIL": "gattuso@mail.com",
"LANGUAGE": "Español"
},
{
"DT_RowId": "row_61",
"ID": 61,
"USERNAME": "Andrea",
"FULL_NAME": "Andrea Pirlo",
"EMAIL": "fewfwe",
"LANGUAGE": "Português"
},
{
"DT_RowId": "row_99999",
"ID": 99999,
"USERNAME": "Alessandro",
"FULL_NAME": "Alessandro Nesta",
"EMAIL": "ererer",
"LANGUAGE": "中文"
}
]
}
[/code]

Juan Martín

allan

> "LANGUAGE": "Português"

But the value that Editor is seeing in the selector is an integer. That's way it isn't being selected.

What I would suggest, if it is possible, is for the JSON to include both the language name and the language id. The name would be used in the DataTables display, but the value would be used for Editor. Is it possible to make that change to include the language ID in the JSON?

Regards,
Allan

jmguillen

It may be possible, yes, I will try that.

But my point is that the default matching should be "by label", not "by id" as we show the label on the table 99% of the time.

The thing is that I can make it work but I would like a solution that does not mean to add another column for every select or having to create the "mRender" dynamically to make it work.

Is it possible to change the matching mechanism for every select?

Thanks,
Juan Martín.

allan

> But my point is that the default matching should be "by label", not "by id"

Oh I see. However, when you use val() on a select element, you get the value - not the label. Editor matches this behaviour - `get()` and `set()` work with the value.

Its possible to create a field type plug-in which will work with the label rather than the value, but wouldn't you just use the label as the value at that point?

Allan

jmguillen

[quote]Its possible to create a field type plug-in which will work with the label rather than the value, but wouldn't you just use the label as the value at that point?[/quote]

Not sure what you mean by "use the label as the value" but let me explain what I think as clear as I can so are we sure we are talking about the same thing.

It does make sense to get the value from val(), yes.
The way get() and set() work also make sense.
Even more, matching with the label instead of the value sounds weird at first.
After all the main duty of an ID is to match things.

But the thing is that editing things on a grid that gets data from more than one table makes it a particular (a) case where:

1) You show labels to the users, obviously, because they don't care (among other things) about the IDs on the tables. You use them for joining.

2) As a consequence of 1) you don't get the IDs to the grid because you actually don't need them there (b).

3) You get lookup values for select options with labels AND IDs because ID is what you end up inserting to the "main" (c) table.

4) At this point the only link you have between data on the grid and the lookup data for the select element is the label, not the id/value.

Again, there are workarounds that would make it work by getting the IDs for the lookup tables to the grid and/or creating data for id/value matching on the column definition, but they are more work and make it harder to create dynamic content for the lookups.

The easiest way I can see is to match by label by default.
It may look as an heterodox solution but the one that makes it simpler than others, on my opinion.

(a) Particular as in peculiar or complex, not uncommon. This would be, from my POV, the most common usage of grids getting data from databases.
(b) The most significant exception is the ID of the main table you need to edit the data.
(c) I am assuming that the case is one where a principal or main table has IDs to lookup tables, which should cover most uses of relational data.

Does this make sense?

Juan Martín.

allan

> 2) As a consequence of 1) you don't get the IDs to the grid because you actually don't need them there (b).

I agree with point 1, but not this point. I think you need the ID in the data source object for the row as well. Let's say, for example, you had two items for the `select` options list which had the same label (I know that normally this would be prevented, but it is a valid thing to do) - same label, but different IDs.

For this reason, you need to know the ID in the data source object as well. That ID allows you to pick the correct value from the select list, rather than just guessing which one is the correct one.

So, my view of it is that you need both the value and the label in the data source object (or at the very least, just the value and the label can be looked up using a function).

Allan

jmguillen

Allan,
As almost everything in general and a design desition in particular it is a matter of opinion.
If you ask me I would make it to match by label (a) and make it simple for both the data we get for the grid and the grid setup itself. Way easier.
That will work fine for most cases.
Of course you could have non unique labels (b) and a ton of other situations where that wont' work but they would be the exception, not the rule.
Again, this is opinable matter.
Thanks for your support,
Juan Martín.

(a) This is the approach other guys took like those from Dojo or jqGrid. On them field definition needs to be explicitly set to be by value/id, otherwise it is by label.
(b) Is is hard for me to find a valid reason for that to happen but eventually may happen, I agree.

jmguillen

Allan,
Finally we changed dataTables.editor.js:

[code]
for ( var i=0, iLen=this.s.fields.length ; i

allan

I think there are probably two options to get this working at the moment:

- Hack the code as you have done (although I would have one it in the `set` function for those two field types

- Create field type plug-ins which operate the way you want (this is the way I would recommend doing it). There is a tutorial on creating field type plug=ins here: https://editor.datatables.net/tutorials/field_types .

Personally I would always work with the id and convert to the label on display only - just like how I always work with UTC for dates and convert to local time only for display. However, every use case is different and your approach is of course absolutely valid :-). One simple use case where you'd want to pick by id rather than label is if you have two employees with the same name (for example).

However, thanks for your feedback - it is very much appreciated and I will take it on board when development Editor further.

Regards,
Allan