Umbraco SQL to find documents using specific dataType


DECLARE @dataTypeSearch NVARCHAR(50)
SET @dataTypeSearch = '%Media Picker Adv%'

-- 1. Find thge data type you're after
SELECT * FROM cmsDataType
INNER JOIN umbracoNode N on N.id = cmsDataType.nodeId
WHERE N.[text] LIKE @dataTypeSearch

--1.b Pick your nodeId from above query
DECLARE @dataTypeId INT
SET @dataTypeId = 6877

-- 2. Find the properties using the dataType:
SELECT * FROM cmsPropertyType WHERE dataTypeId = @dataTypeId -- NodeId from above

--3. Find nodes with a proerty of that type(s):
SELECT DISTINCT N.* FROM UmbracoNode N INNER JOIN
cmsPropertyData PD ON PD.contentNodeId = N.id
WHERE
propertytypeid IN (SELECT DISTINCT id FROM cmsPropertyType WHERE dataTypeId = @dataTypeId) --Id's returned from second query above
AND dataInt IS NOT NULL --NULL CHECK Corresponding dbType from initial query above

--3. To locate in Umbraco select all nodes in the path:
--SELECT * FROm umbracoNode WHERE id in (-1,5205,6454,1086,4573,4454,4455,28104) ORDER BY LEN(path) -- To find a node paste in path

--4. Find Document Types using the propertyType (note the subquery in where clause is copied from item 3 above):
SELECT DISTINCT T.alias FROM cmsContent C
INNER JOIN cmsContentType T ON C.contentType = T.nodeId
WHERE C.nodeId IN (
SELECT DISTINCT N.Id FROM UmbracoNode N INNER JOIN
cmsPropertyData PD ON PD.contentNodeId = N.id
WHERE
propertytypeid IN (SELECT DISTINCT id FROM cmsPropertyType WHERE dataTypeId = @dataTypeId) --Id's returned from second query above
AND dataInt IS NOT NULL --NULL CHECK Corresponding dbType from initial query above
)

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: