I am trying to get a list of members by a property (deliveryRef) set to the same value.
Searching through the forum didn't help much - only old threads that kind of seems like too much work for a simple tast.
My initial though was to query the database directly, but that isn't as simple as it might sound, beacouse members and member properties are not in the same table (where are member properties by the way)
string sqlQuery = "SELECT m.nodeId AS '@id', m.Email AS '@email', CAST(x.xml AS XML) AS '*' FROM cmsMember AS m INNER JOIN cmsContentXml AS x ON m.nodeId = x.nodeId FOR XML PATH('member'), ROOT('members'), TYPE ";
// database sql returns data in following format <members> <member id="1201" email="[email protected]"> <node id="1201" version="d9e77af8-a6bf-4231-8449-bf09ed3132bf" parentID="-1" level="1" writerID="0" nodeType="1162" template="0" sortOrder="2" createDate="2009-08-08T23:54:37" updateDate="2009-08-08T23:54:37" nodeName="auto 3" urlName="auto3" writerName="Administrator" nodeTypeAlias="RegsiteredMembers" path="-1,1201" loginName="auto3" email="[email protected]"> <data alias="title">abc</data> </node> </member> </members>
What you are doing very much exseeds my knowledge of sql. Where do I put the where clause like: WHERE deliveryRef=43? Or do I have to iterate through all the nodes?
SELECT m.nodeId AS '@id', m.Email AS '@email', CAST(x.xml AS XML) AS '*' FROM cmsMember AS m INNER JOIN cmsContentXml AS x ON m.nodeId = x.nodeId WHERE xml LIKE '%Glasgow%' FOR XML PATH('member'), ROOT('members'), TYPE
How to get a list of members by member property
I am trying to get a list of members by a property (deliveryRef) set to the same value.
Searching through the forum didn't help much - only old threads that kind of seems like too much work for a simple tast.
My initial though was to query the database directly, but that isn't as simple as it might sound, beacouse members and member properties are not in the same table (where are member properties by the way)
Does anyone have a great way to achieve this?
string sqlQuery = "SELECT m.nodeId AS '@id', m.Email AS '@email', CAST(x.xml AS XML) AS '*' FROM cmsMember AS m INNER JOIN cmsContentXml AS x ON m.nodeId = x.nodeId FOR XML PATH('member'), ROOT('members'), TYPE ";
// database sql returns data in following format
<members>
<member id="1201" email="[email protected]">
<node id="1201" version="d9e77af8-a6bf-4231-8449-bf09ed3132bf" parentID="-1" level="1" writerID="0" nodeType="1162" template="0" sortOrder="2" createDate="2009-08-08T23:54:37" updateDate="2009-08-08T23:54:37" nodeName="auto 3" urlName="auto3" writerName="Administrator" nodeTypeAlias="RegsiteredMembers" path="-1,1201" loginName="auto3" email="[email protected]">
<data alias="title">abc</data>
</node>
</member>
</members>
Thanks - this seems very usefull.
What you are doing very much exseeds my knowledge of sql. Where do I put the where clause like: WHERE deliveryRef=43? Or do I have to iterate through all the nodes?
Check out this thread - http://forum.umbraco.org/yaf_postst8410_Member-Search.aspx
is working on a reply...