Sort columns with custom expression

[Joan Vilariño]

Hi... I need to sort a column with a value that's not the column's bound value like in this example:

cl.Bound(o => o.Descripcion).Width(200);
            cl.Bound(o => o.IdTipoImpuesto).Width(180).ClientTemplate("(<#= TipoImpuesto.Nombre#>) <#= TipoImpuesto.Descripcion #>");
            cl.Bound(o => o.IdTipoJuego).Width(180).ClientTemplate("<#= TipoJuego.Nombre #>");
            cl.Bound(o => o.PorcentajePremio).Width(130).HtmlAttributes(new { style = "text-align:right" });

The columns 'IdTipoImpuesto' and 'IdTipoJuego' are bound to the sub-entities Id, for editing purposes (the combobox on the template is bound to a 'long' field), but the value shown is the entitie's descritption... so I need it to sort by that description instead the Id...

I see it's not implemented, but would it be so difficult to make a .OrderBy(o => o.TipoImpuesto.Nombre) at column level so you could specify what would be the sort criteria for a given column??

I would really need that...

If you don't plan to do this, please can you give me any directions on how to achieve it?

Thanks!!

[Atanas Korchev]

Hello Joan Vilariño,

The grid can sort only by the member of the column. Unfortunately there is no workaround for the time being apart from using custom binding.

Regards,
Atanas Korchev
the Telerik team

[Joan Vilariño]

Ok, then que questions are:

Are you working on it?

Will you work on it?

I you accept proposals... I would propose to create a SortCriteria method on the column objects where you could pass a lambda expression that would determine the sorting for this column... like :

.Columns(cl => cl.Bound(o => o.CustomerId).SortCriteria(o => o.Customer.Name).ClientTemplate("<#= Customer.Name #>")

I've been looking at your code and I don't think it shoul be TOO complicated to implement (just some property where to keep the orderby data of the column, and make the corrections to work in this call:

command.SortDescriptors.AddRange(GridDescriptorSerializer.Deserialize<SortDescriptor>(orderBy));

Or maybe just changing the onClick event of the column header to place the correct sort expression based on the new method...

Is there any possibility this will be implemented in a future? It would solve all my problems....

Thanks!

[Atanas Korchev]

Hello Joan Vilariño,

 As with every other feature request it all depends on customer feedback. If lots of customers request this we will consider it for implementation. If this is an isolated request we propose a workaround or give instructions how to modify the source code.

In your case there is at least one way to implement this requirement. You can create a ViewModel object and expose both properties. Bind the column to the sortable one and display the other one via the ClientTemplate. For example

class Order
{
       public Customer Customer;
}

class OrderViewModel
{
      public int CustomerID;
      public string CustomerName;
}

Regards,
Atanas Korchev
the Telerik team

[Joan Vilariño]

Hello again.

I've been working a little on your code to try to get this done... though I think it can be improved, I made some changes to your code to implement ordering a column with any expression like this: ( >>>> old code, <<<< new code)

UI/Grid/GridColumnBase.cs: line 72

<<<    public string SortMember
        {
            get
            {
                return Settings.SortMember;
            }
            set
            {
                Settings.SortMember = value;
            }
        }

UI/Grid/Columns/IGridBoundColumn.cs: line 43

<<<    string SortMember
        {
            get; set;
        }

UI/Grid/Fluent/GridColumnFactory.cs: line 76

<<<     column.Settings.SortMember = column.Settings.Member;

UI/Grid/Fluent/GridColumnFactory.cs: line 83

<<<    public virtual GridBoundColumnBuilder<TModel> Bound<TValue>(Expression<Func<TModel, TValue>> expression
                                                                    , Expression<Func<TModel, object>> sortExpression)
        {
            Guard.IsNotNull(expression, "expression");
            Guard.IsNotNull(sortExpression, "sortExpression");
            GridBoundColumn<TModel, TValue> column = new GridBoundColumn<TModel, TValue>(Container, expression);
            column.Settings.SortMember = sortExpression.MemberWithoutInstance();
            Container.Columns.Add(column);

            return new GridBoundColumnBuilder<TModel>(column);
        }

UI/Grid/Serialization/GridBoundColumnSerializer.cs: line 29

>>>         FluentDictionary.For(result)
                .Add("title", column.Title)
                .Add("member", column.Member)
                .Add("type", column.MemberType.ToJavaScriptType())
                .Add("format", column.Format, () => column.Format.HasValue())
                .Add("groupable", column.Groupable, true);


<<<         FluentDictionary.For(result)
                .Add("title", column.Title)
                .Add("member", column.Member)
                .Add("type", column.MemberType.ToJavaScriptType())
                .Add("format", column.Format, () => column.Format.HasValue())
                .Add("groupable", column.Groupable, true)
                .Add("sortMember", column.SortMember);

UI/Grid/Settings/GridColumnSettings.cs: line 27

<<< private string sortMember;

UI/Grid/Settings/GridColumnSettings.cs: line 112

<<<    public string SortMember
        {
            get
            {
                return sortMember;
            }
            set
            {
                sortMember = value;
            }
        }

Scripts/telerik.grid.js: line 216

>>> return $.map(this.sorted, function (s) { return s.member + '-' + s.order; }).join('~');

<<< return $.map(this.sorted, function (s) { return s.sortMember + '-' + s.order; }).join('~');

This works for me the following way :

cl.Bound(o => o.SubEntityId, o => o.SubEntity.Name).ClientTemplate("<#=SubEntity.Name#>");

Allowing me to have a template for .SubentityId that makes it editable, but ordering this colum using .SubEntity.Name.

If you are pleased with this code, would you be SOOOOO kind to include it in your next build? It's very important for us.

Thank you!!

[Atanas Korchev]

Hi Joan Vilariño,

 I am afraid we cannot include this as a built-in feature. If we implement it we would need FilterMember, BindMember, GroupMember etc. Even if we do that people would very easily get confused.

 I still believe that the functionality which you need to implement can be easily implemented with templates and some JavaScript code. Isn't this example showing exactly what you need?

Regards,

Atanas Korchev
the Telerik team

[Joan Vilariño]

I find incredible that your grid component can't do that and you don't want to implement it..

The example you are giving me has nothing to do with what I'm saying... I don't see any SubEntity in your model, and there is no column sorting, so I ask myself if you didn't even understand what I'm asking for ...

As soon as I can define a ClientTemplate to show something that's not bound to the colum, I should be able to define what should be the sort criteria for that column.

The term confusing? We're not talking about end users with limited knowledge of computers... we're talking about coders and giving the tools they need to achieve their goals... at this moment, you haven't gave me a valid solution for my problem... and I don't think I'm the one with this need.

I could face the problem another way, by binding the column to the subentity itself, but then I have to implement ICompare at entity level, which is tedious and only allows me to sort with a fixed criteria, and even further, it won't allow me any kind of filtering with the data.

One thing is if you tell me "this is a know issue, and will be worked out at a given time" but not having even the will to implement this is leaving a product for what we pay unfinished as far as I see.

Cheers

[Atanas Korchev]

Hello Joan Vilariño,

I understand that this is an important requirement for your application. However there a few things which need to be taken into consideration when implementing a new feature. Adding a feature is more than just implementing it in code - someone needs to write tests for it, document it and make an example. Another thing is that we are already feature frozen and cannot add new features before a new release

I have attached a sample application which I think does what you have requested:

1. The grid is bound to an Order object which has a Customer property
2. The Customer has two properties - ID used for editing and Name used for displaying. Sorting is performed by Name
3. When editing a dropdownlist is displayed showing the list with available customers

There are a few important things which need to be noted though:

1. The example would not work with 2010.2.930 because of a known bug. The sample contains the updated JavaScript files which are required. The assembly version is the same though - just the JavaScript files are updated.
2. The editor template is not using Html.DropDownList as the latter does not allow full control for setting the "name" attribute. That's why a vanilla html <select> is used (the name needs to be set to Customer.ID)

Please try this sample project and let me know if it would suffice. 

Best regards,

Atanas Korchev
the Telerik team

[Brian Coverstone]

I also need this feature.  I would very much appreciate it if you could include something similar to the gentlemen's code in the next build.

The current grid cannot sort and group foreign table fields simultaneously, only one or the other.  If you set it to sort the field properly by referencing the foreign field in the Bind, when you group by that field in the grid, records without a match in the foreign table never show up.  If you set the Bind to the foreign key, the grouping works properly, but it will not sort properly, because you are sorting by a GUID.

By allowing us to override the sort field, both the grouping and sorting will work properly.

Here is an example of Telerik's SQL statement generated during a user grouping a column, which appears to be the culprit.  It fails to show records that do not have a foreign record associated with them:

exec sp_executesql N'SELECT [t5].[Category_Id], [t5].[Name], [t5].[Description], [t5].[Date_Entered], [t5].[Category_Type], [t5].[Allow_Quantity], [t5].[Order], [t5].[Parent_Category_Id]
FROM (
    SELECT TOP (10) [t3].[Category_Id], [t3].[Name], [t3].[Description], [t3].[Date_Entered], [t3].[Category_Type], [t3].[Allow_Quantity], [t3].[Order], [t3].[Parent_Category_Id], [t4].[Name] AS [Name2]
    FROM (
        SELECT [t2].[Category_Id], [t2].[Name], [t2].[Description], [t2].[Date_Entered], [t2].[Category_Type], [t2].[Allow_Quantity], [t2].[Order], [t2].[Parent_Category_Id]
        FROM (
            SELECT [t0].[Category_Id], [t0].[Name], CONVERT(VarChar(MAX),[t0].[Description]) AS [Description], [t0].[Date_Entered], [t0].[Category_Type], [t0].[Allow_Quantity], [t0].[Order], [t0].[Parent_Category_Id]
            FROM [dbo].[Categories] AS [t0]
            WHERE [t0].[Category_Id] = @p0
            UNION ALL
            SELECT [t1].[Category_Id], [t1].[Name], CONVERT(VarChar(MAX),[t1].[Description]) AS [Description], [t1].[Date_Entered], [t1].[Category_Type], [t1].[Allow_Quantity], [t1].[Order], [t1].[Parent_Category_Id]
            FROM [dbo].[Categories] AS [t1]
            WHERE [t1].[Category_Type] = @p1
            ) AS [t2]
        ) AS [t3]
    LEFT OUTER JOIN [dbo].[Categories] AS [t4] ON [t4].[Category_Id] = ([t3].[Parent_Category_Id])
    ORDER BY [t4].[Name], [t3].[Parent_Category_Id], [t3].[Order]
    ) AS [t5]
WHERE @x1 = [t5].[Name2]
ORDER BY [t5].[Name2], [t5].[Parent_Category_Id], [t5].[Order]',N'@p0 uniqueidentifier,@p1 int,@x1 varchar(8000)',@p0='00000000-0000-0000-0000-000000000000',@p1=2,@x1=NULL

You will see at the bottom the "WHERE @x1 = [t5].[Name2]" and @x1 is passed in as NULL.  This statement keeps the group by from working properly.  Even though a LEFT OUTER JOIN is used, the WHERE clause technically makes it an INNER JOIN because the foreign record MUST exist for the WHERE clause to allow a record to be returned.

I would very much appreciate if this could be fixed.

[Itay]

Greate Post ! 
Works for me. 

------
 [Serializable]
    public class ShiftUsersModel
    {
     
        public Role Role { get; set; }

    }
}

---
public class Role
{
  int roleID {get; set;}
 [UIHint("Role"), Required]
  string roleName {get; set;}
}

** Just Create an (.cshtml / .ascx) view file for the dropdown on edit mode
also work with the razor view 

tnx telerik

Best regards,
Itay Ben Ner

[Travis]

I agree, it would be nice to have this feature.