Sorting by Published column in a Listview does not order the items correctly

[dhedgepeth]

Which Umbraco version are you using? (Please write the exact version, example: 10.1.0)

10.3.1, 9.5.4, 8.18, 8.12

Bug summary

If you use a Listview and unpublish content, or publish content that was previously unpublished, the Listview will be unable to sort the content by the "Is Published" Column. Instead of ordering the content by published or unpublished, it will sort the content items by their previous publish status.

Specifics

No response

Steps to reproduce

Steps to reproduce:

1. Create a new document type named Blog and enable as a Listview.
2. Create a new document type for a Blog Item.
3. Allow the Blog Item as a child node under the Blog.
4. Add "Is Published" as a column to Listview for the Blog.
   
5. Create content items under the Listview (Publish some and leave others unpublished)

At this point you should be able to sort the content by the Is Published column with no issue:

6. Publish or unpublish a piece of content, you will now be unable to sort correctly by the Is Published column.

Expected result / actual result

When you try to sort the content in the Listview based on the "Is Published" column, you would expect the published content to be grouped together and for unpublished content to be grouped together such as this:

However, when you try to sort by this column after the steps above, it seems that the content is still sorted by its previous publish status which leaves the content sorted randomly:

---

This item has been added to our backlog AB#24049

[patrickdemooij9]

I can take a look at this later today

[patrickdemooij9]

Oke, this is a lot more work than I had thought.
It is going wrong within the database. This query is done:

SELECT [umbracoDocument].[nodeId] AS [NodeId], [umbracoDocument].[published] AS [Published], [umbracoDocument].[edited] AS [Edited]
, [umbracoContent].[nodeId] AS [ContentDto__NodeId], [umbracoContent].[contentTypeId] AS [ContentDto__ContentTypeId]
, [umbracoNode].[id] AS [ContentDto__NodeDto__NodeId], [umbracoNode].[uniqueId] AS [ContentDto__NodeDto__UniqueId], [umbracoNode].[parentId] AS [ContentDto__NodeDto__ParentId], [umbracoNode].[level] AS [ContentDto__NodeDto__Level], [umbracoNode].[path] AS [ContentDto__NodeDto__Path], [umbracoNode].[sortOrder] AS [ContentDto__NodeDto__SortOrder], [umbracoNode].[trashed] AS [ContentDto__NodeDto__Trashed], [umbracoNode].[nodeUser] AS [ContentDto__NodeDto__UserId], [umbracoNode].[text] AS [ContentDto__NodeDto__Text], [umbracoNode].[nodeObjectType] AS [ContentDto__NodeDto__NodeObjectType], [umbracoNode].[createDate] AS [ContentDto__NodeDto__CreateDate]
, [umbracoDocumentVersion].[id] AS [DocumentVersionDto__Id], [umbracoDocumentVersion].[templateId] AS [DocumentVersionDto__TemplateId], [umbracoDocumentVersion].[published] AS [DocumentVersionDto__Published]
, [umbracoContentVersion].[id] AS [DocumentVersionDto__ContentVersionDto__Id], [umbracoContentVersion].[nodeId] AS [DocumentVersionDto__ContentVersionDto__NodeId], [umbracoContentVersion].[versionDate] AS [DocumentVersionDto__ContentVersionDto__VersionDate], [umbracoContentVersion].[userId] AS [DocumentVersionDto__ContentVersionDto__UserId], [umbracoContentVersion].[current] AS [DocumentVersionDto__ContentVersionDto__Current], [umbracoContentVersion].[text] AS [DocumentVersionDto__ContentVersionDto__Text], [umbracoContentVersion].[preventCleanup] AS [DocumentVersionDto__ContentVersionDto__PreventCleanup]
, [pdv].[id] AS [PublishedVersionDto__Id], [pdv].[templateId] AS [PublishedVersionDto__TemplateId], [pdv].[published] AS [PublishedVersionDto__Published]
, [pcv].[id] AS [PublishedVersionDto__ContentVersionDto__Id], [pcv].[nodeId] AS [PublishedVersionDto__ContentVersionDto__NodeId], [pcv].[versionDate] AS [PublishedVersionDto__ContentVersionDto__VersionDate], [pcv].[userId] AS [PublishedVersionDto__ContentVersionDto__UserId], [pcv].[current] AS [PublishedVersionDto__ContentVersionDto__Current], [pcv].[text] AS [PublishedVersionDto__ContentVersionDto__Text], [pcv].[preventCleanup] AS [PublishedVersionDto__ContentVersionDto__PreventCleanup]
, COALESCE([ccv].[name],[umbracoNode].[text]) AS variantName
, (CASE WHEN (ctype.variations & 1) = 0 THEN (CASE WHEN pcv.id IS NULL THEN 0 ELSE 1 END) ELSE (CASE WHEN ccvp.id IS NULL THEN 0 ELSE 1 END) END) AS ordering FROM [umbracoDocument]
INNER JOIN [umbracoContent]
ON [umbracoDocument].[nodeId] = [umbracoContent].[nodeId]
INNER JOIN [umbracoNode]
ON [umbracoContent].[nodeId] = [umbracoNode].[id]
INNER JOIN [umbracoContentVersion]
ON ([umbracoDocument].[nodeId] = [umbracoContentVersion].[nodeId])
INNER JOIN [umbracoDocumentVersion]
ON ([umbracoContentVersion].[id] = [umbracoDocumentVersion].[id])
LEFT JOIN [umbracoContentVersion] [pcv]
INNER JOIN [umbracoDocumentVersion] [pdv]
ON (([pcv].[id] = [pdv].[id]) AND [pcv].[current] = 1 AND [pdv].[published] = 1)
ON ([umbracoDocument].[nodeId] = [pcv].[nodeId])
LEFT JOIN [umbracoContentVersionCultureVariation] [ccv]
INNER JOIN [umbracoLanguage] [lang]
ON (([ccv].[languageId] = [lang].[id]) AND ([lang].[languageISOCode] = 'en-us'))
ON ([umbracoContentVersion].[id] = [ccv].[versionId])
INNER JOIN [cmsContentType] [ctype]
ON ([umbracoContent].[contentTypeId] = [ctype].[nodeId])
LEFT JOIN [umbracoContentVersionCultureVariation] [ccvp]
INNER JOIN [umbracoLanguage] [langp]
ON (([ccvp].[languageId] = [langp].[id]) AND ([langp].[languageISOCode] = 'en-us'))
ON ([pcv].[id] = [ccvp].[versionId])WHERE (([umbracoNode].[nodeObjectType] = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972'))
AND ([umbracoContentVersion].[current] = 1)
AND (([umbracoNode].[parentId] = 1059))

and then returns the ordering in the last column. A 1 if it is published or a 0 if it isn't. However, it currently shows a 1 if it was ever published and a 0 if it has never been published. And since then I've been lost. The results of the query shows version ids that aren't even the current one, but still thinks that it is published. I think the whole query needs to have some rework here.

[nikolajlauridsen]

Fixed in #13474, thanks again for the detailed description 😄