Sort By Foreign Key Text

[Matt]

Is there a way I can accomplish the following using the MVC wrapper?

https://docs.telerik.com/kendo-ui/controls/data-management/grid/how-to/various/foreign-key-column-sorting-by-text

 

 

[Konstantin Dikov]

Hello Matt,

The referred HowTo example creates new field with the string value and that string value is bound to the column, which allows the dataSource to sort it correctly. In the context of the MVC this particular requirement could not be achieved with the same workaround and there are two options for handling this:

• Store the text field instead of the value field in the Grid;
• Manually map the text field with the value field within the controller, before calling the ToDataSourceResult (this could cause performance issues);
• Create the Text and Value fields in such manner, so that the sorting will have the same result if you sort by the Text or by the Value field (something like our online demo for the ForeignKey column)

Hope this helps.


Regards,
Konstantin Dikov
Progress Telerik

[James]

Has the ability to sort by text been implemented for Foreign Key columns yet?  It seems like such a natural requirement but as far as I can tell the Kendo grid generated using Asp.Net Core version doesn't support it.  I'm hoping I just can't find the announcement that the functionality has been added.

[Alex Hajigeorgieva]

Hi, James,

The feature request that was logged some time ago, unfortunately has not had may voters. Since the Kendo UI team prioritizes its future implementations based on popular demand amongst other methods of planning, it is not possible to sort a Foreign Key column by its text key at present out of the box:

https://feedback.telerik.com/kendo-jquery-ui/1358521-grid-sorting-foreignkey-column

I see that the request was also denied - the main reason is that there are various modelling EF techniques that may not include the Foreign Key Column full model that has the text field which would lead to errors.

If you have a structure like this, you will be able to sort by the text, see below:

 public class OrderViewModel
    {
        public int OrderID { get; set; }

        [ForeignKey("CategoryViewModel")]
        public int CategoryID { get; set; }

        public CategoryViewModel Category { get; set; }
    }

The easiest way is to implement the IComparable interface:

 

public class CategoryViewModel:IComparable<CategoryViewModel>

 public int CompareTo(CategoryViewModel other)
{
     return this.CategoryName.CompareTo(other.CategoryName);
}

 

An alternative is to intercept the request and change the Sort.Member from the Foreign Key Id to the Text, for example:

    public IActionResult ReadOrders([DataSourceRequest]DataSourceRequest request)
        {
            if (request.Sorts.Any()) {
                ModifySort(request.Sorts);
            }
            return Json(orders.ToDataSourceResult(request));
        }

        private void ModifySort(IEnumerable<SortDescriptor> sorts)
        {
            if (sorts.Any())
            {
                foreach (var sort in sorts)
                {
                    var descriptor = sort as SortDescriptor;
                    if (descriptor != null && descriptor.Member == "CategoryID")
                    {
                        descriptor.Member = "Category.CategoryName";
                    }
                }
            }
        }

Kind Regards,
Alex Hajigeorgieva
Progress Telerik

[George]

Hello Alex, 

 

I tried implementing your suggestion above but it does not seem to work for sorting by a grouped foreign key column. Is there a way to sort by the foreign key text when grouped? 

[Tsvetomir]

Hi Matt,

My name is Tsvetomir and I am covering for my colleague Alex in her absence.

The reason why the sort is not applied to the groups is that the sort and group sort are two distinct features. Therefore, the sort for the specific group is sent as part of the group query parameter of the request. 

What I can suggest is that you apply the sort after the .ToDataSourceResult() method has been called. Loop through the groups and reorder the items there. 

Regards,
Tsvetomir
Progress Telerik

[Wendy]

I'm trying to sort by Foreign Key text on mvc too but the solutions above didn't work for me. Is there any other solution for this issue?

[Tsvetomir]

Hi Wendy,

The shared example in this thread shows how to sort the foreign key on the server-side. Any other alternative would make the ForeignKey not needed and you would be better off using a regular complex object. A similar example can be observed here:

https://demos.telerik.com/aspnet-mvc/grid

Also, it would be helpful if you could share a sample where the issue is present so that I could help you out in resolving it.

 

Best regards,
Tsvetomir
Progress Telerik

[Wendy]

HI Tsvetomir,

Thank you so much for your reply. This is my original code:

Models:

public class IdentifierViewModel
{
    public Guid IdentifierId { get; set; }
    public Guid IdentifierCategoryId { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
    public List<IdentifierCategoryViewModel> IdentifierCategories { get; set; }
}

public class IdentifierCategoryViewModel
{
    public Guid IdentifierCategoryId { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
}

 

And this is the kendo grid:

@Html.ValidationSummary(true)

@(Html.Kendo().Grid<ViewModels.Categories.IdentifierViewModel>()
    .Name("Identifiers")
    .Editable(editor => editor.Mode(GridEditMode.InCell))
    .Filterable()
    .Resizable(resizing => resizing.Columns(true))
    .Pageable(x => x.PageSizes(new []{5,10,20,50,500}))
    .Sortable()
    .Scrollable(s => s.Height(400))
    .ToolBar(toolBar => {
        toolBar.Template(
            @<text>
                @if (RightsManager.GetCrudRights(ViewContext.Controller) == CrudRights.ViewEdit) {
                    <a class="k-button k-grid-add"><span class="k-icon k-add" style="vertical-align:text-top;"></span>Add Identifier</a>
                    <a class="k-button k-grid-save-changes"><span class="k-icon k-update" style="vertical-align:text-top;"></span>Save Changes</a>
                    <a class="k-button k-grid-cancel-changes"><span class="k-icon k-cancel" style="vertical-align:text-top;"></span>Cancel Changes</a>
                }
                @Html.KendoGridToolbarSearch("Identifiers", new string[] { "Name" })
            </text>);

    })
    .DataSource(ds => ds
                    .Ajax()
                    .Batch(true)
                    .Model(m => {
                        m.Id(x => x.IdentifierId);
                        m.Field(x => x.IsActive).DefaultValue(true);
                    })
                    .Create(read => read.Action("CreateIdentifiers", "Identifier"))
                    .Read(read => read.Action("GetIdentifiers", "Identifier"))
                    .Update(read => read.Action("SaveBatchIdentifiers", "Identifier"))
                    .Sort(s => s.Add(x => x.IdentifierId))
                    .PageSize(10)
    )
    .Columns(columns => {
        columns.Template(@<text></text>).Width(30).ClientTemplate("<a class=\"k-button k-button-icon k-grid-Edit\" onclick=\"ShowEditWindow('#= IdentifierId #');\"><span class=\"k-icon k-edit\" ></span></a>");
        columns.Bound(x => x.Name).Width(300);
        columns.ForeignKey(x => x.IdentifierCategoryId, (System.Collections.IEnumerable)ViewBag.TempIdentifierCategories, "Value", "Text").Width(200).Title("Identifier Category").EditorTemplateName("GridForeignKey-Kendo");
        columns.Bound(x => x.IsActive).Width(100).ClientTemplate("<input type='checkbox' class='GridCheckBox' name='IsActive' #=IsActive? checked='checked' : '' # /> ");
        columns.Command(command =>
        {
            command.Custom("MoveUp").Click("MoveUp").HtmlAttributes(new { id = "IdentifierId" }).Text("<span class=\"k-icon k-i-arrow-n\"></span>");
            command.Custom("MoveDown").Click("MoveDown").HtmlAttributes(new { id = "IdentifierId" }).Text("<span class=\"k-icon k-i-arrow-s\"></span>");
        }).Width(100);
    }))

 

Like I say before I have tried all the solutions mentioned here (changing the models to add the IComparable interface, modified the sort descriptor) but none of them seems to work.

If you can help me to check what I'm missing that will really save me!

 

Thanks!

[Tsvetomir]

Hi Wendy, 

I have investigated the provided code snippets and I have noticed that the IdentifierCategoryViewModel does not inherit from the IComparable interface. And the CompareTo method has not been handled.

What I have also noticed is that you have a List of items per view model. Effectively, on the server-side, you will be comparing a list of items to another list of items and I am not aware what is the exact scenario you are willing to achieve. As well as, I am not sure how you would be comparing one list to another list. Could you share more information regarding the same?

 

Regards,
Tsvetomir
Progress Telerik

[Wendy]

Hi Tsvetomir,

Thanks for your help. Actually this is an old code that I just need to make some improvements, so I'm not fully aware why the use of list to compare. I changed the models to implement the IComparable interface, to look like this:

public class IdentifierViewModel
   {
       public Guid IdentifierId { get; set; }

       [ForeignKey("IdentifierCategoryViewModel")]
       public Guid IdentifierCategoryId { get; set; }

       public string Name { get; set; }
       public bool IsActive { get; set; }
       public IdentifierCategoryViewModel IdentifierCategories { get; set; }
   }

   public class IdentifierCategoryViewModel : IComparable<IdentifierCategoryViewModel>
   {
       public Guid IdentifierCategoryId { get; set; }
       public string Name { get; set; }
       public bool IsActive { get; set; }

       public int CompareTo(IdentifierCategoryViewModel other)
       {
           return Name.CompareTo(other.Name);
       }
   }

 

But still sorting by id, not by text. Is there something else that I need to do on the view?

 

Thanks!

 

[Tsvetomir]

Hi Wendy,

Indeed, with the current setup of the models, you should not be using the ForeignKey column. Instead, you should create a bound column where you will bind it to the IdentifierCategories property. After that, you could set up the client template and the editor template.

The alternative to this would be to modify the sorts as shown earlier:

   public IActionResult ReadOrders([DataSourceRequest]DataSourceRequest request)
        {
            if (request.Sorts.Any()) {
                ModifySort(request.Sorts);
            }
            return Json(orders.ToDataSourceResult(request));
        }

        private void ModifySort(IEnumerable<SortDescriptor> sorts)
        {
            if (sorts.Any())
            {
                foreach (var sort in sorts)
                {
                    var descriptor = sort as SortDescriptor;
                    if (descriptor != null && descriptor.Member == "IdentifierCategoryId")
                    {
                        descriptor.Member = "IdentifierCategories.Name";
                    }
                }
            }
        }

Note that with both approaches the complex object should not be set to a list of objects. Otherwise, you will have to further customize the approach.

 

Kind regards,
Tsvetomir
Progress Telerik