Order entries by DATEDIFF

Question

I'm trying to order the results of an entries query by the difference between two date fields but I can't seem to get it to work.

Here is my query:

{% set todaysStreamsParams = {
  section : 'films',
  orderBy : "DATEDIFF(`screeningDateFirst`, `screeningDateLast`) DESC",
  limit   : 5,
} %}

{% set todaysStreams = craft.entries(todaysStreamsParams).all() %}

But I'm getting the following database exception:

Unknown column 'screeningDateFirst' in 'order clause.

Any tips?

Answer 1

Digging around a bit, it looks like more complex order queries need to be fully elaborated. I adjusted as follows for it to work:

orderBy : "DATEDIFF(`content`.`field_screeningDateFirst`, `content`.`field_screeningDateLast`) DESC",