U4-8779 - Sorting not working in Members List View in 7.5 Beta2

Created by John Churchley 26 Jul 2016, 09:24:35 Updated by Shannon Deminick 04 Aug 2016, 14:47:10

Relates to: U4-8786

Sort of Members in Members List View by custom property and many default properties (Locked out/Approved etc) doesn't work in 7.5 beta2. What did you expect to happen? Order of the list would changed based on selected property chosen What actually happened? OutOfRange Exception thrown.

No error record in logs.

1 Attachments


Claus Jensen 03 Aug 2016, 10:48:39

PR: https://github.com/umbraco/Umbraco-CMS/pull/1409

Customizing the "tailormade" query that only worked for cmsDocument table to also work with other types. Only cmsDocument uses newest and members do not use versioning so these parts of the query had to be optional.


  • Add a custom property to a document type, a media type and a member type
  • Add a column for the custom property in the list view for each type
  • Test this list view and that you can now sort by the custom property
  • Verify that you can also sort by the built-in properties on members such as IsApproved and IsLockedOut (these can just be added as list view columns on the member list view)

Shannon Deminick 04 Aug 2016, 14:10:27

Sorry, this breaks the paging. I'm not sure if it has something specific to do with your changes or if it existed previously.

I have 10000 members, i added a custom property type called 'test', I added this to the list view, when I sort on it, the number of pages are now 1 instead of 1000.

I'll see what i can find out

Shannon Deminick 04 Aug 2016, 14:17:33

Ah, i think i know why. The original query for the paging when sorting on a custom property is something like this:

SELECT cmsMember.nodeId
FROM [cmsMember]
INNER JOIN [cmsContentVersion]
ON [cmsContentVersion].[ContentId] = [cmsMember].[nodeId]
INNER JOIN [cmsContent]
ON [cmsContentVersion].[ContentId] = [cmsContent].[nodeId]
INNER JOIN [cmsContentType]
ON [cmsContentType].[nodeId] = [cmsContent].[contentType]
INNER JOIN [umbracoNode]
ON [cmsContent].[nodeId] = [umbracoNode].[id]
                                SELECT CASE
                                    WHEN dataInt Is Not Null THEN REPLACE(STR(dataInt, 8), SPACE(1), '0')
                                    WHEN dataDecimal Is Not Null THEN REPLACE(STR(dataDecimal, 20, 9), SPACE(1), '0')
                                    WHEN dataDate Is Not Null THEN CONVERT(nvarchar, dataDate, 102)
                                    ELSE COALESCE(dataNvarchar,'')
                                END AS CustomPropVal,
                                cd.nodeId AS CustomPropValContentId
                                FROM cmsMember cd
                                INNER JOIN cmsPropertyData cpd ON cpd.contentNodeId = cd.nodeId
                                INNER JOIN cmsPropertyType cpt ON cpt.Id = cpd.propertytypeId
                                WHERE cpt.Alias = @0) AS CustomPropData
                                ON CustomPropData.CustomPropValContentId = umbracoNode.id
                WHERE (([umbracoNode].[nodeObjectType] = @1))
ORDER BY CustomPropData.CustomPropVal

But if the node doesn't actually have any property data they won't get returned because we're doing an INNER JOIN - this should be an OUTER JOIN. Will see if that works.

Shannon Deminick 04 Aug 2016, 14:47:07

Yup that fixed it ... good catch.

Priority: Task - Pri 1

Type: Bug

State: Fixed


Difficulty: Normal


Backwards Compatible: True

Fix Submitted:

Affected versions: 7.5.0

Due in version: 7.5.0

Sprint: Sprint 39

Story Points: