How to fetch topics ordered by custom field iso8601 value?

[Falco]

@Stefan_Brighiu_M

.order("case when custom_fields.value IS NULL then topics.created_at else cast(custom_fields.value as timestamp) end desc")

You will want to change this into:

.order("COALESCE(CAST(custom_fields.value AS timestamp), custom_fields.value) DESC")

for readability.

@Stefan_Brighiu_M

For the life of me i cannot figure out why the hell the first entry is not at the end

You sort by sortDate and when that is missing you use createdAt. All in descending order. The query is returning the data exactly as asked as 2020 is “greater” than the 2011 second place.

If you want nulls last, you need:

.order("CAST(custom_fields.value AS timestamp DESC NULLS LAST")