Kendo UI - JSON Response for - Grid using Remote Data Source W/Server Grouping & Server Aggregates

Question

I have a project in which I'm using the KendoUI Grid using the server to get the data instead of locally.

I'm not sure what the JSON response should be from my server to get grouping to work however. My goal is when the user drags a column to the grouping header that I know what kind of JSON response to give back so the GRID groups by that column and any other columns that might be added to that header.

Given the image above, how do I create a JSON response to fulfill it (so its showing what its supposed to grouped)? I get I have to do this on my own on the server but not sure how JSON needs to be formated. Furthermore if I want to show a 'count' field next to the groups when they are created so I know how many items are in each group (which I believe is the aggregate?)

My current grid codes looks like the following:

<div id="grid" style="height:100%;"></div>
<script>
    $(window).on("resize", function() {
      kendo.resize($("#grid"));
    });

    var crudServiceBaseUrl = "/api",
    dataSource = new kendo.data.DataSource({
        transport: {
            read:  {
                url: crudServiceBaseUrl + "/companies",
                dataType: "json",
                type: "POST"
            },
            update: {
                url: crudServiceBaseUrl + "/companies/update",
                dataType: "json",
                type: "POST"
            },
            destroy: {
                url: crudServiceBaseUrl + "/companies/destroy",
                dataType: "json",
                type: "POST"
            },
            create: {
                url: crudServiceBaseUrl + "/companies/create",
                dataType: "json",
                type: "POST"
            },
            parameterMap: function(options, operation) {
                if (operation !== "read" && options.models) {
                    return {models: kendo.stringify(options.models)};
                }
            }
        },
        error: function (e) {
            /* the e event argument will represent the following object:
            {
                errorThrown: "custom error",
                errors: ["foo", "bar"]
                sender: {... the Kendo UI DataSource instance ...}
                status: "customerror"
                xhr: null
            }
            */
            //alert("Status: " + e.status + "; Error message: " + e.errorThrown);
            console.log("Status: " + e.status + "; Error message: " + e.errorThrown);
            console.log("Errors: " + e.errors.join("; "));
        },
        autoSync: false,
        serverPaging: true,
        serverFiltering: true,
        serverSorting: true,
        serverGrouping: true,
        serverAggregates: true,
        pageSize: 20,
        columnResizeHandleWidth: 6,
        schema: {
            total: "itemCount",
            data: "items",
            groups: "groups",
            aggregates: "aggregates",
            group: {
                field: "phone", aggregates: [{ field: "phone", aggregate: "count" }]
            },
            model: {
                id: "id",
                fields: {
                    id: { editable: false, nullable: true },
                    name: { validation: { required: true } },
                    phone: { 
                        type: "string",
                        validation: { 
                            required: true,
                            phonerule: function(e){
                                if (e.is("[data-phonerule-msg]"))
                                {
                                    var input  = e.data('kendoMaskedTextBox');
                                    //If we reached the end of input then it will return -1 which means true, validation passed
                                    //Otherwise it won't === -1 and return false meaning all the characters were not entered.
                                    return input.value().indexOf(input.options.promptChar) === -1;
                                }
                                return true; //return true for anything else that is not data-phonerule-msg
                            } 
                        } 
                    },
                    email: { type: "string", validation: { required: true, email:true } }
                }
            }
        }
    });

    $("#grid").kendoGrid({
        dataSource: dataSource,
        groupable: true,
        sortable: {
            mode: "multiple",
            allowUnsort: true
        },
        selectable: "multiple cell",
        allowCopy:true,
        toolbar: ["create","excel"],
        excel: {
            fileName: "Kendo UI Grid Export.xlsx",
            //Below is only used as fallback for old browsers without support
            proxyURL: "//demos.telerik.com/kendo-ui/service/export",
            filterable: true
        },
        pageable: {
            refresh: true,
            pageSizes: true,
            buttonCount: 5
        },
        reorderable: true,
        resizable: true,
        columnMenu: true,
        filterable: true,
        editable: "popup",
        mobile: true,
        columns: [
            { 
                field: "name",
                title: "Company Name",
                aggregates: ["count"],
                groupFooterTemplate: "Count: #=count#"
            },
            { 
                field: "phone",
                title: "Phone",
                editor: function(container, options){
                    //pattern="[(][0-9]{3}[)] [0-9]{3}-[0-9]{4}"
                    var input = $('<input type="tel" data-phonerule-msg="Invalid Phone Number!" class="k-textbox"  required />');
                    input.attr("name", options.field);
                    input.kendoMaskedTextBox({
                        mask: "[phone]"
                    });
                    input.appendTo(container);
                },
                aggregates: ["count"],
                groupFooterTemplate: "Count: #=count#"
            },
            { 
                field: "email",
                title: "Email",
                editor: function(container, options){
                    var input = $('<input type="email" data-email-msg="Invalid email!" class="k-textbox" required/>');
                    input.attr("name", options.field);
                    input.appendTo(container);
                },
                aggregates: ["count"],
                groupFooterTemplate: "Count: #=count#"
            },
            { 
                command: ["edit", "destroy"],
                title: "Operations",
                width: "240px"
            }
        ],
    });
</script>

My current code that generates the demo javascript via Symfony 3.0 is below in..

DefaultController.php

/**
     * @Route("/api/companies", name="api_companies_read")
     */
public function apiCompaniesReadAction(Request $request)
    {
        $data["itemCount"] = "7";

        // $tdata["field"] = "";
//      $tdata["value"] = "";
//      $tdata["items"] = "hey";
//      $data["groups"][] = $tdata;

        $tdata["id"]    = "1";
        $tdata["name"] = "Joe";
        $tdata["phone"] = "[phone]";
        $tdata["email"] = "[email protected]";

        $data["items"][] = $tdata;

        $tdata["id"]    = "2";
        $tdata["name"] = "Rachel";
        $tdata["phone"] = "[phone]";
        $tdata["email"] = "[email protected]";

        $data["items"][] = $tdata;

        $tdata["id"]    = "3";
        $tdata["name"] = "John";
        $tdata["phone"] = "[phone]";
        $tdata["email"] = "[email protected]";

        $data["items"][] = $tdata;

        $tdata["id"]    = "4";
        $tdata["name"] = "Richard";
        $tdata["phone"] = "[phone]";
        $tdata["email"] = "[email protected]";

        $data["items"][] = $tdata;

        $tdata["id"]    = "5";
        $tdata["name"] = "Sister";
        $tdata["phone"] = "[phone]";
        $tdata["email"] = "[email protected]";

        $data["items"][] = $tdata;

        $tdata["id"]    = "6";
        $tdata["name"] = "Brother";
        $tdata["phone"] = "[phone]";
        $tdata["email"] = "[email protected]";

        $data["items"][] = $tdata;

        $tdata["id"]    = "7";
        $tdata["name"] = "Sibling";
        $tdata["phone"] = "[phone]";
        $tdata["email"] = "[email protected]";

        $data["items"][] = $tdata;

//      schema: {
//          total: "total",
//          model: {
//              id: "CompanyID",
//              fields: {
//                  CompanyID: { editable: false, nullable: true },
//                  Name: { validation: { required: true } },
//                  Phone: { type: "string" },
//                  Email: { type: "string" }
//              }
//          }
//      }

        // replace this example code with whatever you need
        return new JsonResponse($data);

    }

The current JSON it creates looks like this.

JSON

{"itemCount":"7","items":[{"id":"1","name":"Joe","phone":"[phone]","email":"[email protected]"},{"id":"2","name":"Rachel","phone":"[phone]","email":"[email protected]"},{"id":"3","name":"John","phone":"[phone]","email":"[email protected]"},{"id":"4","name":"Richard","phone":"[phone]","email":"[email protected]"},{"id":"5","name":"Sister","phone":"[phone]","email":"[email protected]"},{"id":"6","name":"Brother","phone":"[phone]","email":"[email protected]"},{"id":"7","name":"Sibling","phone":"[phone]","email":"[email protected]"}]}

I found an article here that looks close to what I want just hard to understand. This might also help.

I created JSFiddle that can be played around with, just needs to be supplied valid data.

Update

I got server paging to work! The first major change is on the transport for the datasource you have to change the parameterMap to send json so you can access what its trying to tell your server to change.

dataSource = new kendo.data.DataSource({
        transport: {
            read:  {
                url: crudServiceBaseUrl + "/companies",
                dataType: "json",
                type: "POST"
            },
            update: {
                url: crudServiceBaseUrl + "/companies/update",
                dataType: "json",
                type: "POST"
            },
            destroy: {
                url: crudServiceBaseUrl + "/companies/destroy",
                dataType: "json",
                type: "POST"
            },
            create: {
                url: crudServiceBaseUrl + "/companies/create",
                dataType: "json",
                type: "POST"
            },
            parameterMap: function(options, operation) {
                return kendo.stringify(options);
            }
        },

My datasource looks like above, but you could adjust to your needs. Next my php file below.

PHP /companies

/**
     * @Route("/api/companies", name="api_companies_read")
     */
    public function apiCompaniesReadAction(Request $request)
    {

        $request_body = file_get_contents('php://input');
        $json = json_decode($request_body);

        //Based on the JSON Payload response adjust the search in the database
        if($json){
            $page       = $json->page;
            $pageSize   = $json->pageSize;
            $skip       = $json->skip;
            $take       = $json->take;
        }else{
            $page = 1;
            $pageSize = 20;
            $skip = 1;
            $take = 1;
        }

        $repository = $this->getDoctrine()->getRepository('AppBundle:Company');

        /*
        findBy(
            array        $criteria,
            array        $orderBy  = null, 
            integer|null $limit    = null,
            integer|null $offset   = null
        )
        */

        $company_total = $repository->findAll();
        $company_records = $repository->findBy(array(),array(),$pageSize,($page-1)*$pageSize);

        $data["total"] = count($company_total);

        foreach($company_records as $company){
            $temp["id"]     = $company->getId();
            $temp["name"]   = $company->getName();
            $temp["phone"]  = $company->getPhone();
            $temp["email"]  = $company->getEmail();
            $data["data"][] = $temp;
        }

        //converts data to JSON
        return new JsonResponse($data);

    }

Please remember the above is a Symfony 3.0 php implementation using annotations to set the route. The important part in that code is.

$request_body = file_get_contents('php://input');
        $json = json_decode($request_body);

        //Based on the JSON Payload response adjust the search in the database
        if($json){
            $page       = $json->page;
            $pageSize   = $json->pageSize;
            $skip       = $json->skip;
            $take       = $json->take;
        }else{
            $page = 1;
            $pageSize = 20;
            $skip = 1;
            $take = 1;
        }

The file_get_contents('php://input'); gets the javascript object that the KendoUI control sends back.

UPDATE # 3 Got Server Sorting Working!

Below is an example implementaiton using Symfony 3.0 and Doctrine to do Server Sorting!

PHP

/**
     * @Route("/api/companies", name="api_companies_read")
     */
    public function apiCompaniesReadAction(Request $request)
    {
        $request_body = file_get_contents('php://input');
        $json = json_decode($request_body);

        //print_r($json);

        //default parameters in case of error.
        $page = 1;
        $pageSize = 20;
        $skip = 1;
        $take = 1;
        $sort = array();

        //Based on the JSON Payload response adjust the search in the database
        if(isset($json)){
            $page       = $json->page;
            $pageSize   = $json->pageSize;
            $skip       = $json->skip;
            $take       = $json->take;

            if(isset($json->sort)){
                //"sort":[{"field":"name","dir":"asc"}]}:

                foreach($json->sort as $sortObj){
                    $sort[$sortObj->field] = $sortObj->dir;
                }
            }

        }

        $repository = $this->getDoctrine()->getRepository('AppBundle:Company');

        /*
        findBy(
            array        $criteria,
            array        $orderBy  = null, 
            integer|null $limit    = null,
            integer|null $offset   = null
        )
        */

        $company_total = $repository->findAll();
        $company_records = $repository->findBy(array(),$sort,$pageSize,($page-1)*$pageSize);

        $data["total"] = count($company_total);

        foreach($company_records as $company){
            $temp["id"]     = $company->getId();
            $temp["name"]   = $company->getName();
            $temp["phone"]  = $company->getPhone();
            $temp["email"]  = $company->getEmail();
            $data["data"][] = $temp;
        }

        //converts data to JSON
        return new JsonResponse($data);

    }

Answer 1

That first link is spot on. I had to export data from a grid to pdf and excel adhering to the grid grouping. I finally figured out the semantics. The grid has columns and data objects which you can get through grid calls. The data object holds the grouping information. Here is the processing operation.

In a nutshell, you have to recursively iterate the data object tpo determine the grouping. For each element in the data you need to inspect if the data[x].value exists. If it exists then it is a group object with child data. If it does not exists then you use the normal data[row][column].field to get to the child data. The trick here is to call a recurisive function for each point where dat[0].value has data and finally process the data[row][column] on the unwind.

Below is a js function you can use to inspect the Grid's json dta. I would add a group or two to your grid and see the difference between the data with/without grouping applied and that should be what you need to add to your data....I think you pretty much add data as data={[[],[]]}unless it is a group, then it is data={[value='groupName',{[],[]}]}.

function showData(gridName) {
        var grid = $('#' + gridName).data('kendoGrid');
        var data = grid.dataSource.data().toJSON();
        console.log(data);
    }