SQL Server Paging

[Travis Cotton]

Hopefully I didn't reinvent the wheel here, but I looked around and didn't find a solution. I want to use the DataSourceResult classes with Microsoft SQL Server. When I tried server side paging, it failed. Found that Microsoft added paging support in version 2012, but with a different syntax than other databases. Others use something like "LIMIT x OFFSET y" and MSSQL uses "OFFSET y ROWS FETCH NEXT x ROWS ONLY" for whatever reason. Also, MSSQL won't return results unless you also include a sort column. The Telerik PHP wrapper doesn't seem to account for this.

I found "private function page()" in DataSourceResult.php and had to change it to "protected function page()" so that I can override it, then created this class in my PHP file:

class r2DataSourceResult extends DataSourceResult {
    protected function page() {
         return ' OFFSET :skip ROWS FETCH NEXT :take ROWS ONLY ';
    }
}

 

That solved half of the problem. I still had to guarantee that there is always a sort column, and I didn't find a way to do that from the grid (which is where I would like it). I could configure the grid to sort by a column by default, but if the user changes the sort to nothing, it breaks. So I added a default sort parameter to the querystring for the data URL that I specify in the grid, and if the grid doesn't pass a sort field via JSON, I add the default field from the querystring like this:

if (!isset($request->sort[0]->field)) {
  if (isset($_GET['ds'])) {
    $prop = (object) ['field' => $_GET['ds'], 'dir' => 'asc'];
    $request->sort = array($prop);
  }
else {
  echo "Must provide a default sort field with ds querystring parameter.";
  }
}

 

Maybe there's a way to pass a custom value from the grid along with the other JSON request values, but I haven't found that yet. That would be preferable as a method of forcing a default sort column.

So these two tweaks seem to make it work. I've just started, and haven't tried anything other than read functions. Hopefully I'm not going down a rabbit hole here.

Is there already a solution for the new paging syntax with MSSQL 2012 and up, and for forcing a default sort column to always be included in the query?

 

 

[Travis Cotton]

I found the way to pass arbitrary parameters from the grid to the service using transport.read.data, so no more querystring parameter to specify the default sort column if the grid didn't specify it. Still the issue with MSSQL server side paging syntax, and maybe there's already a better way to force/require a sort column so that paging works.

[Boyan Dimitrov]

Hello Travis Cotton,

I would suggest to take a look at the Grid / Aggregates where we set a default grouping on DataSource level with the Kendo UI Grid definition. Same thing can be applied for the sorting operation as well. 

Regards,
Boyan Dimitrov
Progress Telerik

[Travis Cotton]

Thanks Boyan. To clarify, it's not that I can't set a default sort column for the datasource. I am doing that with -- sort: { field: "field_name", dir: "asc" } in the grid configuration javascript. But the column is sortable by the end user. The grid loads, the column is sorted by default, and it's fine. Then the user clicks the column header to re-sort, it goes from my default ASC to DESC and it's still fine, then they click again, and it removes the sort from that column, and the json data sent to the server has an empty sort value -- "sort":[], and then it breaks.

To get around this, I check the $result->sort object for values, and if there are none, I re-create the $result->sort object with the default field that I originally set, which I pass from the grid to the PHP script in one of my arbitrary json fields.

Also, I'm not using the PHP wrappers to control the grid because I'm embedding the grid into a CMS, and the way that it renders the output wasn't working out for me. So I'm just configuring the grid using the jQuery documentation and examples, but I still wanted to use the DataSourceResult.php script to process the data from the grid since it's already doing the work of picking apart the filters, sorts, returning the data as the grid expects it and so on. 

I have a working solution for the SQL Server paging issue (by altering DataSourceResult.php, which I didn't want to do), and for the forced default sort (by looking for a sort value and injecting one if necessary), but thought that I probably overlooked something simple, as usual.

Sorry if I misunderstood your suggestion for the sort. Thanks for the assistance.

[Viktor Tachev]

Hello Travis,

If you would like to disable the non-sorted state of the items I would suggest setting the sortable.allowUnsort property to false. This way the users will be able to sort columns ascending or descending. 

In case you would like to keep the unsorted option please update the server-side logic handling the sort operation. There can be an additional condition that checks if there are any sort options. In case there are none it would just forward the data as-is from the database. 


Regards,
Viktor Tachev
Progress Telerik

[Travis Cotton]

Thanks Viktor. That should be a good solution for most cases.