+ Start a Discussion
Michael MMichael M 

SOQL to query files based on owner

Hello, I need to delete all files (content document links?) created by a specific user. How would I write that SOQL query? 
Best Answer chosen by Michael M
SarvaniSarvani
Hi Michael,

That's wierd. In that case your query should result in records when you used below query:
select id, title,filetype,parentid,ownerid from contentdocument where ownerId='0051N000006BSeyQAG'
I am not sure what's going on.. I did test in my org replicating your scenario it works as expected and pulls the record where the owner is specified user.
Do you have this permission enabled as in the link to query on all content document records. Give it a try  https://salesforce.stackexchange.com/questions/2206/obtaining-all-contentdocument-records-using-soql

For the question,  if I query ContentDocumentLink and then Delete that list, does that completely delete the files from Salesforce? 

You cannot delete records fetched from the ContentDocumentLink if the share type is "I" it throws excpetion error as it's default content document link record created in the backend when user uploads files for that you need to delete records from content document which deletes both instances.
SELECT id,ContentDocumentId, ContentDocument.CreatedDate,ContentDocument.Title, sharetype, LinkedEntityId  FROM ContentDocumentLink where LinkedEntityId in ( SELECT Id FROM User)
You can only delete content document link record files if share type is other than "V" which means related to any record like Account, Opportunity but not the user object.
SELECT id,ContentDocumentId, ContentDocument.CreatedDate,ContentDocument.Title, sharetype, LinkedEntityId  FROM ContentDocumentLink where LinkedEntityId in ( SELECT Id FROM Account)
https://salesforce.stackexchange.com/questions/274499/unable-to-delete-contentdocumentlink

Thanks

All Answers

AbhishekAbhishek (Salesforce Developers) 
Hi Michael,

Your query is answered in the below blogs,

https://developer.salesforce.com/forums/?id=906F00000008vuaIAA

https://success.salesforce.com/answers?id=90630000000heONAAY

I hope you find the above solution helpful. If it does, please mark as Best Answer to help others too.

Thanks.
Michael MMichael M
I understand the concept that you need to query the results in a list and then delete the list. My question is about contentdocumentlink specifically. The WHERE clause of the query for contentdocumentlink cannot be based on the owner, I think it needs to be based on LinkedEntityId (see below for an example). However I need to query the files based on the owner of the FILES, NOT the owner of the Lead records that the files are attached to. How can that be done?  

[SELECT Id, Linkedentity.name, Linkedentity.id,ContentDocument.CreatedDate  
                FROM contentDocumentLink 
                WHERE Linkedentityid 
                IN (SELECT Id FROM lead where createddate < Last_N_Days:7 and isconverted = false) ];  
AbhishekAbhishek (Salesforce Developers) 
I haven't face this similar requirement. I suspect that this can't be achieved if any got more inputs i will let you know.
SarvaniSarvani
Hi Michael,

Give a try with below SOQL:
select id, title,filetype,parentid,ownerid from contentdocument where ownerId='005f40000020kWABCZ'
Hope this helps!

Thanks
Michael MMichael M
Hi Sarvani,
I tried using that query, but it returned 0 results for the userid. This was the query I used:
select id, title, filetype, parentid, ownerid from contentdocument where ownerId='0051N000006BSeyQAG'

However, when I used this query, I got many results:
SELECT ContentDocumentId, ContentDocument.CreatedDate, LinkedEntityId FROM ContentDocumentLink where LinkedEntityId in ( SELECT Id FROM user where id = '0051N000006BSeyQAG')

Do you know why there would be a difference in results? Also, do you think the results of the last query would be accurate? I just want to make sure I'm getting all the correct files. 
SarvaniSarvani
Hi Michael,

When you run the below query if you do not get any results it means that there are no files that the user owns.
select id, title,filetype,parentid,ownerid from contentdocument where ownerId='0051N000006BSeyQAG'
However, if the below query results in any records there are chances that the file has been shared with the user '0051N000006BSeyQAG' by some other user. Since LinkedEnity can be refernce to any object that supports chatter.( https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_contentdocumentlink.htm )
SELECT ContentDocumentId, ContentDocument.CreatedDate,ContentDocument.Title, LinkedEntityId FROM ContentDocumentLink where LinkedEntityId in ( SELECT Id FROM user where id = '0051N000006BSeyQAG')
Anyways to confirm who the owner of those records is take any record from above results and query like below using ContentDocumentId.
select id, title,filetype,parentid,ownerid from contentdocument where id='ContentDocumentId'
Look for the ownerid field of the results which shouldn't be 0051N000006BSeyQAG

Hope this helps!

Thanks
Michael MMichael M
Hi Sarvani,

I ran the last query (select id, title,filetype,parentid,ownerid from contentdocument where id='ContentDocumentId') with the ContentDocumentId from a couple examples from  the other query, and it says that the ownerid for those ContentDocumentId's are in fact 0051N000006BSeyQAG. If that's the case, why would this query (select id, title,filetype,parentid,ownerid from contentdocument where ownerId='0051N000006BSeyQAG') return 0 results? And does that mean that this query (SELECT ContentDocumentId, ContentDocument.CreatedDate,ContentDocument.Title, LinkedEntityId FROM ContentDocumentLink where LinkedEntityId in ( SELECT Id FROM user where id = '0051N000006BSeyQAG') is accurate? 
Michael MMichael M
Hi Sarvani,

On a related note, if I query ContentDocumentLink and then Delete that list, does that completely delete the files from Salesforce? 
SarvaniSarvani
Hi Michael,

That's wierd. In that case your query should result in records when you used below query:
select id, title,filetype,parentid,ownerid from contentdocument where ownerId='0051N000006BSeyQAG'
I am not sure what's going on.. I did test in my org replicating your scenario it works as expected and pulls the record where the owner is specified user.
Do you have this permission enabled as in the link to query on all content document records. Give it a try  https://salesforce.stackexchange.com/questions/2206/obtaining-all-contentdocument-records-using-soql

For the question,  if I query ContentDocumentLink and then Delete that list, does that completely delete the files from Salesforce? 

You cannot delete records fetched from the ContentDocumentLink if the share type is "I" it throws excpetion error as it's default content document link record created in the backend when user uploads files for that you need to delete records from content document which deletes both instances.
SELECT id,ContentDocumentId, ContentDocument.CreatedDate,ContentDocument.Title, sharetype, LinkedEntityId  FROM ContentDocumentLink where LinkedEntityId in ( SELECT Id FROM User)
You can only delete content document link record files if share type is other than "V" which means related to any record like Account, Opportunity but not the user object.
SELECT id,ContentDocumentId, ContentDocument.CreatedDate,ContentDocument.Title, sharetype, LinkedEntityId  FROM ContentDocumentLink where LinkedEntityId in ( SELECT Id FROM Account)
https://salesforce.stackexchange.com/questions/274499/unable-to-delete-contentdocumentlink

Thanks
This was selected as the best answer
Michael MMichael M
Thank you, Sarvani