I am currently in the situation where I have to bulk-delete a bunch of nodes. Now since I do not wish to loose database integrity, I'd rather set the trashed flag in the database to then let the CMS delete all of these nodes. Now I need to query for a specific bunch of nodes under a specific parent node with specific content.
Now this would be as easy as: (Values redacted ofc)
SELECT *
FROM [dbo].[umbracoPropertyData]
WHERE varcharValue like 'VALUE' AND id IN (SELECT id FROM [dbo].[umbracoNode] WHERE parentId = 1337);
If the relation was made from umbracoPropertyData to umbracoNode, which is of course impossible.
Does anyone have a fitting query for this sort of ordeal? Or any idea to where I can make the relation?
The Property Data is related to the Content Version, which is in turn related to the Umbraco Node.
Every time you save a content item in Umbraco a new Version is created, and consequently the Content Version and all the related Property Data gets updated. There is a flag in the Content Version for 'Current' which reflects what the Current saved version is.
so something like this
SELECT top 100 *
FROM [umbracoPropertyData] upd
INNER JOIN umbracoContentVersion ucv
ON upd.versionId = ucv.id
INNER JOIN umbracoNode un
ON un.id = ucv.nodeId
WHERE ucv.[current] = 1
AND un.ParentId = 1234
Would give you the kind of join you are looking for I think...
Query for content via SQL to bulk-trash nodes
Hello Umbraco Community!
I am currently in the situation where I have to bulk-delete a bunch of nodes. Now since I do not wish to loose database integrity, I'd rather set the trashed flag in the database to then let the CMS delete all of these nodes. Now I need to query for a specific bunch of nodes under a specific parent node with specific content.
Now this would be as easy as: (Values redacted ofc)
If the relation was made from umbracoPropertyData to umbracoNode, which is of course impossible.
Does anyone have a fitting query for this sort of ordeal? Or any idea to where I can make the relation?
Best regards
Joshua
Hi Joshua
The Property Data is related to the Content Version, which is in turn related to the Umbraco Node.
Every time you save a content item in Umbraco a new Version is created, and consequently the Content Version and all the related Property Data gets updated. There is a flag in the Content Version for 'Current' which reflects what the Current saved version is.
so something like this
Would give you the kind of join you are looking for I think...
regards
Marc
Nevermind my first answer! Yes this shows me the data in relation! I can now set the trashed flag accordingly!
Thank you very much!
is working on a reply...