1-888-365-2779
+1-888-365-2779
Try Now
More in this section

Forums / Developing with Sitefinity / Query for all Generic Content "drafts"

Query for all Generic Content "drafts"

2 posts, 0 answered
  1. Connections Academy Developer
    Connections Academy Developer avatar
    151 posts
    Registered:
    04 May 2005
    16 Jul 2010
    Link to this post
    I was trying to write a SQL query to list all generic content items with the most current version as "draft," but I couldn't figure out where this was stored. How can I determine if a version of a generic content item is a draft in SQL?
  2. Connections Academy Developer
    Connections Academy Developer avatar
    151 posts
    Registered:
    04 May 2005
    16 Jul 2010
    Link to this post
    I figured it out. In case anyone else needs this, here's my code:

    SELECT DISTINCT TOP 1000 url, sf_VersionItem.Version, sf_Activity.Status
    FROM sf_CmsContentVersion JOIN sf_CmsContentBase
        ON sf_CmsContentVersion.ID = sf_CmsContentBase.ID
    JOIN sf_VersionItem
        ON sf_CmsContentVersion.ID = sf_VersionItem.ID
    JOIN sf_VrsTxtData
        ON sf_VrsTxtData.ItemID = sf_VersionItem.id
    JOIN sf_WorkflowInstance
        ON sf_VrsTxtData.DataImpl = sf_WorkflowInstance.ID
    JOIN sf_Activity
        ON sf_Activity.ID = sf_WorkflowInstance.Activity_ID
    WHERE sf_VersionItem.Application = '/'
        AND KeyValue = 'WorkflowInstanceId'

    Status 1 = Draft, 3 = Published.
2 posts, 0 answered