This is a migrated thread and some comments may be shown as answers.

Sort By Foreign Key Text

11 Answers 206 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Matt
Top achievements
Rank 1
Matt asked on 10 Apr 2018, 04:23 PM

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

 

 

11 Answers, 1 is accepted

Sort by
0
Konstantin Dikov
Telerik team
answered on 12 Apr 2018, 02:20 PM
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
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
James
Top achievements
Rank 1
answered on 03 Oct 2019, 02:51 PM
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.
0
Alex Hajigeorgieva
Telerik team
answered on 07 Oct 2019, 12:07 PM

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

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
George
Top achievements
Rank 1
answered on 29 Oct 2020, 04:28 PM

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? 

0
Tsvetomir
Telerik team
answered on 02 Nov 2020, 12:12 PM

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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
Wendy
Top achievements
Rank 1
answered on 01 Mar 2021, 07:15 PM
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?
0
Tsvetomir
Telerik team
answered on 03 Mar 2021, 10:32 AM

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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
Wendy
Top achievements
Rank 1
answered on 11 Mar 2021, 11:35 PM

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!

0
Tsvetomir
Telerik team
answered on 15 Mar 2021, 03:17 PM

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

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

0
Wendy
Top achievements
Rank 1
answered on 17 Mar 2021, 03:41 PM

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!

 

0
Tsvetomir
Telerik team
answered on 19 Mar 2021, 10:54 AM

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

Love the Telerik and Kendo UI products and believe more people should try them? Invite a fellow developer to become a Progress customer and each of you can get a $50 Amazon gift voucher.

Tags
Grid
Asked by
Matt
Top achievements
Rank 1
Answers by
Konstantin Dikov
Telerik team
James
Top achievements
Rank 1
Alex Hajigeorgieva
Telerik team
George
Top achievements
Rank 1
Tsvetomir
Telerik team
Wendy
Top achievements
Rank 1
Share this question
or