Error when trying to use column based on field using 'SQL functions' / ServerSide / NodeJS
Error when trying to use column based on field using 'SQL functions' / ServerSide / NodeJS
MarkAndersonUK
Posts: 44Questions: 17Answers: 0
Hi,
When I attempt to use an 'SQL function' to define a field, it appears to work fine, the value is returned
in my AJAX data. However, when I reference that field as the data source for a column, I get the following error:
Uncaught TypeError: Cannot read properties of undefined (reading 'style')
at Ya (jquery.dataTables.min.js:76)
at Ba (jquery.dataTables.min.js:62)
at f (jquery.dataTables.min.js:107)
at HTMLTableElement.<anonymous> (jquery.dataTables.min.js:108)
at Function.each (jquery.min.js:2)
at k.fn.init.each (jquery.min.js:2)
at k.fn.init.u [as dataTable] (jquery.dataTables.min.js:98)
at k.fn.init.l.fn.DataTable (jquery.dataTables.min.js:187)
at HTMLDocument.<anonymous> (Contacts:79)
at e (jquery.min.js:2)
Here is a copy of my NodeJS controller :
router.all('/api/clients', async function(req, res) {
let editor = new Editor(ot198, 'clients', 'clients.Id')
.fields(
new Field("clients.Id"),
new Field("clients.infusionsoft_id"),
new Field("clients.AgentId"),
new Field("agent2.FirstName"),
new Field("agent2.LastName"),
new Field("clients.FirstName"),
new Field("clients.MiddleName"),
new Field("clients.LastName"),
new Field("concat_ws(' ',clients.FirstName,clients.MiddleName,clients.LastName)", "clients.FullName" )
.set( false ),
new Field("clients.EMail1"),
new Field("clients.HomeState"),
new Field("clients.HomeZipCode"),
)
.leftJoin( 'compass.agent as agent2', function () {
this.on('agent2.AgentReference', '=', 'clients.AgentId')
})
await editor.process(req.body);
res.json(editor.data());
});
The Javascript for the page:
var clients_table = $('#clients').DataTable({
dom: 'Blfrtip',
ajax: {
url: '/Contacts/api/clients',
type: "POST"
},
serverSide: true,
processing: false,
columns: [
{data: "clients.Id"},
{data: "clients.infusionsoft_id"},
{data: "clients.AgentId"},
{data: null,
editField: ['clients.FirstName', 'clients.MiddleName', 'clients.LastName'],
className: 'bbledit',
"searchable": false,
},
{data: "clients.FullName"},
{data: "clients.EMail1",
render: function (toFormat) {
var tEmail;
tEmail = toFormat.toString();
tEmail = '<a href="mailto:' + tEmail + '" target="_blank">' + tEmail + '</a>';
return tEmail
}},
{data: "clients.HomeState"},
{data: "clients.HomeZipCode"},
],
paging: true,
fixedColumns: true,
lengthChange: true,
lengthMenu: [ [15, 30, 50, -1], [15, 30, 50, "All"] ],
searching: true,
colReorder: true,
responsive: true,
buttons: [
{ extend: "create", editor: editor },
{ extend: "edit", editor: editor },
{ extend: "remove", editor: editor }
],
select: true,
});
If I remove the columns entirely, the page loads and I am able to capture the AJAX data, and the column does exist, and does have the correct data in it, here is an extract of one of the records:
"data": [
{
"DT_RowId": "row_1",
"clients": {
"Id": 1,
"infusionsoft_id": 3832,
"AgentId": 161,
"FirstName": "Brenda",
"MiddleName": "",
"LastName": "Smith",
"LastName2": "Smith",
"FullName": "Brenda Smith",
"EMail1": "Smith@mail.com",
"HomeState": "UT",
"HomeZipCode": "84095"
},
"agent2": {
"FirstName": "Shem",
"LastName": "Barlow"
}
},
If anyone is interested, the reason I am trying to load the name fields a second time, is to allow the search function to to work on the name fields, as I am using a multi-field bubble edit on the name, which causes the serverside search to fail, so the intention is to load the name fields a second time (in a single CONCAT_WS field) and make that field hidden, thus allowing the search on those fields to still work. If there is a simpler way, I would love to hear about it.
At all appears to be working just as I imagined, all but the error message I get when I try and add the field to a column.
Any help would be most appreciated.
Thanks !!
This question has an accepted answers - jump to answer
Answers
This error typically means there is a mismatch in the number of columns defined in HTML and in Datatables. Looks like you have 8 columns defined in Datatables. Do you have 8 defined in HTML?
Kevin
Thanks @kthorngren,
You are correct, adding the HTML appears to fix the initial problem, however, it created another problem, when I try and perform a search, I am getting an SQL Error now, and it is really strange, the SQL looks invalid. Take a look :
The search term I typed is :
andersonHere is the SQL that is sent to the server :
The SQL is invalid, around the SQL Statement section, the ` characters are
placed wrongly, the corrected SQL should be :
Replace :
With :
However, I cannot find a way to correct this myself.
Finally, the error is :
Hi @MarkAndersonUK ,
What version of the editor libraries are you using to produce this? It would be worth updating to the latest version and seeing if it occurs there.
On another note, it is actually possible to avoid this by combining the fields on the client side rather than the server side by using a renderer. You can see an example of how to do this here.
Thanks,
Sandy
Hi @sandy,
The libraries I am using are :
jquery.dataTables.min.js - 1.11.3
dataTables.editor.min.js - 2.0.5
I tried loading using npm, but using NodeJS, I just get errors
when I tried to follow the NPM instructions, I recall it was
'ReferenceError : window is not defined at object. ' when trying to use it. (I read that this is because window does not exist in NodeJS), I searched the forum but could not find anyone with a working example using NPM and NodeJS.
I will try the renderer method and see it if fixed the issues, as I am just having an awful time getting this to work nice.. The latest thing is I can't get the basic search to to work in ServerSide mode.. For example, I have a name split into multiple fields (first, middle and last), in Local mode, the search is able to find records if I type the name 'Bob Smith' (for example), but in serverside, it does not find the records.. The search function appears to work quite differently and so far I cannot figure a way around it.. I don't want to confuse this discussion, so I will create a new discussion about it... Thanks for you help.
Hi @sandy, I tried using the locally rendered method to join the fields, but
this does not solve the problem as the problem is when we search, the SQL produced was invalid. While that no longer happens, we are not able to include the locally rendered fields at all in a search, if we try the following error occurs :
I need to create the concatenated fields on the server-side, so they are included in the basic search function, as I displayed earlier when I do that, the SQL being produced by the search function is invalid, and the ** ` ** characters are placed wrongly or are missing in required places. That error is :
(full error details are in my earlier post)
How can I fix this invalid SQL? is there a way that I can override the SQL generated? If not, do you have any suggestions? If this is a bug, do I have to post a report somewhere, other than this forum post, to get it addressed?
I appreciate all your help and advice.
Hi,
Sandy asked if I could jump in here and take a look at this. Apologies you are running into this issue. I've been looking at our code base, and I'm afraid that at the moment we do a really horrible job of handling the concat function (or almost any function) due to how our aliasing of column names work. That is an error and something we need to address.
However, I do have a workaround I think - drop the concat on the server-side and do that rendering entirely on the client-side. The search should then work correctly. Use something like:
Note that if you do a column search, it will be based on the
FirstName- likewise the sorting. Global search will work across them all.If that is a problem, I think a VIEW would be the only workaround at the moment. That would encapsulate the concat at the server-side, so Editor just sees a regular table to read from.
Sorry I don't have a better solution at the moment. It is going to involve us changing how we index the database parameters internally.
Allan
Hi @allan,
I think I found a solution that works for me, like you suggested, I used a view to concat the fields on the server. I created some hidden cols from this view, that contain the full name (without middlename) and full name (with middlename). This allows the basic search box to find the records based on any name combination.
At first I tried to use the 'readTable' option to select a view, but when using serverside, that gave me a lot of errors, where it could not save anything, despite using the 'editField' option to set the field and including the table names. I just could not get it to work at all. So I finalized, on using one table for the main read/write data, and a self-referencing joined table for all of the search fields I wanted.
I wanted to thank both you (@allan) and @sandy for all your help. I love the editor, and am now another step closer to finishing my project. THANKS
This worked for me, and perhaps might work for others using a similar setup, so here is what I basically ended up with (I removed fields and options to make this example simpler) :
**NodeJS Controller Extract: **
**The Javascript section: **
**The VIEW on the SQL : **
That is excellent - delighted to hear you've got this up and running as you need now. Thank you for your kind words, and also highlighting an area where we need to improve things. I had knowing the indexing of our db parameters was a bit of an issue, before, but I can see it is actually more of a problem than I'd thought of before. Going to be looking at addressing that for 2.1.
Regards,
Allan