<p>Okay, here's my stab at this. No doubt Brad will swagger in at some point, and declare that this can all be done with one line of trivial PHP.</p>

<p>Whatever, Brad. Moving on...</p>

<p>You can't use the <code>ElementCriteriaModel</code> to retrieve specific entry versions. This leaves you with two options:</p>

<h2>Option 1: Use the Entry Revisions service</h2>

<p>The <code>EntryRevisionsService</code> has <a href="https://craftcms.com/classreference/services/EntryRevisionsService#getVersionsByEntryId-detail" rel="nofollow noreferrer">a <code>getVersionsByEntryId</code> method</a>, which does precisely what it says. If you're only dealing with a small number of Entries, this may suffice.</p>

<h2>Option 2: Go old(ish) school</h2>

<p>If the first option doesn't suit, you'll need to get your hands dirty querying the database, and populating <code>EntryModel</code> instances from the result.</p>

<p>Here's an example, to get you started:</p>

<pre><code>$versionIds = [];

// Retrieve all the versions created before your "threshold" date.
$versions = craft()-&gt;db-&gt;createCommand()
    -&gt;select(['ev.entryId as entryId', 'ev.id as versionId'])
    -&gt;from('entryversions ev')
    -&gt;where('ev.dateCreated &lt; :date', [':date' =&gt; '2017-03-20'])
    -&gt;order('ev.dateCreated desc')
    -&gt;queryAll();

/**
 * Construct an associative array where the key is the entry ID,
 * and the value is the most recent entry version ID created _before_
 * your date threshold.
 */
foreach ($versions as $version) {
    if (!array_key_exists($version['entryId'], $versionIds)) {
        $versionIds[$version['entryId']] = $version['versionId'];
    }
}

/**
 * Finally, we can do something useful with our lives.
 * Retrieve the desired version of the entries.
 *
 * Note: you'll need to retrieve all of the required entry
 * columns, in order to populate your EntryModel. I've just
 * done a wildcard, you may wish to be more specific.
 */
$results = craft()-&gt;db-&gt;createCommand()
    -&gt;select('e.*, ev.*')
    -&gt;from('entries e')
    -&gt;join('entrytypes et', 'et.id = e.typeId')
    -&gt;join('entryversions ev', 'ev.entryId = e.id')
    -&gt;where('et.handle = :handle', [':handle' =&gt; 'entryTypeHandle'])
    -&gt;andWhere(['in', 'ev.id', array_values($versionIds)])
    -&gt;group('e.id')
    -&gt;order('ev.dateCreated desc')
    -&gt;queryAll();
</code></pre>

<p>Now that you've retrieved the results, you'll need to convert each row to an <code>EntryModel</code>. This could be fairly trivial, or a living hell, depending on whether your Entries contains Matrix fields, Relationships, and so forth.</p>

<hr>

<p>Note: I don't <em>think</em> it's possible to do this in a single query, but your <code>GROUP BY</code> fu may be better than mine.</p>

CC BY-SA 3.0
2017-03-21T18:53:18.483Z
I don't swagger, Stephen.
Brad Bell
CC BY-SA 3.0
2017-03-21T18:53:56.970Z
*stumble. We regret the error.
Stephen Lewis
CC BY-SA 3.0
2017-03-21T18:54:51.360Z
Only thing I'd add is that depending on the number of versions returned, populate (Entry)Models can be quite expensive memory wise in Craft 2. In which case, you might just want to return the exact data you're looking for.
Brad Bell
CC BY-SA 3.0
2017-03-21T21:07:09.933Z
Hmm, OK. If I created a MySQL view that gives me week old data from the versions table, is there a way for the Element API plugin to query that view rather that the latest Entry versions?
emarref
CC BY-SA 3.0
2017-03-21T21:11:04.113Z
No, is the short answer. It's also the long answer, but Stack Exchange won't let me type a two-character comment.
Stephen Lewis
CC BY-SA 3.0
2017-03-21T16:02:59.010Z
2017-03-21T17:33:10.400Z
2017-03-21T17:33:10.400Z
Stephen Lewis
<p>I am building an API that returns instances of an entry type. Only authenticated users can access the API. There are different tiers of user - paid and free. Free users can access the API, but the content should only be updated weekly. I have a scheduled task that regularly updates my entries.</p>

<p>How can I query the database for a particular entry type, but only retrieve versions of entries that are at least 7 days old?</p>

<query><entry-versions>
How can I retrieve older versions of a custom entry type in bulk?
# How can I retrieve older versions of a custom entry type in bulk?

(Asked by: username_0 on 2017-03-21)

I am building an API that returns instances of an entry type. Only authenticated users can access the API. There are different tiers of user - paid and free. Free users can access the API, but the content should only be updated weekly. I have a scheduled task that regularly updates my entries.

How can I query the database for a particular entry type, but only retrieve versions of entries that are at least 7 days old?

**Tags:** query, entry-versions

---

## 1 answers

### Answer by username_1 on 2017-03-21. Score: 1 | Accepted answer

Okay, here's my stab at this. No doubt Brad will swagger in at some point, and declare that this can all be done with one line of trivial PHP.
Whatever, Brad. Moving on...
You can't use the ElementCriteriaModel to retrieve specific entry versions. This leaves you with two options:
Option 1: Use the Entry Revisions service
The EntryRevisionsService has a getVersionsByEntryId method, which does precisely what it says. If you're only dealing with a small number of Entries, this may suffice.
Option 2: Go old(ish) school
If the first option doesn't suit, you'll need to get your hands dirty querying the database, and populating EntryModel instances from the result.
Here's an example, to get you started:
$versionIds = [];

// Retrieve all the versions created before your "threshold" date.
$versions = craft()->db->createCommand()
    ->select(['ev.entryId as entryId', 'ev.id as versionId'])
    ->from('entryversions ev')
    ->where('ev.dateCreated < :date', [':date' => '2017-03-20'])
    ->order('ev.dateCreated desc')
    ->queryAll();

/**
 * Construct an associative array where the key is the entry ID,
 * and the value is the most recent entry version ID created _before_
 * your date threshold.
 */
foreach ($versions as $version) {
    if (!array_key_exists($version['entryId'], $versionIds)) {
        $versionIds[$version['entryId']] = $version['versionId'];
    }
}

/**
 * Finally, we can do something useful with our lives.
 * Retrieve the desired version of the entries.
 *
 * Note: you'll need to retrieve all of the required entry
 * columns, in order to populate your EntryModel. I've just
 * done a wildcard, you may wish to be more specific.
 */
$results = craft()->db->createCommand()
    ->select('e.*, ev.*')
    ->from('entries e')
    ->join('entrytypes et', 'et.id = e.typeId')
    ->join('entryversions ev', 'ev.entryId = e.id')
    ->where('et.handle = :handle', [':handle' => 'entryTypeHandle'])
    ->andWhere(['in', 'ev.id', array_values($versionIds)])
    ->group('e.id')
    ->order('ev.dateCreated desc')
    ->queryAll();

Now that you've retrieved the results, you'll need to convert each row to an EntryModel. This could be fairly trivial, or a living hell, depending on whether your Entries contains Matrix fields, Relationships, and so forth.

Note: I don't think it's possible to do this in a single query, but your GROUP BY fu may be better than mine.

> **Comments:**
> **username_2**: I don't swagger, Stephen.  
> **username_1**: *stumble. We regret the error.  
> **username_2**: Only thing I'd add is that depending on the number of versions returned, populate (Entry)Models can be quite expensive memory wise in Craft 2. In which case, you might just want to return the exact data you're looking for.  
> **username_0**: Hmm, OK. If I created a MySQL view that gives me week old data from the versions table, is there a way for the Element API plugin to query that view rather that the latest Entry versions?  
> **username_1**: No, is the short answer. It's also the long answer, but Stack Exchange won't let me type a two-character comment.  

---

