ServerSide processing failed on search & PostgreSQL

ServerSide processing failed on search & PostgreSQL

cdr3934cdr3934 Posts: 4Questions: 2Answers: 0

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' );

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    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

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin
    Answer ✓

    I've committed the fix in here.

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

    Allan

  • cdr3934cdr3934 Posts: 4Questions: 2Answers: 0

    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

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

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

    Allan

  • Shamsudin Bin SulaimanShamsudin Bin Sulaiman Posts: 1Questions: 0Answers: 0

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

  • fahadcaptfahadcapt Posts: 6Questions: 0Answers: 0

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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

    Colin

  • fahadcaptfahadcapt Posts: 6Questions: 0Answers: 0
    edited 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

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    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

  • fahadcaptfahadcapt Posts: 6Questions: 0Answers: 0

    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)
    
    
  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

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

    Allan

  • fahadcaptfahadcapt Posts: 6Questions: 0Answers: 0

    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

  • fahadcaptfahadcapt Posts: 6Questions: 0Answers: 0

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

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    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

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    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

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

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

  • fahadcaptfahadcapt Posts: 6Questions: 0Answers: 0
    edited 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",
    };
    
  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    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

Sign In or Register to comment.