How can I retrieve older versions of a custom entry type in bulk?

Question

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?

Answer 1

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.