<p>You can use <a href="https://craftcms.com/docs/4.x/element-queries.html#advanced-element-queries" rel="nofollow noreferrer">Advanced Element Queries</a> to optimize long-running queries that load a lot of data. This involves limiting what you're selecting and then using an appropriate method to execute the query to avoid overhead. For example, let's say you're currently finding the date of the most recently created related page like this:</p>
<pre class="lang-twig prettyprint-override"><code>{% set mostRecentAuthorEntry = craft.entries()
    .section('blog')
    .relatedTo(author)
    .orderBy('postDate DESC')
    .one()
%}
{% set updateDate = mostRecentAuthorEntry.postDate ?? null %}
</code></pre>
<p>This loads all the fields of that entry and populates an entire <code>Entry</code> object, even though you only need the <code>postDate</code>. You can optimize that query to only load the <code>postDate</code>:</p>
<pre class="lang-twig prettyprint-override"><code>{% set updateDate = craft.entries()
    .section('blog')
    .relatedTo(author)
    .orderBy('postDate DESC')
    .select('postDate')
    .scalar()
%}
</code></pre>
<p>This will return the date as a string (keep in mind that the result may be <code>null</code> if no related entries are found). One caveat is that this value will <em>not</em> be passed through the field's <a href="https://docs.craftcms.com/api/v4/craft-base-fieldinterface.html#method-normalizevalue" rel="nofollow noreferrer"><code>normalizeValue</code> method</a>, so it will be 'raw'. For dates, this means you have to set the correct system timezone to get the right date out. You can use <a href="https://docs.craftcms.com/api/v4/craft-helpers-datetimehelper.html#method-todatetime" rel="nofollow noreferrer"><code>DateTimeHelper::toDateTime</code></a> for that.</p>
<p>You can apply the same principle to retrieve multiple values. For example, if you need an array of <code>postDate</code> values of multiple entries, use the same select statement as above and then execute the query with <a href="https://www.yiiframework.com/doc/api/2.0/yii-db-query#column()-detail" rel="nofollow noreferrer"><code>column()</code></a>. If you need multiple values from multiple entries, use <a href="https://www.yiiframework.com/doc/api/2.0/yii-db-query#select()-detail" rel="nofollow noreferrer"><code>select()</code></a> to only select the data you need and use <a href="https://craftcms.com/docs/4.x/entries.html#asarray" rel="nofollow noreferrer"><code>asArray()</code></a> to return the results as associative arrays instead of <code>Entry</code> objects to avoid the overhead of object creation.</p>
<p>Read the <a href="https://craftcms.com/docs/4.x/element-queries.html#advanced-element-queries" rel="nofollow noreferrer">documentation linked above</a>, as well as the <a href="https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder" rel="nofollow noreferrer">Yii2 Query Builder</a> article to learn more.</p>

CC BY-SA 4.0
2023-02-23T10:15:40.780Z
2023-02-23T10:15:40.780Z
MoritzLost
<p>I have a few sections whose main purpose is to be related to other entries - think authors' names, so a user can click through to all their entries.</p>
<p>On the templates for these pages - as well as tag pages - I have called the date of the last entry as the 'updated' date, as technically that is when the page as we see it will have changed.</p>
<p>I tried to do similar for a sitemap, but unsurprisingly, these did not load as it was running that query for many entries at once. It's not a make-or-break, but something that is bugging me: for one of these sections there are thousands of entries. Is there some simpler way around this?</p>
<p>I found an answer on here about categories, but could not for the life of me get that to work for two sections.</p>

<datetime><relatedto><queries>
Date of latest (parent) related entry?
# Date of latest (parent) related entry?

(Asked by: username_0 on 2023-02-16)

I have a few sections whose main purpose is to be related to other entries - think authors' names, so a user can click through to all their entries.

On the templates for these pages - as well as tag pages - I have called the date of the last entry as the 'updated' date, as technically that is when the page as we see it will have changed.

I tried to do similar for a sitemap, but unsurprisingly, these did not load as it was running that query for many entries at once. It's not a make-or-break, but something that is bugging me: for one of these sections there are thousands of entries. Is there some simpler way around this?

I found an answer on here about categories, but could not for the life of me get that to work for two sections.

**Tags:** datetime, relatedto, queries


---

## 1 answers

### Answer by username_1 on 2023-02-23. Score: 1

You can use Advanced Element Queries to optimize long-running queries that load a lot of data. This involves limiting what you're selecting and then using an appropriate method to execute the query to avoid overhead. For example, let's say you're currently finding the date of the most recently created related page like this:
{% set mostRecentAuthorEntry = craft.entries()
    .section('blog')
    .relatedTo(author)
    .orderBy('postDate DESC')
    .one()
%}
{% set updateDate = mostRecentAuthorEntry.postDate ?? null %}

This loads all the fields of that entry and populates an entire Entry object, even though you only need the postDate. You can optimize that query to only load the postDate:
{% set updateDate = craft.entries()
    .section('blog')
    .relatedTo(author)
    .orderBy('postDate DESC')
    .select('postDate')
    .scalar()
%}

This will return the date as a string (keep in mind that the result may be null if no related entries are found). One caveat is that this value will not be passed through the field's normalizeValue method, so it will be 'raw'. For dates, this means you have to set the correct system timezone to get the right date out. You can use DateTimeHelper::toDateTime for that.
You can apply the same principle to retrieve multiple values. For example, if you need an array of postDate values of multiple entries, use the same select statement as above and then execute the query with column(). If you need multiple values from multiple entries, use select() to only select the data you need and use asArray() to return the results as associative arrays instead of Entry objects to avoid the overhead of object creation.
Read the documentation linked above, as well as the Yii2 Query Builder article to learn more.


---

