Skip to content

Instantly share code, notes, and snippets.

@danlister
Last active May 22, 2018 18:47
Show Gist options
  • Save danlister/3bbff7fc433ae01e2f2a to your computer and use it in GitHub Desktop.
Save danlister/3bbff7fc433ae01e2f2a to your computer and use it in GitHub Desktop.
Retrieve a list of Umbraco Documents which have pending changes
DECLARE @NodeId INT
DECLARE my_cursor CURSOR local static read_only forward_only FOR
SELECT DISTINCT nodeid
FROM cmsdocument
OPEN my_cursor
FETCH next FROM my_cursor INTO @NodeId
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @published DATETIME
DECLARE @newest DATETIME
SELECT @published = versiondate
FROM cmscontentversion
WHERE versionid = (SELECT TOP(1) versionid
FROM cmsdocument
WHERE nodeid = @NodeId
ORDER BY updatedate DESC)
SELECT TOP(1) @newest = updatedate
FROM cmsdocument
WHERE nodeid = @NodeId
AND published = 0
ORDER BY updatedate DESC
IF @published IS NOT NULL AND @newest IS NOT NULL
BEGIN
DECLARE @difference INT
SET @difference = Datediff(second, @published, @newest)
IF @difference > 2
PRINT @NodeId
END
FETCH next FROM my_cursor INTO @NodeId
END
CLOSE my_cursor
DEALLOCATE my_cursor
SELECT umbracoNode.id,
published.versionId AS publishedVersion,
latest.versionId AS newestVersion
FROM (umbracoNode
LEFT JOIN umbracoNode parent ON parent.parentID = umbracoNode.id
INNER JOIN cmsContent content ON content.nodeId = umbracoNode.id
LEFT JOIN cmsContentType contenttype ON contenttype.nodeId = content.contentType
LEFT JOIN
(SELECT nodeId,
versionId
FROM cmsDocument
WHERE published = 1
GROUP BY nodeId,
versionId) AS published ON umbracoNode.id = published.nodeId
LEFT JOIN
(SELECT nodeId,
versionId
FROM cmsDocument
WHERE newest = 1
GROUP BY nodeId,
versionId) AS latest ON umbracoNode.id = latest.nodeId)
WHERE published.versionId != latest.versionId
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment