Copied to clipboard

Flag this post as spam?

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


  • Peter Alcock 113 posts 176 karma points
    May 20, 2023 @ 21:38
    Peter Alcock
    0

    Member data - Please help!

    Hi,

    Absolutely love Konstrukt so far and seems almost a life saver as i was quoted over £2k to do what i think i'm not too far off doing!

    I have a custom section called tracking from konstrukt and i can easily display a list of all members which is great based off the documentation sample and altering to cmsMember table.

    What i am desperately trying to do is get it to also display a custom member property value they have, the property alias is 'atest', i know there a fair bit of linking to cmspropertytype etc i need to get it.

    Can anyone please help!?

    Thanks everyone Pete

  • Marc Goodson 2148 posts 14353 karma points MVP 8x c-trib
    May 22, 2023 @ 08:19
    Marc Goodson
    0

    Hi Pete

    How about something like this:

    Select * from 
    cmsMember m
    INNER JOIN umbracoNode un ON m.nodeId = un.id 
    INNER JOIN umbracoContentVersion cv ON un.id = cv.nodeId
    INNER JOIN umbracoPropertyData pd ON cv.Id = pd.versionId
    INNER JOIN cmsPropertyType pt ON pd.propertyTypeId = pt.id
    Where pt.Alias = 'atest' AND cv.[current] = 1
    

    I don't have a V9+ db instance on my machine which has Members to be super sure - but essentially that query shows how all the tables are related and 'should' pull back all the members that have a property with alias 'atest'

    Hope that puts you in the right direction anyhow.

    regards

    Marc

  • Peter Alcock 113 posts 176 karma points
    May 22, 2023 @ 12:02
    Peter Alcock
    0

    Hi David, thank you so much for getting back to me.

    This is my current poco model to use with konstruct which displays the members ok and has the field for the atest value but it just doesnt populate it with the actual value from the db.

    If i run a direct query on the db it works fine as:

    SELECT
        pd.varcharValue AS atestValue
    FROM
        cmsMember AS m
    LEFT JOIN
        umbracoNode AS n ON m.nodeId = n.id
    LEFT JOIN
        umbracoContentVersion AS cv ON n.id = cv.nodeId
    LEFT JOIN
        umbracoPropertyData AS pd ON cv.id = pd.versionId
    LEFT JOIN
        cmsPropertyType AS pt ON pd.propertyTypeId = pt.id
    WHERE
     pt.Alias = 'atest';
    

    But this model code doesnt seem to want to pull the actual atest field value and put it in the public string for konstrukt to render, have you any ideas?

    using NPoco;
    using Umbraco.Cms.Infrastructure.Persistence.DatabaseAnnotations;
    
    namespace UmbracoProject
    {
        internal class ModelPoco
        {
            public class Person
            {
                [Column("nodeId")]
                [PrimaryKeyColumn(AutoIncrement = false)]
                public int MemberId { get; set; }
    
                [Column("loginName")]
                public string LoginName { get; set; }
    
                [Column("email")]
                public string Email { get; set; }
    
                [Ignore]
                public string Atest { get; set; }
            }
    
            public static void UpdateAtestValue(Person person, IDatabase db)
            {
                var sql = new Sql()
                    .Select("pd.varcharValue AS Atest")
                    .From("cmsMember AS m")
                    .LeftJoin("umbracoNode AS n").On("m.nodeId = n.id")
                    .LeftJoin("umbracoContentVersion AS cv").On("n.id = cv.nodeId")
                    .LeftJoin("umbracoPropertyData AS pd").On("cv.id = pd.versionId")
                    .LeftJoin("cmsPropertyType AS pt").On("pd.propertyTypeId = pt.id")
                    .Where("pt.Alias = @0", "atest")
                    .Append("AND m.nodeId = @0", person.MemberId);
    
                person.Atest = db.FirstOrDefault<string>(sql);
            }
        }
    }
    
  • Marc Goodson 2148 posts 14353 karma points MVP 8x c-trib
    May 22, 2023 @ 12:14
    Marc Goodson
    0

    Hi Peter

    Who is this David person :-P

    It's a while since I messed around with npetapoco but

    Is Ignore the right attribute to use here?

    Would [ResultColumn] be more appropriate?

    as that will result in it being populated if the query contains matching data... ... but not get in the way of any inserts or updates.

    regards

    marc

  • Peter Alcock 113 posts 176 karma points
    May 23, 2023 @ 08:27
    Peter Alcock
    0

    Hi Marc!

    So sorry, i was talking to a David while typing out my reply! i gave that a try with no avail just yet, i then thought there was probably nothing actually set to execute the UpdateAtestValue void.

    I currently have this which i feel is close (Might be wrong!) it builds and runs but when opening the section where it should be displayed it crashes with an exemption error on the Atest = db.FirstOrDefault

    As the ModelPoco seems to already have a connection to the cmsMember table i figured the sql query would already be connected to the DB, am i wrong here?

    Any ideas so much apreaciated!

    using NPoco;
    using Umbraco.Cms.Infrastructure.Persistence.DatabaseAnnotations;
    
    namespace UmbracoProject
    {
        internal class ModelPoco
        {
            [TableName("cmsMember")]
            [PrimaryKey("nodeId")]
            public class Person
            {
                [PrimaryKeyColumn]
                public int nodeId { get; set; }
    
                public string LoginName { get; set; }
    
                public string Email { get; set; }
    
                private IDatabase db; // Declare the IDatabase instance
    
                private string sql; // Declare the SQL query
    
                public string Atest
                {
                    get
                    {
                        UpdateAtestValue();
                        return Atest; // Return the updated Atest value
                    }
                    set
                    {
                        Atest = value; // Update the Atest property value
                    }
                }
    
                public void UpdateAtestValue()
                {
                     var sql = new Sql()
                        .Select("pd.varcharValue")
                        .From("cmsMember AS m")
                        .LeftJoin("umbracoNode AS n").On("m.nodeId = n.id")
                        .LeftJoin("umbracoContentVersion AS cv").On("n.id = cv.nodeId")
                        .LeftJoin("umbracoPropertyData AS pd").On("cv.id = pd.versionId")
                        .LeftJoin("cmsPropertyType AS pt").On("pd.propertyTypeId = pt.id")
                        .Where("pt.Alias = @0", "atest")
                        .Where("m.nodeId = @0", 1803);
    
                    Atest = db.FirstOrDefault<string>(sql);
                }
            }
        }
    }
    

    FYI i used this just try try and pull a single value through that i know 100% exists!

    .Where("m.nodeId = @0", 1803);

Please Sign in or register to post replies

Write your reply to:

Draft