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?


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

 * 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)])
    ->order('ev.dateCreated desc')

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.

  • I don't swagger, Stephen.
    – Brad Bell
    Mar 21 '17 at 18:53
  • 1
    *stumble. We regret the error. Mar 21 '17 at 18:53
  • 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
    Mar 21 '17 at 18:54
  • 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
    Mar 21 '17 at 21:07
  • 1
    No, is the short answer. It's also the long answer, but Stack Exchange won't let me type a two-character comment. Mar 21 '17 at 21:11

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.