How to sort View on rewritten field?

Question

I have a View with a table display format and all columns are set as being sortable. Although the issue isn't specific to Date fields; I am specifically trying to figure out an issue with Date fields.

I have 2 Date fields, one is primary and the other is secondary. I have tried various ways in adding the login for "if (pri) pri, else sec".

1. using no results option for pri date field
2. using twig in a custom text field

The results are:

1. sorts only on the base field; not the rewritten value
2. wsod when selecting to sort on this column

I have tried this both with db search as well as Search API - same results. I seem to be able, with Search API, to create an aggregated "First" field which seems to work; although the date format is not adjustable. I have also seen a patch which allows formatting of the base date fields; but my guess is that this will then give a string sorting of the aggregated field (i.e. Dec comes before June).

My next shot is to use the aggregated date field formatted as SOLR is giving by default and assume that is a valid date sort (I think it is, YYYY-MM-DD) and then do up a field formatter to display the correct date format.

Answer 1

I think the quick answer is "you can't sort on a rewritten value" in Views; regardless if it's a date field or using Search API.

There are however; various workarounds depending on the particular issue. I have used the following now:

• use Computed Field to store the rewritten value in the db and then display/sort on that
• SearchAPI is capable of doing simple rewrites with its Aggregated Field; this may work for simple rewrites.

You can often combine a computed field and a rewrite in Views to take advantage of Views field formatting. Keep in mind that the sorting occurs on the original db value for the field. So, for my example above, you could do a computed field to store the timestamp of the Date Range's end date; and then add that to the View but rewrite that field with the formatted end date. This will then sort on the timestamp; but display the properly formatted value.