Grid foreignkey sorting using parameterMap?

[Raimo]

Hello

We have the same  problem as others with the sorting of foreignkey fields in the grid.
http://kendoui-feedback.telerik.com/forums/127393-kendo-ui-feedback/suggestions/4147227-grid-sorting-foreignkey-column
And it is still not solved...

Our workaround was to add the corresponding text value as a hidden column in the grid and in the Read Action we just replaced the Sorting Fields.

Something like that:

foreach (var sort in request.Sorts)
{
    if (sort.Member == "BenutzergruppeId")
        sort.Member = "Benutzergruppe";

    if (sort.Member == "DepartementId")
        sort.Member = "Departement";
}

If we do so, the frontend looks like it would sort the foreign key column but actually the data is sorted by the text field. Exactly what we wanted. And yes we could extend the request to make it more generic, as someone has suggested in the link above, but we have another goal.

We want to include it in our Library and to do that the plan was to do it the following way:
(Please keep in mind that the code has only a prototype state! For example, it does not consider multiple dropdowns in a grid. And I did not copy all the code that is necessary, just the parts that are interesting.)

We created an own new Column Type “DropDown” for the Grid and now we can use it like that:

columns.DropDown(config => config
    .Bound(p => p.DepartementId)
    .DisplayProperty(p => p.Departement))
    .Sortable(true)
    .Searchable();

Now we can add a DisplayProperty and our code just added a hidden column for that DisplayProperty. (And the ClientTemplate)

public static GridBoundColumnBuilder<TViewModel> DropDown<TViewModel>(
    this GridColumnFactory<TViewModel> factory,
    Action<XyzGridBoundDropDownBuilder<TViewModel>> configurator) where TViewModel : ViewModelBase, new()
{
    var boundDropDownBuilder = new XyzGridBoundDropDownBuilder<TViewModel>((XyzGrid<TViewModel>)factory.Container);
    configurator(boundDropDownBuilder);

    // Add a hidden column for the DisplayProperty
    var gridBoundColumnBuilderHidden = factory.Bound(boundDropDownBuilder.DropDown.DisplayExprValue);
    gridBoundColumnBuilderHidden.Hidden();
    gridBoundColumnBuilderHidden.Sortable(true);

    // Add the visible DropDown column (return it to allow further configuration)
    var gridBoundColumnBuilder = factory.Bound(boundDropDownBuilder.DropDown.ExprValue);
    gridBoundColumnBuilder.ClientTemplate($"#={boundDropDownBuilder.DropDown.DisplayProperyName}#");

    return gridBoundColumnBuilder;
}

And we add the information of the field in the ViewData for later reuse

public XyzGridBoundDropDownBuilder<TViewModel> Bound(Expression<Func<TViewModel, long>> expression)
{
    DropDown.ExprValue = expression;
    DropDown.ForeignKeyProperyName = expression.ToMemberExpression().Member.Name;
    DropDown.Grid.ViewData["ForeignKeyProperyName"] = DropDown.ForeignKeyProperyName;
    return this;
}

public XyzGridBoundDropDownBuilder<TViewModel> DisplayProperty(Expression<Func<TViewModel, string>> expression)
{
    DropDown.DisplayExprValue = expression;
    DropDown.DisplayProperyName = expression.ToMemberExpression().Member.Name;
    DropDown.Grid.ViewData["DisplayProperyName"] = DropDown.DisplayProperyName;
    return this;
}

 

Then it was the goal to completely solve the problem together with de DropDown Column and therefore we wanted to solve the replacement of the fields, like above in C#, in JavaScript which we will copy with the DropDown to the View. And we found out the best place to do that would be in the parameterMap method. And we did something like that:

<script>
    $(function() {
            var displayProperyName = '@ViewData["DisplayProperyName"]';
            var foreignKeyProperyName = '@ViewData["ForeignKeyProperyName"]';

            if ('null' !== displayProperyName)
                $('body').append("<div id='data-displayProperyName' data-displayProperyName='" + displayProperyName + "'></div>");

            if ('null' !== foreignKeyProperyName)
                $('body').append("<div id='data-foreignKeyProperyName' data-foreignKeyProperyName='" + foreignKeyProperyName + "'></div>");

            $('#BenutzerGrid').data("kendoGrid").dataSource.transport.parameterMap = function(options, operation) {

                var optionsResult = jQuery.extend(true, {}, options);

                if ('null' !== displayProperyName && 'null' !== foreignKeyProperyName) {
                    optionsResult.sort.forEach(replaceSortDisplayProperty);
                }

                var sortStringifyed = '';
                optionsResult.sort.forEach(function(item, index) {
                        sortStringifyed  += item.field + "-" + item.dir;
                    }
                );
                optionsResult.sort = '';
                optionsResult.sort = sortStringifyed;

                var result = decodeURIComponent($.param(optionsResult, true));
                return result;
            }
        }
    );

This worked that fare. Therefore we expect to be on the right track. But now finally the part which we are not happy with:

var sortStringifyed = '';
optionsResult.sort.forEach(function(item, index) {
        sortStringifyed  += item.field + "-" + item.dir;
    }
);
optionsResult.sort = '';
optionsResult.sort = sortStringifyed;

var result = decodeURIComponent($.param(optionsResult, true));
return result;

We could not find out which kendo JavaScript method is preparing the options to give it back like that:
sort=Name-asc&page=1&pageSize=15&group=&filter=

Is there a method for that? If not necessary we don’t want to implement it by our self to bring the sort, filter and group objects in the correct string based format.

Actually we just want to place something like that

var optionsResult = jQuery.extend(true, {}, options);

if ('null' !== displayProperyName && 'null' !== foreignKeyProperyName) {
    optionsResult.sort.forEach(replaceSortDisplayProperty);
}

for the read type at the beginning of the parameterMap method. The rest can run as before.

Generally, is this we do a recommend way, to do what we want? Or is there a better way? For example is the parameterMap the correct method?

And if we are on the right track, which method generates to correct stringifyed options including the translation of the arrays (sort, filter,..)?

[Konstantin Dikov]

Hi Raimo,

The parameterMap is the correct function for the override, but since you are now storing the text field in the Grid records, there should be no point in using the ForeignKey column in the first place. The reason that someone would use the ForeignKey column is to find the matching text values from external data to the value fields in the Grid records. Could you please elaborate why you need to keep the ForeignKey column when you have the text values in the data?

Looking forward to your reply.


Regards,
Konstantin Dikov
Progress Telerik

[Raimo]

Hi Konstantin

We need die foreign key field because for the filter in the column and when we allow inline editing. But you are right, if we only would show the text we would not need the foreign key field.

Therefore we are on the right track. :-)
Do you know which method generates to correct stringifyed options including the translation of the arrays (sort, filter,..)?

Regards,
Raimo

[Konstantin Dikov]

Hello Raimo,

You can use the parameterMap function and pass the sort, filter, etc. expressions:

var grid = $("#grid").getKendoGrid();
var parameterMap = grid.dataSource.transport.parameterMap;
var sortData = grid.dataSource.sort();
var filterData = grid.dataSource.filter();
var data = parameterMap({ sort: sortData, filter: filterData });
var request = decodeURIComponent($.param(data));

Best Regards,
Konstantin Dikov
Progress Telerik

[Raimo]

Hey Konstantin

Thanks for the solution, but it does not solve the problem.
The thing is that we override the dataSource.transport.parameterMap.  Therefore your solution does not work.
However finally we found the solution by our self.
Thanks to the following 2 posts:
https://www.telerik.com/forums/parametermap#5RxQYmnRa0WHwIthTfQ4Fw
https://stackoverflow.com/questions/2136522/can-you-alter-a-javascript-function-after-declaring-it

Currently the code looks like this:

<script>
    $(function() {
            var displayProperyName = '@ViewData["DisplayProperyName"]';
            var foreignKeyProperyName = '@ViewData["ForeignKeyProperyName"]';

        if ('null' !== displayProperyName)
            $('body').append("<div id='data-displayProperyName' data-displayProperyName='" + displayProperyName + "'></div>");

        if ('null' !== foreignKeyProperyName)
            $('body').append("<div id='data-foreignKeyProperyName' data-foreignKeyProperyName='" + foreignKeyProperyName + "'></div>");

        var parameterMap = kendo.data.transports["aspnetmvc-ajax"].prototype.options.parameterMap;
        var parameterMapSource = parameterMap.toString();
        var newParameterMapSource = parameterMapSource.replace('{',
                '{' +
                'if ("null" !== $("#data-displayProperyName").data("displayproperyname") && "null" !== $("#data-foreignKeyProperyName").data("foreignkeyproperyname")) ' +
                '{' +
                '  options.sort.forEach(replaceSortDisplayProperty);' +
                '}' +
                '\n')
            .replace(/^function[^{]+{/i, "") // remove everything up to and including the first curly bracket
            .replace(/}[^}]*$/i, "");  // remove last curly bracket and everything after<br>

        var newParameterMap = new Function('options', 'operation', 'serializationOptions', newParameterMapSource);
        kendo.data.transports["aspnetmvc-ajax"].prototype.options.parameterMap = newParameterMap;
        }
    );

    function replaceSortDisplayProperty(item, index) {
        debugger;
        var displayProperyName = $('#data-displayProperyName').data('displayproperyname');
        var foreignKeyProperyName = $('#data-foreignKeyProperyName').data('foreignkeyproperyname');


        if (item.field === foreignKeyProperyName)
            item.field = displayProperyName;
    }
</script>

It's still a prototype! But if somebody wants to do something similar it should be a good point to start.

Best regards,
Raimo

[Konstantin Dikov]

Hello Raimo,

Thank you for sharing the code with the community. Hopefully it will be helpful for others with similar scenario.


Best Regards,
Konstantin Dikov
Progress Telerik

[Raimo]

The version I posted before did not work. Here the correct Version:

<script>
    $(function() {
            var displayProperyName = '@ViewData["DisplayProperyName"]';
            var foreignKeyProperyName = '@ViewData["ForeignKeyProperyName"]';

        if ('null' !== displayProperyName)
            $('body').append("<div id='data-displayProperyName' data-displayProperyName='" + displayProperyName + "'></div>");
        if ('null' !== foreignKeyProperyName)
            $('body').append("<div id='data-foreignKeyProperyName' data-foreignKeyProperyName='" + foreignKeyProperyName + "'></div>");

        var parameterMap = $("#BenutzerGrid").getKendoGrid().dataSource.transport.parameterMap;
        var parameterMapSource = parameterMap.toString();
        var newParameterMapSource = parameterMapSource.replace('{',
                '{' +
                'if ("null" !== $("#data-displayProperyName").data("displayproperyname") && "null" !== $("#data-foreignKeyProperyName").data("foreignkeyproperyname")) ' +
                '{' +
                '  options.sort.forEach(replaceSortDisplayProperty);' +
                '}' +
                'var parameterMap = kendo.data.transports["aspnetmvc-ajax"].prototype.options.parameterMap;' + // The magic! The correct parameterMap function
                'var that = this;' + // it needs a that (unclear if that = this is correct, but it works)
                'var stringifyDates = true;' + // stringifyDates needs to be defined
                '\n')
            .replace(/^function[^{]+{/i, "") // remove everything up to and including the first curly bracket
            .replace(/}[^}]*$/i, "");  // remove last curly bracket and everything after<br>

        var newParameterMap = new Function('options', 'operation', 'serializationOptions', newParameterMapSource);
        $("#BenutzerGrid").getKendoGrid().dataSource.transport.parameterMap = newParameterMap;
        }
    );

    function replaceSortDisplayProperty(item, index) {
        var displayProperyName = $('#data-displayProperyName').data('displayproperyname');
        var foreignKeyProperyName = $('#data-foreignKeyProperyName').data('foreignkeyproperyname');

        if (item.field === foreignKeyProperyName)
            item.field = displayProperyName;
    }
</script>