Grid PHP addFilterItem method

[Robert]

Hello

 

I've been looking for the solution for this and still empty.

I'm trying to add a parameter to a filter when it loads and I'm doing what It's supposed to be the correct option but no luck for me.

Some one who could help me?

01.$DateField = new \Kendo\Data\DataSourceSchemaModelField('date');
02.$DateField->type('date');
03.
04.$dataSource = new \Kendo\Data\DataSource('dataSource');
05.
06.$filterItem = new \Kendo\Data\DataSourceFilterItem();
07.$filterItem->field('DateField');
08.$filterItem->operator('eq');
09.$filterItem->value(date('d-m-Y'));
10.
11.$dataSource->transport($transport)
12.                     ->pageSize(10)
13.                     ->schema($schema)
14.                     ->serverPaging(true)
15.                     ->addFilterItem($filterItem);
16.
17.$Date = new \Kendo\UI\GridColumn();
18.$Date->field('date')
19.          ->title(ucfirst(trans('labels.date')))
20.          ->format('{0:dd/MM/yyyy}');

[Viktor Tachev]

Hi Robert,

I have examined the provided code and noticed that addFilterItem is called for the transport. Note that the method should be called for DataSource. 

• http://docs.telerik.com/kendo-ui/api/php/Kendo/Data/DataSource#methods-addFilterItem

Regards,
Viktor Tachev
Progress Telerik

[Robert]

Hello Viktor.

 

I'm not sure if I get, could you give me an example?

I'm not seen it clear enough in the link you shared.

Thanks

 

[Viktor Tachev]

Hi Robert,

The code snippet below illustrates the approach:

$dataSource = new \Kendo\Data\DataSource('dataSource');

$filterItem = new \Kendo\Data\DataSourceFilterItem();
$filterItem->field('DateField');

$dataSource->addFilterItem($filterItem);

Regards,
Viktor Tachev
Progress Telerik

[Robert]

Hello Viktor.

I'm getting an error of syntax but I don't know why.

Here is my code and the error.

Parse error: syntax error, unexpected '(', expecting identifier (T_STRING) or variable (T_VARIABLE) or '{' or '$' (View: /test/resources/views/serviceRequests/index.blade.php)

$dataSource = new \Kendo\Data\DataSource('dataSource');
               $filterItem = new \Kendo\Data\DataSourceFilterItem('filterItem');
               $filterItem->('DateField');
               $filterItem->operator('eq');
               $filterItem->value(date('d-m-Y'));

               $dataSource->transport($transport)
                       ->pageSize(10)
                       ->schema($schema)
                       ->serverPaging(true)
                       ->serverSorting(true)
                       ->serverFiltering(true);



               $dataSource->addFilterItem($filterItem);

[Viktor Tachev]

Hi Robert,

Please ensure that you call the field method when setting the field for the filter item:

$filterItem = new \Kendo\Data\DataSourceFilterItem();
$filterItem->field('DateField');

Regards,
Viktor Tachev
Progress Telerik

[Robert]

Hello.

I'm sorry I didn't see it.

Try It and still don't managed to load the filter with the data.

Here is what I did:

I did a little change with the way I pass the date after I tried the traditional way $filterItem->value(date('d-m-Y')); and didn't worked.

$dataSource = new \Kendo\Data\DataSource();
$dataSource->transport($transport)
                     ->pageSize(10)
                     ->schema($schema)
                     ->serverPaging(true)
                     ->serverFiltering(true);


$today = date('d-m-Y');
$filterItem = new \Kendo\Data\DataSourceFilterItem();
$filterItem->field('DateField');
$filterItem->operator('eq');
$filterItem->value($today);

$dataSource->addFilterItem($filterItem);

[Konstantin Dikov]

Hi Robert,

Could you please elaborate what is the result after applying the initial filter? If the Grid is empty this might indicate that there are no records with such date in the data source and it will be expected to see no result. If that is the case, you could try to use a different operator, just to see if the filtering is working (like gt or gte for example).

Looking forward to your reply with the result.


Regards,
Konstantin Dikov
Progress Telerik

[Robert]

Hello

I tried but nothing again, here is my code and now I' getting an error.

Also I wrote a console log to the $schema and attached the output.

$schema->parse(new \Kendo\JavaScriptFunction(
 'function(response) {
        console.log(response);
    return JSON.parse(response);
 }'
));

$filterItem = new \Kendo\Data\DataSourceFilterItem();
$filterItem->field('date');
$filterItem->operator('gt');
$filterItem->value(date('d-m-Y'));

[Konstantin Dikov]

Hi Robert,

Could you please provide information on the error that you are receiving? Thus we might be able to pinpoint the root of the problem.


Regards,
Konstantin Dikov
Progress Telerik

[Robert]

Hello

I tried some more digging and this is what I found.
this is my grid data and the data from the filters It's there.

Grid {#918 ▼
  #id: "grid"
  -attributes: array:1 [▶]
  -isClientTemplate: false
  -properties: array:12 [▼
    "excelExport" => JavaScriptFunction {#935 ▶}
    "pdfExport" => JavaScriptFunction {#939 ▶}
    "columns" => array:11 [▶]
    "dataSource" => array:6 [▼
      "transport" => array:2 [▶]
      "pageSize" => 11
      "schema" => array:6 [▶]
      "serverPaging" => true
      "serverFiltering" => true
      "filter" => array:1 [▼
        0 => DataSourceFilterItem {#917 ▼
          -properties: array:2 [▼
            "logic" => "and"
            "filters" => array:2 [▼
              0 => DataSourceFilterItem {#913 ▼
                -properties: array:3 [▼
                  "field" => "date"
                  "operator" => "gt"
                  "value" => "01/09/2017"
                ]
                #ignore: []
              }
              1 => DataSourceFilterItem {#915 ▼
                -properties: array:3 [▼
                  "field" => "date"
                  "operator" => "lt"
                  "value" => "15/09/2017"
                ]
                #ignore: []
              }
            ]
          ]
          #ignore: []
        }
      ]
    ]
    "scrollable" => true
    "sortable" => true
    "filterable" => true
    "pageable" => true
    "toolbar" => array:2 [▶]
    "excel" => array:2 [▶]
    "pdf" => array:10 [▶]
    "dataBound" => JavaScriptFunction {#940 ▶}
  ]
  #ignore: []
}

 

 

The error I'm getting is this one :

 

{"error":0,"message":"PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"}

 

 

After diggin some more, I came with this:

"SELECT * FROM receptionIndexView WHERE ( AND )"

 

 

This after vieving the $sql from the function used to get the data from DataSourceResult.php in the function read()

public function read($table, $properties, $request = null) {

        $result = array();

        $propertyNames = $this->propertyNames($properties);

        $result['total'] = $this->total($table, $properties, $request);

        $sql = sprintf('SELECT %s FROM %s', implode(', ', $propertyNames), $table);

        if (isset($request->filter)) {
            $sql .= $this->filter($properties, $request->filter);
        }
        dd($sql);
        $sort = $this->mergeSortDescriptors($request);

        if (count($sort) > 0) {
            $sql .= $this->sort($propertyNames, $sort);
        }

        if (isset($request->skip) && isset($request->take)) {
            $sql .= $this->page();
        }

        $statement = $this->db->prepare($sql);

        if (isset($request->filter)) {
            $this->bindFilterValues($statement, $request->filter);
        }

        if (isset($request->skip) && isset($request->take)) {
            $statement->bindValue(':skip', (int)$request->skip, PDO::PARAM_INT);
            $statement->bindValue(':take', (int)$request->take, PDO::PARAM_INT);
        }

        $statement->execute();

        $data = $statement->fetchAll(PDO::FETCH_ASSOC);

        if (isset($request->group) && count($request->group) > 0) {
            $data = $this->group($data, $request->group, $table, $request, $propertyNames);
            $result['groups'] = $data;
        } else {
            $result['data'] = $data;
        }

        if (isset($request->aggregate)) {
            $result["aggregates"] = $this->calculateAggregates($table, $request->aggregate, $request, $propertyNames);
        }

        return $result;
    }

    public function readJoin($table, $joinTable, $properties, $key, $column, $request = null) {
        $result = $this->read($table, $properties, $request);

        for ($index = 0, $count = count($result['data']); $index < $count; $index++) {
            $sql = sprintf('SELECT %s FROM %s WHERE %s = %s', $column, $joinTable, $key, $result['data'][$index][$key]);

            $statement = $this->db->prepare($sql);
            $statement->execute();
            $data = $statement->fetchAll(PDO::FETCH_NUM);
            $result['data'][$index]['Attendees'] = $data;
        }

        return $result;
    }

 

 

So something is wrong either with the data on I'm passing to the filters or with the way the "WHERE" is translating this data, I can modify the /telerik/src/telerik/DataSourceResult.php file but I think this should work out of th box.
I'm waiting for your help.
This is my code.

 

$dataSource = new \Kendo\Data\DataSource();

$filterItem1 = new \Kendo\Data\DataSourceFilterItem();
$filterItem1->field('date');
$filterItem1->operator('gt');
$date1 = DateTime::createFromFormat('j-M-Y', '1-Sep-2017');
$filterItem1->value(date_format($date1, 'd/m/Y'));

$filterItem2 = new \Kendo\Data\DataSourceFilterItem();
$filterItem2->field('date');
$filterItem2->operator('lt');
$date2 = DateTime::createFromFormat('j-M-Y', '15-Sep-2017');
$filterItem2->value(date_format($date2, 'd/m/Y'));

$filterArray = array($filterItem1, $filterItem2);
$filterItem = new \Kendo\Data\DataSourceFilterItem();
$filterItem->logic('and');
 $filterItem->filters($filterArray);

$dataSource->transport($transport)
           ->pageSize(11)
           ->schema($schema)
           ->serverPaging(true)
           ->serverFiltering(true)
           ->addFilterItem($filterItem);

[Konstantin Dikov]

Hello Robert,

We will try to create a project locally and use your logic for the initial filter for the dates, but meanwhile, could you please try to include multiple filters for other fields (not dates) and see if the same error will be observed. You could also try to pass a date object without a format as a value, because in our demo for the row filtering, once multiple values are applied, the request is as in the attached screenshot (note the format of the date value).



Regards,
Konstantin Dikov
Progress Telerik