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

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);
        }
    }
}

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);

is working on a reply...