Grid filtering isnull / isnotnull issue

2 posts, 0 answers
  1. Eric
    Eric avatar
    46 posts
    Member since:
    Aug 2012

    Posted 07 Mar 2018 Link to this post

    We are having an issue with our grid filtering when it comes to the "Is Null" and "Is Not Null" filters. We are filtering server side via the DataSourceResult.php file, when a column filter is applied with isnull or isnotnull we are getting a mySQL error:

    {"error":{"type":"PDOException","message":"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?)' at line 1","file":"\/vagrant\/pimcore-root\/website\/lib\/Kendo\/lib\/DataSourceResult.php","line":47}}

     

    Filter sent in the request:

     "filter":{"logic":"and","filters":[{"field":"ItemName","operator":"contains","value":"test"},{"field":"IsSpecialPrint","operator":"isnotnull","value":""}]}

     

    We're hitting the DataSourceResult result like in the example:

    $request = json_decode(file_get_contents('php://input'));
     $result = new DataSourceResult();

    Grid datasource JS:

    var gridDataSource = new kendo.data.DataSource({
                transport:{
                    read:{
                        url: "/gridRead",
                        type: "POST",
                        contentType: "application/json"
                    },
                    parameterMap: function(data){
                        return kendo.stringify(data);
                    },
                    prefix: ""
                },
                pageSize: 25,
                page: 1,
                total: 0,
                serverPaging: true,
                serverSorting: true,
                serverFiltering: true,
                serverGrouping: true,
                serverAggregates: true,
                sort: [{
                    field: "SKU",
                    dir: "asc"
                }],
                schema:{
                    data: "data",
                    total: "total",
                    errors: "errors",
                    model: {
                        id: "oo_id",
                        fields: schema
                    }
                }
            });

    This error seems to occur on all columns regardless if boolean/number/string.Could this be an error within the DataSourceResult.php file filtering logic or something with the encoding of filter values?

    We are using version R1 2017.

    Thanks.

  2. Stefan
    Admin
    Stefan avatar
    2525 posts

    Posted 09 Mar 2018 Link to this post

    Hello, Eric,

    I tested the "DataSourceResult.php" file and it was working as expected on my end. The is null and is not null filters are applied as expected.

    Please check the following demo and the content of the "DataSourceResult.php" file to observe if there are any changes to it compare to the one you are currently using:

    https://demos.telerik.com/php-ui/grid/remote-data-binding

    Also, please check if updating the Kendo UI version will fix the issue.

    If this still occurs, please provide an example when we can observe the generated MySQL query and advise further.

    Regards,
    Stefan
    Progress Telerik
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top