Support fallback column for sorting when the value is NULL

[rostaingc]

Is your feature request related to a problem? Please describe.

I have a list of tasks that is sorted and paginated. Each task can have a plannedStartDate and an actualStartDate.

I show the tasks in a table with a column 'Start date'

• if the task has an actualStartDate it displays the actualStartDate
• if actualStartDate is null, it displays the plannedStartDate

I would like to sort my list with nestjs-query on the 'Start Date' equivalent to this SQL:

ORDER BY CASE 
    WHEN actualStartDate IS NOT NULL THEN actualStartDate
    ELSE plannedStartDate
END

If I put the columns actualStartDate and plannedStartDate in the SortField list, it will sort the tasks by actualStartDate first and then by plannedStartDate, but it will not 'mix' the two and sort the tasks on this combined column.

Have you read the Contributing Guidelines?

Yes

Describe alternatives you've considered For the moment the only solution I see it to use a custom SQL query instead of nestjs-query.

[TriPSs]

How would you propose to fit this inside the Query<T>?

[ValentinVignal]

What about something like

sorting = [
  {
    field: 'actualStartDate'
    fieldFallback: {
      field: 'plannedStartDate'
    }
]

The typing could be:

export interface FallbackSortField<T> {
  field: keyof T;
  fieldFallback: FallbackSortField<T>;
}

export interface SortField<T> extends FallbackSortField<T> {
  direction: SortDirection;
  nulls?: SortNulls;
}

What do you think?

[TriPSs]

Interesting @ValentinVignal, could you make a PR?

[ValentinVignal]

I can give it a try :)