Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Rob Shaw 37 posts 170 karma points c-trib
    Nov 07, 2019 @ 15:11
    Rob Shaw
    0

    Relate umbracoPropertyData to individual members

    Hi all,

    I've added some custom properties to a custom member type in Umbraco 8.

    The data for these fields is stored in the umbracoPropertyData table.

    I am having an issue relating the rows to the members the values belong to.

    Does anyone know what table is used to store the relation between umbracoPropertyData rows and the nodes/members they belong to?

    Thanks in advance!

  • Rob Shaw 37 posts 170 karma points c-trib
    Nov 11, 2019 @ 13:39
    Rob Shaw
    101

    Hey all,

    So I just came back to this and have come up with a solution, I'm no SQL wizard so I'm not sure how efficient the query actually is but it works!

    The following script does require you to know the propertyTypeId of your custom properties (you could change this for the alias of course) and what column the value is stored in.

    SELECT
    MAX(CASE WHEN pd.propertyTypeId = 50 THEN pd.varcharValue else '' END) AS FirstName,
    MAX(CASE WHEN pd.propertyTypeId = 51 THEN pd.varcharValue else '' END) AS LastName,
    MAX(CASE WHEN pd.propertyTypeId = 52 THEN pd.varcharValue else '' END) AS Avatar
    FROM
    dbo.cmsMemberType AS mt 
    INNER JOIN dbo.cmsPropertyType AS pt ON mt.propertytypeId = pt.id
    INNER JOIN dbo.umbracoPropertyData AS pd ON pt.id = pd.propertytypeid
    INNER JOIN dbo.umbracoContentVersion AS ucv ON ucv.id = pd.versionId
    GROUP BY ucv.id
    

    With the above script I was able to return the custom member property values for registered members (FirstName, LastName, Avatar)

    If you think that there could be any improvements or have any questions please let me know and I hope this helps someone in the same situation as me!

Please Sign in or register to post replies

Write your reply to:

Draft