Get a Sitecore item from the master DB that is the version that would be published if the site was published now

Question

I am working on a data export from Sitecore, from the master database. It gets data from items of a certain type and puts it into custom XML. I am able to generate the XML I need but now the client has asked that instead of returning data from the most recent version it return data from the version that would be published if the site was published at the time the export was performed.

The nature of the site is that it must be published on a regular but infrequent basis (once a year, I believe) but the data will be maintained continuously and anything approved in Sitecore needs to be added to another system, so doing the export from the web database is not an option, unfortunately. I am hoping to hook into whatever mechanism Sitecore uses to decide which version goes to the Web DB when/if publishing happens without actually publishing.

I have looked at Item.Publishing.GetValidVersion, IsValid, and IsPublishable but have been unsuccessful in putting them to use. I have read the Sitecore docs for those methods but the description just says "Gets the valid version." and I am not sure what it means by "valid".

Here is my latest attempt:

foreach (Sitecore.Data.ID courseId in courseIds)
{
  Sitecore.Data.Items.Item course = master.GetItem(courseId);
  string initialVersion  = course.Version.ToString();
  if (course != null & course.Parent.Name != "Narrative-Courses")
  {
    course = course.Publishing.GetValidVersion(DateTime.Now, true, true);
    string finalVersion = course.Version.ToString();
    if (initialVersion != finalVersion)
    {
      log(course.Name + ": " + initialVersion + ", " + finalVersion);
    }
    if (course != null)
    {
    //add xml for this item
...

My log is blank and my XML includes the highest number version of each item, regardless of workflow state. When an item has version 1 in an Approved state and version 2 in a Draft state, I only get version 2 in the XML.

Am I misinterpreting what those methods are for or just misusing them?

Answer 1

You can accomplish this two ways:

1. Instead of getting items from the master database, go directly to the web database which always has the most recent public item versions.
2. Create another publishing target (perhaps called "export", with an export database) and make your code run on the export database. When you want to do an actual export with your code, publish to the export publishing target then run your code against that database.

Answer 2

I ended up writing something to check if the current version is approved (in a workflow state marked final) and, if not, go through the older versions to find the latest one that is approved. I have not tested this rigorously but it does seem to be working correctly. It would have been clearer if I had iterated through in reverse order and returned the first one that was approved but I think this does the same thing.

public Item GetBestVersion(Item item)
{
Item returnItem = null;
string wfIdString = item.Fields[Sitecore.FieldIDs.WorkflowState].Value;
if (Sitecore.Data.ID.IsID(wfIdString))
{
    if (wfIdString != "")
    {
        using (new Sitecore.SecurityModel.SecurityDisabler())
        {
            Sitecore.Data.Database master = Sitecore.Data.Database.GetDatabase("master");
            Sitecore.Data.ID workflowStateId = Sitecore.Data.ID.Parse(wfIdString);
            Sitecore.Data.Items.Item wftarget = master.GetItem(workflowStateId);
            if (wftarget != null)
            {
                Sitecore.Data.ID finalId = Sitecore.Data.ID.Parse("{FB8ABC73-7ACF-45A0-898C-D3CCB889C3EE}");
                if (wftarget.Fields[finalId].Value == "1")
                {
                    return item;
                }
            }
        }
    }
}

foreach (Sitecore.Data.Items.Item oldVersion in item.Versions.GetOlderVersions())
{
    string oldWfIdString = oldVersion.Fields[Sitecore.FieldIDs.WorkflowState].Value;
    if (Sitecore.Data.ID.IsID(oldWfIdString))
    {
        if (oldWfIdString != "")
        {
            using (new Sitecore.SecurityModel.SecurityDisabler())
            {
                Sitecore.Data.Database master = Sitecore.Data.Database.GetDatabase("master");
                Sitecore.Data.ID workflowStateId = Sitecore.Data.ID.Parse(oldWfIdString);
                Sitecore.Data.Items.Item wftarget = master.GetItem(workflowStateId);
                if (wftarget != null)
                {
                    Sitecore.Data.ID finalId = Sitecore.Data.ID.Parse("{FB8ABC73-7ACF-45A0-898C-D3CCB889C3EE}");
                    if (wftarget.Fields[finalId].Value == "1")
                    {
                        returnItem = oldVersion;
                    }

                }
            }
        }
    }
}
return returnItem;
}