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

<p>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.</p>

<pre><code>{% 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')
%}
</code></pre>

CC BY-SA 4.0
2019-08-07T10:30:35.407Z
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
CC BY-SA 4.0
2019-08-07T13:14:53.780Z
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
CC BY-SA 4.0
2019-08-07T13:38:09.280Z
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
CC BY-SA 4.0
2019-08-08T12:34:38.147Z
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
CC BY-SA 4.0
2019-08-08T15:36:53.600Z
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
CC BY-SA 4.0
2019-08-08T15:39:19.530Z
What happens if you add `.groupBy('elements.id')`?
Ben Croker
CC BY-SA 4.0
2019-08-08T15:40:18.330Z
Ah, good idea! Will try it out
KSP
CC BY-SA 4.0
2019-08-09T20:01:26.707Z
Apparently the `.groupBy()` is not really helping much, as it basically needs something distinct to group on. To get this to work, I would basically need to some kind of more complex sub query, and only get the next upcoming show from each movie, and none of the others. And that is becoming a bit overkill. That said – I should be able to use more or less the same code for the daily program, to fake a kind of "reverse eager-loading". That should be simpler, as every show is only related to one film.
KSP
CC BY-SA 4.0
2019-05-30T16:44:12.533Z
2019-08-08T12:33:42.957Z
2019-08-08T12:33:42.957Z
Ben Croker
<p>I have a query that loads a set of <code>movies</code>, and eager-loads corresponding <code>shows</code>, which are related elements. For now, the movies are sorted by <code>startingDate</code>, which is a custom field in the <code>movie</code> entry:</p>

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

<p>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 <code>order: shows.showTime</code> or <code>order: shows[0].showTime</code>, but that doesn't seem to work.</p>

<p>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?</p>

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

<entries><ordering><eager-loading>
Ordering entries based on an eager-loaded element's custom field
# Ordering entries based on an eager-loaded element's custom field

(Asked by: username_0 on 2017-07-29)

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.

**Tags:** entries, ordering, eager-loading


---

## 1 answers

### Answer by username_1 on 2019-05-30. Score: 5

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')
%}

> **Comments:**
> **username_0**: 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?  
> **username_0**: 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.  
> **username_0**: 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.  
> **username_1**: 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.  
> **username_0**: 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! ;)  
> **username_1**: What happens if you add `.groupBy('elements.id')`?  
> **username_0**: Ah, good idea! Will try it out  
> **username_0**: Apparently the `.groupBy()` is not really helping much, as it basically needs something distinct to group on. To get this to work, I would basically need to some kind of more complex sub query, and only get the next upcoming show from each movie, and none of the others. And that is becoming a bit overkill. That said – I should be able to use more or less the same code for the daily program, to fake a kind of "reverse eager-loading". That should be simpler, as every show is only related to one film.  

---

