order_by lambda as string fails to update with column name changes

[combinatorist]

Describe the current behavior

If you sort_by a column and then change that column name, it doesn't update the name in your sort string. This causes a silent semantic error of nonsensical results.

Steps to reproduce

1. open this document
2. duplicate it
3. and rename the number column
4. observe that the other columns lose their sorting

(I completed steps 2-4 for you, resulting in this other document, with just the column's first letter capitalized).

Describe the expected behavior

Grist should update the column reference so that this sorting feature is safe under column renames just like most formulas.

If inputting the formula as a string is just a way to give meaning to the negative sign, then I would argue we should get rid of the negative sign (per #1352). Or, if it is just a hack to access sqlite's own sorting, then I would argue, we should make it work on just a single column. Users can always support more complex logic by adding a custom column with the lambda fucntion they need.

Where have you encountered this bug?

• On docs.getgrist.com
• On a self-hosted instance

Instance information (when self-hosting only)

No response

[dsagal]

Use the order_by parameter instead of sort_by, see https://support.getgrist.com/functions/#lookuprecords. The order_by parameter supports more options (such as tuples and reverse order), and should behave correctly for renaming columns. The sort_by is deprecated, but still supported for backward compatibility.

[combinatorist]

Thanks for clarifying that order_by is better - I didn't catch the significance of "falls back to RowID" when I read the docs the first time.

However, for this specific bug, order_by still uses in string names for the fields according to documentation and when I try to give it raw field symbols (e.g. order_by=$number), it gives me a "KeyError". I must admit, I still don't understand why the order_by relies on a string representation. It smells of an implementation wart to me. I'm guessing it's hard to build it with the full functionality of true field, legible to grist, but is there some known reason it is intrinsically impossible that I'm missing?

[dsagal]

Yes, it is because $number is the value of the "number" column in the current row, like 5 or 17. That is its meaning in every other formula context (e.g. when you say $number * 2). But for Table1.lookupRecords(Person=$id, order_by="number"), it's important for the function to know which field you intend to order by, rather than a particular value of that field. The easiest way to identify the field is by its string name.

We could come up with other alternatives, but those would be more verbose, and perhaps less standard. E.g. Django specifies sort order using a string field name too: https://www.w3schools.com/django/django_queryset_orderby.php.