Ordering entries based on an eager-loaded element's custom field

Question

I have a query that loads a set of movies, and eager-loads corresponding shows, which are related elements. For now, the movies are sorted by startingDate, which is a custom field in the movie entry:

{% set currentMovies = craft.entries({
        section: 'movies',
        type:    'movie',
        order:   'startingDate asc',
        limit:   null,
        with: [
            'shows',
            'poster',
            'stills'
        ]
    })
%}

What I actually want is to sort the movies by whichever film has the first upcoming screening. That basically means I would like to order the set by something to the effect of order: shows.showTime or order: shows[0].showTime, but that doesn't seem to work.

Is there a way to order by a field on a related entry, or is that variable simply not yet accessible at the point of ordering?

I tried to eager-load the shows.showtime field, but realised that doesn't work either, as eager-loading only makes sense for relations, not fields themselves.

Answer 1

This requires a bit of extra work on the ElementQuery, but it can be done in the same query.

We need to perform an inner join on the relation between the movie and the show and then another inner join on the show's content table. Finally we can order on the custom field of the show's content table.

{% set currentMovies = craft.entries()
    .section('movies')
    .type('movie')
    .limit(null)
    .with([
        'shows',
        'poster',
        'stills'
    ])
    .innerJoin('{{%relations}} shows_relation', '[[shows_relation.sourceId]] = [[elements.id]]')
    .innerJoin('{{%content}} shows_content', '[[shows_content.elementId]] = [[shows_relation.targetId]]')
    .orderBy('shows_content.field_startingDate asc')
%}