SOQL ContentDocument: Ids not returning

Question

A handful of our users have created notes on contact and opportunity objects that we are trying to port over. They are currently saved as ContentDocuments - but I also want to pull what objects the notes are related to via contentdocumentlink.

Here's where things get weird.

When I run the following query:

SELECT title, filetype from contentdocument

I get 50 results.

However, when I run a query for contentdocumentlink:

SELECT 
  contentdocument.title, 
  linkedentity.id, 
  contentdocument.id 
FROM ContentDocumentLink 
WHERE linkedentityid in (SELECT id FROM Contact) 
//inner query required, can't SOQL query ContentDocumentLink 
//without specifying a linkedentityid...

and then place all of the contentdocument id's into a set,

set<id> docIds = new set<id>();
for(contentdocumentlink cdl : [*above query*]){
  docIds.add(cdl.documentid);
}

I get hundreds of results.

So a full query of contentdocument gets 50 results, but a full query of contentdocumentlink returns hundreds of contentdocument ids...

Manually checking the contentdocument id's shows that the contentdocuments do exist, they just don't show up in the general query.

A final detail that may be important is the file type of these contentdocuments that are not showing up.

When I query

SELECT title, filetype from contentdocument

The file types are XLS, DOC, PDF, etc.

But when I manually input a doc id I grabbed from contentdocumentlink (that does not show up in the general contendocument query),

SELECT title, filetype from contentdocument where id = :my_file.id

I get a hit with a file type of SNOTE

Is there something funky with SNotes that make them not show up in general queries? Why would there by contentdocument id's that show up from contentdocumentlink, but not from a general query of contentdocument?

Answer 1

I think I found a simpler solution: add the Apex user to the contentdocument..

Here is the code:

//First, get all the CDLs for each object
ContentDocumentLink[] cdls = [SELECT
                                contentdocumentid,
                                linkedentity.name, 
                                linkedentityid,
                                linkedentity.type 
                              FROM ContentDocumentLink 
                              WHERE linkedentityid in (SELECT id FROM User)];
cdls.addAll([SELECT
                contentdocumentid,
                linkedentity.name, 
                linkedentityid,
                linkedentity.type 
             FROM ContentDocumentLink 
             WHERE linkedentityid in (SELECT id FROM Contact)]);

//...// repeat for each object

//Get unique document ids (since you can have multiple CDLs for a single Doc)
set<id> docIds = new set<id>();
for(ContentDocumentLink cdl : cdls){
    docIds.add(cdl.contentdocumentid);
}

//get apex user id
id apexUser = UserInfo.getUserId();

//create new CDLs
ContentDocumentLink[] newCdls = new list<ContentDocumentLink>();
for(id docId : docIds){
  ContentDocumentLink newCdl = new ContentDocumentLink(
        ContentDocumentId=docId, 
        linkedentityid=apexUser, 
        Visibility='SharedUsers',
        ShareType='C');
  newCdls.add(newCdl);
}
insert newCdls;

//check new results
ContentDocument[] Cds = [SELECT id FROM ContentDocument];

//Profit

Answer 2

ContentDocument migration is really tricky. As you note in the comments, the View All Data and Modify All Data permissions do not apply to ContentDocument in the same way they do to other objects.

The best way that I have found to accomplish this, in the context of an org-to-org move, is to extract the document data by means of the Scheduled Data Export feature (making sure to mark the checkbox to include attachments and files). This process did work for both notes and regular files.

You can then utilize a complex process to load those documents into a new org and (via several levels of INDEX/MATCH in Excel) reconstruct their ContentDocumentLink entries. It goes something like this (this might be a little terse; I am happy to expand upon it).

• Throughout your migration, keep a global lookup table of old-org-id to new-org-id (for all objects - trust me, you will need this for anything with a polymorphic lookup relationship).
• Dump your ContentDocuments and ContentVersions with the Scheduled Data Export tool.
• Push your ContentVersions up to new-Salesforce, which automatically creates their ContentDocument "parents". You'll have to synthesize a Body column in the data whose content is the path to the directory in the data export containing the actual ContentVersion data, with the last path component being the Id of the ContentVersion itself.
• Re-query the new ContentVersions in new Salesforce to obtain the Ids of the new ContentDocuments created by Salesforce.
• Extract all of your ContentDocumentLinks from old Salesforce. This is not easy to do if you have any significant data volume due to the tight query restrictions on ContentDocumentLink. I ultimately wrote a Python script to do batched queries and extract the data, based upon the extant ContentDocument Ids in the data export.
• Map the CDL's LinkedEntityId using an INDEX/MATCH into your global lookup table.
• Map the CDL's ContentDocumentId by doing a series of INDEX/MATCH steps:
  • from old-ContentDocumentId (in the extracted CDL table) to old-ContentVersionId (in your exported data)
  • from old-ContentVersionId to new-ContentVersionId (in your Data Loader success file from the ContentVersion upload)
  • from new-ContentVersionId to new-ContentDocumentId (in the export file you created four steps ago).
• Insert the new ContentDocumentLinks.
  • Be prepared for some of them to fail if you've changed anything about your user set or record ownership.

I would be delighted if anyone were to propose a better solution. This migration took me two solid days to develop and execute, and it seems ridiculously baroque to me.