ServerSide processing failed on search & PostgreSQL

cdr3934 · May 2019

Serverside search (used example: /Editor-PHP-1.9.0/examples/simple/server-side-processing.html) against a PostgreSQL 11 Database failed when hitting other database field types than text or char (f.exe. timestamp or integer).

Error Message: An SQL error occured: SQLSTATE[42883]: Undefined function: 7 ERROR: Operator doest not exists: timestamp without time zone ~~* unknown

The problem is, that the query is build like:

.... OR start_date LIKE '%{search_value}%' ....

it is not possible to use SQL 'like' at timestamp, numeric values and bools etc.

Depending on the columns data type, other fields than text/char must be excluded when building the query ...

Another solution would be, the build the query this way:

start_date::text LIKE '%{search_value}%'

The following patch seems to work fine - between: I changed LIKE to ILIKE for case-insensitive searches ....

    1606,1607c1607,1609
    <                                               if ( $field) {
    <                                                       $q->or_where( $field."::TEXT", '%'.$http['search']['value'].'%', 'ilike' );
    ---
    >
    >                                               if ( $field ) {
    >                                                       $q->or_where( $field, '%'.$http['search']['value'].'%', 'like' );
    1643,1644c1645
    <                               $query->where( $this->_ssp_field( $http, $i )."::TEXT",
    <                                       '%'.$search.'%', 'ilike' );
    ---
    >                               $query->where( $this->_ssp_field( $http, $i ), '%'.$search.'%', 'like' );

allan · May 2019

Thanks for your workaround! What's interesting is that our unit tests aren't failing on this. Can I ask what version of Postgres you are using please?

Allan

allan · May 2019

I've committed the fix in here.

Apparently we weren't triggering that in the tests! They are there now though .

Allan

cdr3934 · May 2019

I can confirm that your patches https://github.com/DataTables/Editor-PHP/commit/258ecdeae191a7b98276834ec1964e2226c8a810 are performing fine for PostgreSQL. I checked against version 11 but they should work hasslefree for any other postgres version too. btw: they are not yet included in the PHP downloads: https://editor.datatables.net/download/index

allan · May 2019

Thanks . They will be available from the download page when we tag and release Editor 1.9.1.

Allan

Shamsudin Bin Sulaiman · July 2019

I have tested the patches on Postgres version 10, and it's perfectly works. Thanks Allan.

fahadcapt · April 2022

global search not working in Node.js for postgres version 13

colin · April 2022

Is this using our search scripts, or yours? Are you able to link to your page so we can take a look, please?

Colin

fahadcapt · April 2022

I am using Editor demo example downloaded from here: https://editor.datatables.net/download/
and migrate given SQL script for Postgres on my localhost, everything is working fine except search, because Postgres doesn't support like query for data and numeric values

allan · April 2022

When you say you migrated the demo SQL to Postgres - I'm not sure what you mean. We provide SQL specifically for Postgres.

I've just tried our examples for server-side processing with Editor in PHP with Postgres, searching on a number and it appears to work okay.

Can you give me some more detail about your setup; versions, error messages, etc please?

Allan

fahadcapt · April 2022

Im using your node.js code with provided SQL specifically for Postgres
and getting error on server-side-processing page search which is using /api/staff API, see the error below

Unhandled promise error:  [object Promise]error: select count("id") as "cnt" from "datatables_demo" where ("first_name" like $1 or "last_name" like $2 or "position" like $3 or "office" like $4 or "start_date" like $5) - operator does not exist: timestamp without time zone ~~ unknown
stack: error: select count("id") as "cnt" from "datatables_demo" where ("first_name" like $1 or "last_name" like $2 or "position" like $3 or "office" like $4 or "start_date" like $5) - operator does not exist: timestamp without time zone ~~ unknown
    at Parser.parseErrorMessage (D:\node projects\Editor-datatable\node_modules\pg-protocol\src\parser.ts:369:69)
    at Parser.handlePacket (D:\node projects\Editor-datatable\node_modules\pg-protocol\src\parser.ts:188:21)
    at Parser.parse (D:\node projects\Editor-datatable\node_modules\pg-protocol\src\parser.ts:103:30)
    at Socket.<anonymous> (D:\node projects\Editor-datatable\node_modules\pg-protocol\src\index.ts:7:48)
    at Socket.emit (events.js:315:20)
    at addChunk (internal/streams/readable.js:309:12)
    at readableAddChunk (internal/streams/readable.js:284:9)
    at Socket.Readable.push (internal/streams/readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)

allan · April 2022

This is with our Node.js demos (or possibly .NET, but I'm guessing node from the paths there)?

Allan

fahadcapt · April 2022

I'm attaching my complete code for datatable listing, and the things I required from the server are sorting, pagination, and global search from all fields.

1- I'm using "datatables.net-editor-server": "^2.0.7"
2- my Node.js version is 14
3- my Postgres version is 13
4- my API code for the data table is as follow:

let knex = require('../db');

let {
    Editor,
    Field,
    Validate,
    Format,
    Options
} = require("datatables.net-editor-server");

router.post("/getUserList", async function (req, res) {
   
    let editor = new Editor(knex, "Users").fields(
        new Field('id'),
        new Field('username'),
        new Field('email'),
        new Field('created_at')
        .getFormatter(Format.sqlDateToFormat('YYYY-MM-DD')),
    );

    await editor.process(req.body);
    res.status(200).json(editor.data());
})

but as I described earlier, I'm getting a server error on the search mentioned in the above comment,
Please suggest me the solution

Fahad

fahadcapt · April 2022

@allen, @colin guys do we have any solution to this?

allan · April 2022

Many thanks for the information. I'm seeing this as well with our Node application. Curious it doesn't happen in PHP... I'm looking into it now and will post back when I have an update.

For the moment, what you could do is add columns.searchable to the date columns so search is not performed on them. Not idea, but a possible workaround.

Allan

allan · April 2022

I've identified the issue now and patched the source here.

We'll get a patch release made soon to make this available via npm. In the meantime, if you replace the node_modules/datatables.net-editor-server/dist/editor.js file with this one that should allow it to work.

Allan

allan · April 2022

One additional thing - worth noting that since you are using server-side processing, it is only possible to search in ISO8601 format.

fahadcapt · April 2022

Thanks, @allen I got your point, I was using client name "postgresql" in my db connection, but the editor library set checks by comparing the client as a pg
so here is the quick solution:

I changed my db config in db.js file from

let dbConfig = {
    client: 'postgresql',

    connection: "postgres://postgres:12345@localhost:5432/todo",
};

to

let dbConfig = {
    client: 'pg',

    connection: "postgres://postgres:12345@localhost:5432/todo",
};

allan · April 2022

Yup - that will do it as well. I should have thought of suggesting that.I'm not sure if they both use pg under the hood in knex, but we'll support both forms going forward - pg will get it working for now though

Allan

ServerSide processing failed on search & PostgreSQL

ServerSide processing failed on search & PostgreSQL

Answers

Howdy, Stranger!

Categories

DataTables

ServerSide processing failed on search & PostgreSQL

ServerSide processing failed on search & PostgreSQL

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables