Try our conversational search powered by Generative AI!

Database query for Media items



How can I find list of media items (say PDF's) that are not referenced anywhere (on any pages or blocks) through Database query?


Jul 14, 2021 19:11

Hello Dileep

Does this have to be a database query? Querying the DB isn't recommended as the schema can change without notice. 

You can however find references to content using the IContentRepository. So something like this will allow you to do this work using the API:

IContentRepository repo;
repo.GetReferencesToContent(yourPdf.ContentLink, false).Any();
Edited, Jul 15, 2021 7:50
Dileep D - Jul 21, 2021 18:56
Since it is just one time activity which they need data for (related to some migration), I was trying to avoid coding and deployment for it.

I managed to get this data using below queries.

DECLARE @PDFTable TABLE(ContentId int, Name varchar(1000), pageguid varchar(50), ExpiryDate Datetime)

    T.pkID 'Content Id',
    TPL.Name 'Content Name',
    from tblPage T
    inner join tblPageLanguage TPL ON T.pkID = TPL.fkPageID
    inner join tblPageType TP on T.fkPageTypeID = TP.pkID
    where TP.Name = 'PDFTypeFile' -- This should be the type name used for the PDF or whatever media items you need

    -- Get all articles where the pdf/media items is referenced with the field data where the media items are referenced. In my case it was in Related resources which can accept more than 1 media item
    Declare @CustomTable TABLE (ContentId int, WebPageUrl varchar(500), WebpageName varchar(500), PropertyName varchar(50), PropertyValue varchar(max), PageType varchar(50))

    Insert into @CustomTable

    select tblPage.pkID, tblPageLanguage.LinkURL 'Web Page URL', tblPageLanguage.Name 'Web Page Name',tblPageDefinition.Name
    'Property Name', tblProperty.LongString 'Property Value',
    from tblProperty
    inner join tblPage on tblProperty.fkPageID = tblPage.pkID
    inner join tblPageDefinition on tblProperty.fkPageDefinitionID = tblPageDefinition.pkID
    inner join tblPageLanguage on tblpage.pkID = tblPageLanguage.fkPageID
    inner join tblPageType on tblPageType.pkID = tblPage.fkPageTypeID
    where tblPageType.Name in ('NewsPage', 'ArticlePage')
    and tblPageDefinition.Name = 'RelatedResources'
    and tblPageLanguage.StopPublish > GETDATE()
    order by tblPageLanguage.Name

Once I have both these data, I used cursor to loop through all the media items and find their reference from the second table and either print or insert into a third table. Am no SQL expert but managed to get this done. If any one needs I can give the full query.

Again all credits to Khurram Khan from this article

Aug 25, 2021 20:35
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.