ServerSide processing failed on search & PostgreSQL

cdr3934

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

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

I've committed the fix in here.

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

Allan

cdr3934

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

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

Allan

Shamsudin Bin Sulaiman

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

fahadcapt

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

colin

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

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

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

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

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

Allan

fahadcapt

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

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

allan

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

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

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

fahadcapt

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

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