Editor: order function for options serverside error

jgessinger

Hi Allan,

I have problems with ordering the options of select lists in .NET.

I start with an example. We have the table "Test" in the database:
Name | Sequence | IsDefault
Test1 | 4 | true
Test2 | 3 | false
Test3 | 1 | false
Test3 | 2 | false

My controller action is defined like this:

return new Editor(db, "Test", "Name")
    .Field(new Field("Test.Name")
        .Options("Test", "Name", "Name", q => q
            .Where("IsDefault", false)
            .Order("Sequence")
        )
    )
    .Process(request)
    .Data();

In my output I get an alert with "DataTables warning: table id=... - ORDER BY items must appear in the select list if SELECT DISTINCT is specified."
When I remove the "Where" statement in the Options, I get the error "DataTables warning: table id=... - Incorrect syntax near ')'."
Without the "Order" function everything is working fine!

These are server side errors, that are submitted in the json response.

So this is a down broken example. In my big table whenever I add the Order function I get the error "DataTables warning: table id=... - Es ist ein Fehler aufgetreten. Bitte überprüfen Sie Ihre Eingaben." (An error occured. Please check your input.)

Is there maybe a problem in your .NET library? Could you check this for me?

allan

As the error message from the SQL server notes, the issue is that Sequence is not in the list of fields that will be selected (only Name currently is) and therefore you can't sort on a field that isn't included.

You could use new []{"Sequence", "Name"} as the third parameter for the Field.Options() method which will include that field allowing it to be sorted correctly. That would result in the Sequence and Name being concatenated together by default. You could use the fifth parameter for the Field.Options() method to define your own renderer if you only want the name to be shown.

Regards,
Allan

jgessinger

I tried your suggestion:

return new Editor(db, "Test", "Name")
    .Field(new Field("Test.Name")
        .Options("Test",
            "Name",
            new[] {"Sequence", "Name"},
            q => q
                .Where("IsDefault", false)
                .Order("Sequence"),
            row => row["Name"].ToString()
        )
    )
    .Process(request)
    .Data();

This will output just the name in my select dropdown. But when I remove the 4th parameter, it also just displays the name and not the sequence concatenated to it. And unfortunately the output in the select list is not ordered!

When I change 'row => row["Name"].ToString()' to 'row => row["Sequence"].ToString()' I get my sequences as output in the select list. This is ordered now, but just because of the option labels (that represents the sequence).

When I change '.Order("Sequence")' to '.Order("Sequence DESC")' I get the same output as above.

I checked the script response and in my 'options' key, the array entries are always ordered by the label.

(Btw. the last error message in my first post was a custom one by me, sorry )

allan

What version of the .NET libraries are you using? If not 1.5.6 could you update to that version please.

Allan

jgessinger

Its 1.5.6. I also started with this version.

allan

I see the problem now - apologies I had thought the ordering was done in SQL, but it isn't, it is done using the label that is created (in Field.cs):

            return rows.Select(row => new Dictionary<string, object>
            {
                {"value", row[OptsValue]},
                {"label", formatter(row)}
            }).OrderBy(x => x["label"]).ToList();

Unfortunately that means that it isn't quite so easy to change the order. Options:

1. Alter the Field code so you can use an SQL order by clause (i.e. just remove the orderBy linq command.
2. Change the order of the data on the client-side (initComplete) - although that might be a little tricky.

Perhaps the Options parameter needs one more option - the order by column. If specified then it wouldn't do its own custom ordering.

Allan

jgessinger

Thank you for your reply and your suggestions Allan.

It would be great if you add this as task for the new editor version.

allan

It should be in 1.6, which is probably around 2 months away (ish!).

Allan

allan

Just to note, this ability has now been committed into Editor's 1.6 branch and will be included in the 1.6.0 release. I currently expect that to be early December.

Allan