Hah! Welcome to SQL hell :)

There is a way, but it involves some horrible joins. I had to do something similar, here's some example SQL:

SELECT M.nodeId As Id,  N.[text] As Name, M.Email, CPD.dataNtext As MemberField, N.uniqueID as [Key]
FROM dbo.umbracoNode N
INNER JOIN dbo.cmsContent C
ON C.nodeId = N.id
INNER JOIN dbo.cmsContentType CT
ON CT.nodeId = C.contentType
INNER JOIN dbo.cmsContentVersion CV
ON CV.ContentId = N.id
INNER JOIN dbo.cmsMember M
ON M.nodeId = C.nodeId
LEFT JOIN dbo.cmsPropertyType CPT
ON CPT.contentTypeId = C.contentType
LEFT JOIN dbo.cmsPropertyData CPD
ON CPD.propertytypeid = CPT.id AND CPD.versionId = CV.VersionId
INNER JOIN dbo.cmsMember2MemberGroup M2G ON M.nodeId = M2G.Member
INNER JOIN dbo.umbracoNode G ON M2G.MemberGroup = G.id
WHERE G.[text] = 'MemberGroupName' AND CPT.Alias = 'PropertyAlias'

Just change MemberGroupName to the group name you want to limit selects to and PropertyAlias to the name of the member alias you want to bring back.

Hi Dan

The SQL doesn't seem to work in my case.. it just return the data of cmsMember table + MemberField column which NULL values for each row and a Key column.

I don't see why it's needed to join with content tables.. it should be enough with cmsMember, cmsPropertyType and cmdPropertyData tables.

/Bjarne

It definitely works for me.  But you need to ensure that CPD.dataNText is the correct column for how the particular member data is stored. For instance, if your value is stored as NVarchar you'd need to select CPD.dataNvarchar or if it was a date you'd select CPD.dataDate etc.

As for the joins, if you can work out how to get these values without joining to those tables let me know :)  But I got my SQL from hacking the Umbraco source code and tracing the SQL that Umbraco uses for its queries...

Yes, you're right..  I have noticed that when I was working with a SQL solution for this case, where some properties are stored in different fields.

It would be great to use Examine, but the timeout error is driving me mad, so I think SQL would be a way to solve it, with the right/optimal query.

This query below takes about 200 ms to execute.

SELECT COUNT(*) FROM (SELECT M.nodeId As Id,  N.[text] As Name, M.Email, CPD.dataNvarchar As MemberField, N.uniqueID as [Key]
FROM dbo.umbracoNode N
INNER JOIN dbo.cmsContent C
ON C.nodeId = N.id
INNER JOIN dbo.cmsContentType CT
ON CT.nodeId = C.contentType
INNER JOIN dbo.cmsContentVersion CV
ON CV.ContentId = N.id
INNER JOIN dbo.cmsMember M
ON M.nodeId = C.nodeId
LEFT JOIN dbo.cmsPropertyType CPT
ON CPT.contentTypeId = C.contentType
LEFT JOIN dbo.cmsPropertyData CPD
ON CPD.propertytypeid = CPT.id AND CPD.versionId = CV.VersionId
INNER JOIN dbo.cmsMember2MemberGroup M2G ON M.nodeId = M2G.Member
INNER JOIN dbo.umbracoNode G ON M2G.MemberGroup = G.id
WHERE G.[text] = 'SiteMembers' AND CPT.Alias = 'gender'
AND CPD.dataNvarchar = 'Male') t

I found another useful script to get number of members in a specific membergroup, where SiteMembers is the membergroup alias:

SELECT Count FROM (
SELECT
        n.text AS MemberGroup,
        COUNT(g.MemberGroup) AS Count
FROM
        cmsMember2MemberGroup AS g
        INNER JOIN umbracoNode AS n ON g.MemberGroup = n.id
GROUP BY
        g.MemberGroup, n.text
) t 
WHERE t.MemberGroup = 'SiteMembers'

/Bjarne

So I have now created two method to get the values via SQL instead of via Examine Member Index:

public static int MembersWithPropertyValue(string propertyAlias, string proertyValue, string propertyType)
{
            string connString = WebConfigurationManager.ConnectionStrings["umbracoDbDSN"].ConnectionString;
            string sql = "SELECT COUNT(*) FROM (SELECT M.nodeId As Id,  N.[text] As Name, M.Email, CPD.[" + @propertyType + "] As MemberField, N.uniqueID as [Key] FROM dbo.umbracoNode N";
            sql += " INNER JOIN dbo.cmsContent C ON C.nodeId = N.id INNER JOIN dbo.cmsContentType CT ON CT.nodeId = C.contentType";
            sql += " INNER JOIN dbo.cmsContentVersion CV ON CV.ContentId = N.id INNER JOIN dbo.cmsMember M ON M.nodeId = C.nodeId";
            sql += " LEFT JOIN dbo.cmsPropertyType CPT ON CPT.contentTypeId = C.contentType LEFT JOIN dbo.cmsPropertyData CPD ON CPD.propertytypeid = CPT.id AND CPD.versionId = CV.VersionId";
            sql += " INNER JOIN dbo.cmsMember2MemberGroup M2G ON M.nodeId = M2G.Member INNER JOIN dbo.umbracoNode G ON M2G.MemberGroup = G.id";
            sql += " WHERE G.[text] = 'SiteMembers' AND CPT.Alias = @propertyAlias AND CPD.[" + @propertyType + "] = @propertyValue) t";

            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@propertyAlias", propertyAlias);
            cmd.Parameters.AddWithValue("@propertyValue", proertyValue);
            cmd.Parameters.AddWithValue("@propertyType", propertyType);

        int numberOfMembers = 0;
        using (conn)
        {
                conn.Open();
                numberOfMembers = (int)cmd.ExecuteScalar();
                conn.Close();
        }

        return numberOfMembers;
}

public static int MembersInGroup(string memberGroup)
{
            string connString = WebConfigurationManager.ConnectionStrings["umbracoDbDSN"].ConnectionString;
            string sql = "SELECT Count FROM (SELECT n.text AS MemberGroup, COUNT(g.MemberGroup) AS Count FROM cmsMember2MemberGroup AS g";
            sql += " INNER JOIN umbracoNode AS n ON g.MemberGroup = n.id GROUP BY g.MemberGroup, n.text) t WHERE t.MemberGroup = @memberGroup";

            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@memberGroup", memberGroup);

        int numberOfMembers = 0;
        using (conn)
        {
                conn.Open();
                numberOfMembers = (int)cmd.ExecuteScalar();
                conn.Close();
        }

        return numberOfMembers;
}

Cool! But you do know you can use PetaPoco to create queries in Umbraco without the need for going via SQL Client?   

The idea is you create a POCO class to hold the values of all your columns (such as MyMember) then you can query it like:

var db = ApplicationContext.Current.DatabaseContext.Database;
Sql query = new Sql(sql, sqlParams); // sql is your SQL query and sqlParams are any parameters
var members = db.Fetch(sql);

Or if you just want to get a count then you can use the ExecuteScalar method:

var count = db.ExecuteScalar<int>(sql);

Warren Buckley has a good article on this -> http://creativewebspecialist.co.uk/2013/07/16/umbraco-petapoco-to-store-blog-comments/

Makes for much nicer code.

Yes, I know.. but I haven't used it much .. 

but it shouldn't be that hard the rewrite the code with PetaPoco.

So in my example above I would still have the same sql query string, but remove SqlConnection, SqlCommand, cmd.Parameters.AddWithValue, open and close the connection...?

Yep, pretty much. ApplicationContext.Current.DatabaseContext.Database exposes a connection to the current Umbraco string, so you don't need to manually connect. You can then just execute plain SQL commands and return the results as objects that are mapped to the columns in your query.

Hi Dan

I have tried to change my code to use PetaPoco and this seems to work. Is this the way to pass parameters using the new keyword and the comma separated list for the parameters: http://www.toptensoftware.com/petapoco/

public static int MembersInGroup(string memberGroup)
{
    string sql = "SELECT Count FROM (SELECT n.text AS MemberGroup, COUNT(g.MemberGroup) AS Count FROM cmsMember2MemberGroup AS g";
    sql += " INNER JOIN umbracoNode AS n ON g.MemberGroup = n.id GROUP BY g.MemberGroup, n.text) t WHERE t.MemberGroup = @memberGroup";

    var db = ApplicationContext.Current.DatabaseContext.Database;
    var numberOfMembers = db.ExecuteScalar<int>(sql, new { memberGroup });

    return numberOfMembers;
}

Pretty short :) and could actually be shorted a few lines, but with the variable numberOfMembers it might be more clear what is returned.

/Bjarne

Nice! Always good to reduce code :)

There are many different ways of doing things with PetaPoco, it can be a bit confusing. But I *think* you can just do:

var numberOfMembers = db.ExecuteScalar<int>(sql,memberGroup );

No need for the new {} syntax. If you had multiple parameters, then you can just pass them like this:

var numberOfMembers = db.ExecuteScalar<int>(sql,param1, param2, param3, etc. );

It does need the new keyword as the method expect an object and not different parameters in the method. Otherwise you will get an exception :)

Here you can see the different methods, some without paramters, others with 1 or more parameters:

public static int GetNumberOfOrdersFromMember(int memberId)
{
    var db = ApplicationContext.Current.DatabaseContext.Database;
    string sql = "SELECT COUNT(*) from uCommerce_PurchaseOrder AS o INNER JOIN uCommerce_Customer c ON o.CustomerId = c.CustomerId WHERE o.OrderStatusId = 3 AND c.MemberId = @memberId";

    var numberOfOrders = db.ExecuteScalar<int>(sql, new { memberId });
    return numberOfOrders;
}

public static int MembersWithPurchases()
{
    var db = ApplicationContext.Current.DatabaseContext.Database;
    string sql = "SELECT COUNT(*) FROM (SELECT DISTINCT c.MemberId, o.OrderId, ROW_NUMBER() OVER (PARTITION BY c.MemberId ORDER BY c.MemberId) AS RowNumber";
    sql += " FROM uCommerce_PurchaseOrder AS o JOIN uCommerce_Customer c ON o.CustomerId = c.CustomerId WHERE o.OrderStatusId = 3)  AS mo WHERE  mo.RowNumber = 1";

    var numberOfOrders = db.ExecuteScalar<int>(sql);
    return numberOfOrders;
}

public static int MembersWithPropertyValue(string propertyAlias, string propertyValue, string propertyType)
{
    var db = ApplicationContext.Current.DatabaseContext.Database;
    string sql = "SELECT COUNT(*) FROM (SELECT M.nodeId As Id,  N.[text] As Name, M.Email, CPD.[" + @propertyType + "] As MemberField, N.uniqueID as [Key] FROM dbo.umbracoNode N";
    sql += " INNER JOIN dbo.cmsContent C ON C.nodeId = N.id INNER JOIN dbo.cmsContentType CT ON CT.nodeId = C.contentType";
    sql += " INNER JOIN dbo.cmsContentVersion CV ON CV.ContentId = N.id INNER JOIN dbo.cmsMember M ON M.nodeId = C.nodeId";
    sql += " LEFT JOIN dbo.cmsPropertyType CPT ON CPT.contentTypeId = C.contentType LEFT JOIN dbo.cmsPropertyData CPD ON CPD.propertytypeid = CPT.id AND CPD.versionId = CV.VersionId";
    sql += " INNER JOIN dbo.cmsMember2MemberGroup M2G ON M.nodeId = M2G.Member INNER JOIN dbo.umbracoNode G ON M2G.MemberGroup = G.id";
    sql += " WHERE G.[text] = 'SiteMembers' AND CPT.Alias = @propertyAlias AND CPD.[" + @propertyType + "] = @propertyValue) t";

    var numberOfMembers = db.ExecuteScalar<int>(sql, new { propertyAlias, propertyValue, propertyType });
    return numberOfMembers;
}

public static int MembersInGroup(string memberGroup)
{
    var db = ApplicationContext.Current.DatabaseContext.Database;
    string sql = "SELECT Count FROM (SELECT n.text AS MemberGroup, COUNT(g.MemberGroup) AS Count FROM cmsMember2MemberGroup AS g";
    sql += " INNER JOIN umbracoNode AS n ON g.MemberGroup = n.id GROUP BY g.MemberGroup, n.text) t WHERE t.MemberGroup = @memberGroup";

    var numberOfMembers = db.ExecuteScalar<int>(sql, new { memberGroup });
    return numberOfMembers;
}

/Bjarne

"It does need the new keyword as the method expect an object and not different parameters in the method. Otherwise you will get an exception :)"

Are you sure? The method excepts a params array of objects, and the params parameter in C# is a bit magic in that it allows you to pass an array in as single values.  So this compiles and executes fine:

string sql = "SELECT COUNT(*) from cmsMember WHERE NodeId > @0 AND NodeId < @1";
int id1 = 12;
int id2 = 23;

int count = DatabaseContext.Database.ExecuteScalar<int>(sql, id1, id2);

You can just reference parameters in your SQL using @0, @1 etc.

ahh, I think I understand what you mean :)

the reason for using the new keyword is when you are using named parameters, like I have done in this case :)

So I can write the method with named paramters like this:

public static int MembersWithPropertyValue(string propertyAlias, string propertyValue, string propertyType)
{
    var db = ApplicationContext.Current.DatabaseContext.Database;
    string sql = "SELECT COUNT(*) FROM (SELECT M.nodeId As Id,  N.[text] As Name, M.Email, CPD.[" + @propertyType + "] As MemberField, N.uniqueID as [Key] FROM dbo.umbracoNode N";
    sql += " INNER JOIN dbo.cmsContent C ON C.nodeId = N.id INNER JOIN dbo.cmsContentType CT ON CT.nodeId = C.contentType";
    sql += " INNER JOIN dbo.cmsContentVersion CV ON CV.ContentId = N.id INNER JOIN dbo.cmsMember M ON M.nodeId = C.nodeId";
    sql += " LEFT JOIN dbo.cmsPropertyType CPT ON CPT.contentTypeId = C.contentType LEFT JOIN dbo.cmsPropertyData CPD ON CPD.propertytypeid = CPT.id AND CPD.versionId = CV.VersionId";
    sql += " INNER JOIN dbo.cmsMember2MemberGroup M2G ON M.nodeId = M2G.Member INNER JOIN dbo.umbracoNode G ON M2G.MemberGroup = G.id";
    sql += " WHERE G.[text] = 'SiteMembers' AND CPT.Alias = @propertyAlias AND CPD.[" + @propertyType + "] = @propertyValue) t";

    var numberOfMembers = db.ExecuteScalar<int>(sql, new { propertyAlias, propertyValue, propertyType });
    return numberOfMembers;
}

or like this as your suggestion:

public static int MembersWithPropertyValue(string propertyAlias, string propertyValue, string propertyType)
{
      var db = ApplicationContext.Current.DatabaseContext.Database;
      string sql = "SELECT COUNT(*) FROM (SELECT M.nodeId As Id,  N.[text] As Name, M.Email, CPD.[" + propertyType + "] As MemberField, N.uniqueID as [Key] FROM dbo.umbracoNode N";
      sql += " INNER JOIN dbo.cmsContent C ON C.nodeId = N.id INNER JOIN dbo.cmsContentType CT ON CT.nodeId = C.contentType";
      sql += " INNER JOIN dbo.cmsContentVersion CV ON CV.ContentId = N.id INNER JOIN dbo.cmsMember M ON M.nodeId = C.nodeId";
      sql += " LEFT JOIN dbo.cmsPropertyType CPT ON CPT.contentTypeId = C.contentType LEFT JOIN dbo.cmsPropertyData CPD ON CPD.propertytypeid = CPT.id AND CPD.versionId = CV.VersionId";
      sql += " INNER JOIN dbo.cmsMember2MemberGroup M2G ON M.nodeId = M2G.Member INNER JOIN dbo.umbracoNode G ON M2G.MemberGroup = G.id";
      sql += " WHERE G.[text] = 'SiteMembers' AND CPT.Alias = @0 AND CPD.[" + propertyType + "] = @1) t";

      var numberOfMembers = db.ExecuteScalar<int>(sql, propertyAlias, propertyValue, propertyType); //new { propertyAlias, propertyValue, propertyType });

      return numberOfMembers;
}

in second method I'm not sure to use the parameter propertyValue in the sql as it's the column name e.g. dataInt. if I write it like this [" + @2 + "] the variable is not defined and if I include it inside the string like this "[@2]" I get an exception unknown column name @2
 

Hi Dan

I think this might be an another approach to your SQL query, where you don't have to specify the column type:

SELECT * FROM (SELECT M.nodeId As Id,  N.[text] As Name, M.Email,
ISNULL(CASE 
WHEN CPT.dataTypeId IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Nvarchar') THEN CPD.[dataNvarchar] 
WHEN CPT.dataTypeId IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Ntext') THEN CPD.[dataNtext] 
WHEN CPT.dataTypeId IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Date') THEN CONVERT(NVARCHAR, CPD.[dataDate]) 
WHEN CPT.dataTypeId IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Integer') THEN  CONVERT(NVARCHAR, CPD.[dataInt])
ELSE NULL END, NULL) AS MemberField
, N.uniqueID as [Key] FROM dbo.umbracoNode N
INNER JOIN dbo.cmsContent C ON C.nodeId = N.id INNER JOIN dbo.cmsContentType CT ON CT.nodeId = C.contentType
INNER JOIN dbo.cmsContentVersion CV ON CV.ContentId = N.id INNER JOIN dbo.cmsMember M ON M.nodeId = C.nodeId
LEFT JOIN dbo.cmsPropertyType CPT ON CPT.contentTypeId = C.contentType LEFT JOIN dbo.cmsPropertyData CPD ON CPD.propertytypeid = CPT.id AND CPD.versionId = CV.VersionId
INNER JOIN dbo.cmsMember2MemberGroup M2G ON M.nodeId = M2G.Member INNER JOIN dbo.umbracoNode G ON M2G.MemberGroup = G.id
WHERE G.[text] = 'SiteMembers' AND CPT.Alias = 'gender') t
WHERE CAST(t.MemberField AS NVARCHAR(MAX)) = 'Male'

or if I just want the count of members with the property value:

SELECT COUNT(*) FROM (SELECT M.nodeId As Id,  N.[text] As Name, M.Email,
ISNULL(CASE 
WHEN CPT.dataTypeId IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Nvarchar') THEN CPD.[dataNvarchar] 
WHEN CPT.dataTypeId IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Ntext') THEN CPD.[dataNtext] 
WHEN CPT.dataTypeId IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Date') THEN CONVERT(NVARCHAR, CPD.[dataDate]) 
WHEN CPT.dataTypeId IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Integer') THEN  CONVERT(NVARCHAR, CPD.[dataInt])
ELSE NULL END, NULL) AS MemberField
, N.uniqueID as [Key] FROM dbo.umbracoNode N
INNER JOIN dbo.cmsContent C ON C.nodeId = N.id INNER JOIN dbo.cmsContentType CT ON CT.nodeId = C.contentType
INNER JOIN dbo.cmsContentVersion CV ON CV.ContentId = N.id INNER JOIN dbo.cmsMember M ON M.nodeId = C.nodeId
LEFT JOIN dbo.cmsPropertyType CPT ON CPT.contentTypeId = C.contentType LEFT JOIN dbo.cmsPropertyData CPD ON CPD.propertytypeid = CPT.id AND CPD.versionId = CV.VersionId
INNER JOIN dbo.cmsMember2MemberGroup M2G ON M.nodeId = M2G.Member INNER JOIN dbo.umbracoNode G ON M2G.MemberGroup = G.id
WHERE G.[text] = 'SiteMembers' AND CPT.Alias = 'gender') t
WHERE CAST(t.MemberField AS NVARCHAR(MAX)) = 'Male'

/Bjarne

Ahh, that's a good idea using the CASE statement. Nice! Just wish the Umbraco DB wasn't so complicated :)

Okay, the query didn't performed that well anyway, so I specify the datatype column..

When using the query with CASE to lookup the datatype it took about 4000 ms even I only checked for one property.

Instead I specify the datatype column on my own via a method, where it only takes 200-300 ms.

when I check for two properties, e.g. gender=male and emailSubscription=true, it takes about 2000 ms.. and there are 95K members. So it's something that will be noticed :)

SELECT COUNT(*) FROM (
SELECT t.Id, t.Name, t.Email, t.[Key], t.[dataNvarchar], t.Alias, ROW_NUMBER() OVER (PARTITION BY t.Id ORDER BY t.Id) AS RowNumber
FROM (SELECT M.nodeId As Id,  N.[text] As Name, M.Email, CPD.[dataNvarchar], CPD.[dataNtext], CPD.[dataDate], CPD.[dataInt], N.uniqueID as [Key], CPT.Alias 
FROM dbo.umbracoNode N
INNER JOIN dbo.cmsContent C
ON C.nodeId = N.id
INNER JOIN dbo.cmsContentType CT
ON CT.nodeId = C.contentType
INNER JOIN dbo.cmsContentVersion CV
ON CV.ContentId = N.id
INNER JOIN dbo.cmsMember M
ON M.nodeId = C.nodeId
LEFT JOIN dbo.cmsPropertyType CPT
ON CPT.contentTypeId = C.contentType
LEFT JOIN dbo.cmsPropertyData CPD
ON CPD.propertytypeid = CPT.id AND CPD.versionId = CV.VersionId
INNER JOIN dbo.cmsMember2MemberGroup M2G ON M.nodeId = M2G.Member
INNER JOIN dbo.umbracoNode G ON M2G.MemberGroup = G.id
WHERE G.[text] = 'SiteMembers'
AND CPT.Alias = 'gender'
OR CPT.Alias = 'emailSubscription'
) t
WHERE (CAST(t.dataNvarchar AS NVARCHAR(MAX)) = 'Male'
OR CAST(t.dataInt AS NVARCHAR(MAX)) = '1'
)
) mi
WHERE mi.RowNumber = 2

I just select all four columns dataNvarchar, dataNtext, dataInt and dataDate ... and here the RowNumber tells that a member is both "Male" and is subscribed the newsletter.

/Bjarne

is working on a reply...