3

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.

4

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')
%}
  • Thanks, Ben, I somehow only saw this now! I don't quite get it to work. First problem was the commas at the end of each line, as you re-wrote it to dot notation, but that was fine. But removing those, I get an "Integrity constraint violation" with the error message "Column 'elementId' in on clause is ambiguous". Any idea what to do with that? – KSP Aug 7 at 10:30
  • After digging around a bit, it seems that elementId needs to be elements.id. But the resulting query is causing an out of memory error. When I test the SQL statement in myPhpAdmin, I get tons of duplicates for every movie, resulting in 21696 hits, although there are actually only 16 movies to return. So I guess this is what causes it to run out of memory. Any idea why the query would suddenly return so many results? I'm really a novice when it comes to databases and SQL. To be clear, the movies are the source element, on to which the shows are added via an entries field. – KSP Aug 7 at 13:14
  • Some more digging … limiting the query to a specific movie, which only has one screening published, it returns that movie 64 times. So seems that there is some fundamental flaw in the joins somehow. – KSP Aug 7 at 13:38
  • I've updated the answer with your suggestions. If you're not familiar with SQL then perhaps this approach is overly complex and a simpler (although less performant) approach that doesn't use eager-loading might be better to take. – Ben Croker Aug 8 at 12:34
  • 1
    I know a tiny bit of SQL, but you’re right it’s probably not worth it. Then again I’m getting a bit stubborn, and would like to understand this :) The issue seems to be that the movies are related to multiple shows, so I get duplicates. With .addSelect() and .distinct() I’m a bit closer, so hopefully on the right track. Thanks so far, and wish me luck! ;) – KSP Aug 8 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.