I have an Umbraco 6 installation with 10 multisites (and this number is growing), I'm writing an SQL query for a report to show the Member Details assocaited with each Site. I can get the Member Details but I can't find a way to identify each Site level node (and thereby get the name). I can see the name in the Text field in the [umbracoNode] table and I can get the node id from that but I don't know how to identify it as a root level node.
Does anyone know how to do this? I need to identify root levele nodes and get the correspndning name - as shown in the Content tree like this
Typical, shortly after posting I found a solution - might not be the best one but works for me and might be of help to someone else. Here it is. I noticed the field 'nodeObjectType' in the [umbracoNode] table had a value that was the same for all of my SubSites. This value is also present in other data rows but when I added (parentID = '-1') to the WHERE claue it only returned 10 rows, all matching my 10 subsites (hopefully this isn't a coincedence - maybe someone else can clarify?).
SELECT TOP 1000 [id] ,[trashed] ,[parentID] ,[nodeUser] ,[level] ,[path] ,[sortOrder] ,[uniqueID] ,[text] ,[nodeObjectType] ,[createDate] FROM [dbo].[umbracoNode] WHERE 1=1 AND nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' AND parentID = '-1'
SQL RootNode Name Multi Site
Hi
I have an Umbraco 6 installation with 10 multisites (and this number is growing),
I'm writing an SQL query for a report to show the Member Details assocaited with each Site.
I can get the Member Details but I can't find a way to identify each Site level node (and thereby get the name).
I can see the name in the Text field in the [umbracoNode] table and I can get the node id from that but I don't know how to identify it as a root level node.
Does anyone know how to do this? I need to identify root levele nodes and get the correspndning name - as shown in the Content tree like this
Content
----------->SubSiteName1
----------->SubSiteName2
----------->SubSiteName3
etc.
Thanks in advance for any input you give.
John
Typical, shortly after posting I found a solution - might not be the best one but works for me and might be of help to someone else.
Here it is. I noticed the field 'nodeObjectType' in the [umbracoNode] table had a value that was the same for all of my SubSites. This value is also present in other data rows but when I added (parentID = '-1') to the WHERE claue it only returned 10 rows, all matching my 10 subsites (hopefully this isn't a coincedence - maybe someone else can clarify?).
SELECT TOP 1000 [id]
,[trashed]
,[parentID]
,[nodeUser]
,[level]
,[path]
,[sortOrder]
,[uniqueID]
,[text]
,[nodeObjectType]
,[createDate]
FROM [dbo].[umbracoNode]
WHERE 1=1
AND nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'
AND parentID = '-1'
is working on a reply...