Transform Request to Autoquery friendly

Question

We are working with a 3rd party grid (telerik kendo) that has paging/sorting/filtering built in. It will send the requests in a certain way when making the GET call and I'm trying to determine if there is a way to translate these requests to AutoQuery friendly requests.

Query string params

Sort Pattern:

sort[{0}][field] and sort[{0}][dir]

Filtering:

filter[filters][{0}][field]
filter[filters][{0}][operator]
filter[filters][{0}][value]

So this which is populated in the querystring:

filter[filters][0][field]
filter[filters][0][operator]
filter[filters][0][value]

would need to be translated to.

FieldName=1  //      filter[filters][0][field]+filter[filters][0][operator]+filter[filters][0][value] in a nutshell (not exactly true)

Should I manipulate the querystring object in a plugin by removing the filters (or just adding the ones I need) ? Is there a better option here?

I'm not sure there is a clean way to do this on the kendo side either.

Answer 1

I will explain the two routes I'm going down, I hope to see a better answer.

First, I tried to modify the querystring in a request filter, but could not. I ended up having to run the autoqueries manually by getting the params and modifying them before calling AutoQuery.Execute. Something like this:

var requestparams = Request.ToAutoQueryParams();
var q = AutoQueryDb.CreateQuery(requestobject, requestparams);
AutoQueryDb.Execute(requestobject, q);

I wish there was a more global way to do this. The extension method just loops over all the querystring params and adds the ones that I need.

After doing the above work, I wasn't very happy with the result so I investigated doing it differently and ended up with the following:

Register the Kendo grid filter operations to their equivalent Service Stack auto query ones:

 var aq = new AutoQueryFeature { MaxLimit = 100, EnableAutoQueryViewer=true };
        aq.ImplicitConventions.Add("%neq", aq.ImplicitConventions["%NotEqualTo"]);
        aq.ImplicitConventions.Add("%eq", "{Field} = {Value}");

Next, on the grid's read operation, we need to reformat the the querystring:

read: {
                                url: "/api/stuff?format=json&isGrid=true",
                                data: function (options) {
                                    if (options.sort && options.sort.length > 0) {
                                        options.OrderBy = (options.sort[0].dir == "desc" ? "-" : "") + options.sort[0].field;
                                    }
                                    if (options.filter && options.filter.filters.length > 0) {
                                        for (var i = 0; i < options.filter.filters.length; i++) {
                                            var f = options.filter.filters[i];
                                            console.log(f);
                                            options[f.field + f.operator] = f.value;
                                        }
                                    }

                                }

Now, the grid will send the operations in a Autoquery friendly manner.

Answer 2

I created an AutoQueryDataSource ts class that you may or may not find useful.

It's usage is along the lines of:

this.gridDataSource = AutoQueryKendoDataSource.getDefaultInstance<dtos.QueryDbSubclass, dtos.ListDefinition>('/api/autoQueryRoute', { orderByDesc: 'createdOn' });

export default class AutoQueryKendoDataSource<queryT extends dtos.QueryDb_1<T>, T> extends kendo.data.DataSource {
    private constructor(options: kendo.data.DataSourceOptions = {}, public route?: string, public request?: queryT) {
        super(options)
    }

    defer: ng.IDeferred<any>;
    static exportToExcel(columns: kendo.ui.GridColumn[], dataSource: kendo.data.DataSource, filename: string) {
        let rows = [{ cells: columns.map(d => { return { value: d.field }; }) }];
        dataSource.fetch(function () {
            var data = this.data();
            for (var i = 0; i < data.length; i++) {
                //push single row for every record
                rows.push({
                    cells: _.map(columns, d => { return { value: data[i][d.field] } })
                })
            }
            var workbook = new kendo.ooxml.Workbook({
                sheets: [
                    {
                        columns: _.map(columns, d => { return { autoWidth: true } }),
                        // Title of the sheet
                        title: filename,
                        // Rows of the sheet
                        rows: rows
                    }
                ]
            });
            //save the file as Excel file with extension xlsx
            kendo.saveAs({ dataURI: workbook.toDataURL(), fileName: filename });
        })
    }

    static getDefaultInstance<queryT extends dtos.QueryDb_1<T>, T>(route: string, request: queryT, $q?: ng.IQService, model?: any) {
        let sortInfo: {
            orderBy?: string,
            orderByDesc?: string,
            skip?: number
        } = {
        };

        let opts = {
            transport: {
                read: {
                    url: route,
                    dataType: 'json',
                    data: request
                },
                parameterMap: (data, type) => {
                    if (type == 'read') {
                        if (data.sort) {
                            data.sort.forEach((s: any) => {
                                if (s.field.indexOf('.') > -1) {
                                    var arr = _.split(s.field, '.')
                                    s.field = arr[arr.length - 1];
                                }

                            })
                        }//for autoquery to work, need only field names not entity names.

                        sortInfo = {
                            orderByDesc: _.join(_.map(_.filter(data.sort, (s: any) => s.dir == 'desc'), 'field'), ','),
                            orderBy: _.join(_.map(_.filter(data.sort, (s: any) => s.dir == 'asc'), 'field'), ','),
                            skip: 0
                        }

                        if (data.page)
                            sortInfo.skip = (data.page - 1) * data.pageSize,
                                _.extend(data, request);
                        //override sorting if done via grid
                        if (sortInfo.orderByDesc) {
                            (<any>data).orderByDesc = sortInfo.orderByDesc;
                            (<any>data).orderBy = null;
                        }
                        if (sortInfo.orderBy) {
                            (<any>data).orderBy = sortInfo.orderBy;
                            (<any>data).orderByDesc = null;
                        }
                        (<any>data).skip = sortInfo.skip;

                        return data;
                    }
                    return data;
                },
            },
            requestStart: (e: kendo.data.DataSourceRequestStartEvent) => {
                let ds = <AutoQueryKendoDataSource<queryT, T>>e.sender;

                if ($q)
                    ds.defer = $q.defer();
            },
            requestEnd: (e: kendo.data.DataSourceRequestEndEvent) => {
                new DatesToStringsService().convert(e.response);
                let ds = <AutoQueryKendoDataSource<queryT, T>>e.sender;

                if (ds.defer)
                    ds.defer.resolve();
            },
            schema: {
                data: (response: dtos.QueryResponse<T>) => {
                    return response.results;
                },
                type: 'json',
                total: 'total',
                model: model
            },
            pageSize: request.take || 40,
            page: 1,
            serverPaging: true,
            serverSorting: true
        }

        let ds = new AutoQueryKendoDataSource<queryT, T>(opts, route, request);
        return ds;
    }
}