Loading different entry revisions in ExpressionEngine 2 per environment

Question

I'm developing a website using ExpressionEngine 2, which will sit on both a staging and production environment, both with their own database. We will be running a cron job to synchronise the databases and file structure on an daily basis.

The control panel will only be accessible on the staging environment. Here, 3 user groups can follow a workflow I have created using the status groups:

• Step 1 - "Editor" edits the entry and changes the status to edited, submits.
• Step 2 - "Approver" checks the content and changes the status to approved, submits.
• Step 3 - "Publisher" changes the status to published, submits.

The staging environment will show any changes to content at step 1, meaning we can test the front-end of the site as required, when a change has been submitted. Whereas, the production environment is only displaying the changes if the status of all 3 steps have been competed (status=published). This is exactly how we need the environments to work.

The problem arises when we need to edit a page that has previously been published. If an editor is to edit an entry and the other two steps aren't completed in the window before the next synchronise, the content from the page will vanish on production (since status=edited).

We understand that a solution for this could potentially be found by using the 'Entry revisions' (http://ellislab.com/expressionengine/user-guide/cp/content/publish.html#revisions-tab). If we were able to grab the last published revision of an entry if status!=published, we could display this on the production environment until the page next reaches a published state.

Can anyone tell me if this is at all possible?

If not, can anyone suggest any other potential solutions?

Thank you, Chris

Answer 1

Chris, this is something of a quandary that has lingered in EE (and any relational db environment) for ages.

Our general workflow is that content is always published in production and only template development is tested in staging.

I had an issue years ago using another CMS where we were trying to setup this kind of synchronisation but we found there wasn't a way to do it without overwriting authors at some point.