Sorting ContentVersion based on Related objects like Account, Contact

Question

I want to build a photo gallery where I can sort Images based on accounts, contacts or any other object. But not able to do so due to Data model of ContentVersion/documents.

I can't sort ContentDocumentLinks due to many limitations of query on this object. Like I can't do

[SELECT LinkedEntityId FROM ContentDocumentLink WHERE LinkedEntity.Type IN ('Account')]

Error you will get is

Implementation restriction: ContentDocumentLink requires a filter by a single Id on ContentDocumentId or LinkedEntityId using the equals operator or multiple Id's using the IN operator.

To query contentdocumentLink we need either LinkedEntityId or ContentdocumentId.

One solution that is that create lookup fields on contentversion for different objects we want to sort. Like create lookups for account and contact and populate those whenever a contentDocumentLink record is created. But then 1 Content Version can't be attached to 2 accounts. Also creating and managing multiple lookup fields is just an overhead I want to avoid.

Another solution is to query related objects like

[select id from account sortby name limit 10];

Get Account ids and try to find out related ContentDocuments using

[SELECT LinkedEntityId FROM ContentDocumentLink WHERE LinkedEntityId =: AccountIds]

See if there are enough Contentdocuments there for your First page... if not then query next 10 Accounts and query contentdocumentlink again.

This can work but can be slow if some accounts don't have any contentversions attached to them.

Do we have any other clean solution for sorting contentdocuments on related objects? Or do you prefer first or second solution.

Answer 1

If you have a relatively small number of types that you want to find the related content for, a query of this form for each type (Account, Contact, ...):

ContentDocumentLink[] cdls = [
    select LinkedEntityId, LinkedEntity.Name
    from ContentDocumentLink
    where LinkedEntityId in (select Id from Account)
    order by LinkedEntity.Name
    limit 1000
];

will work. The two queries are combined, allowing the platform's query optimiser to remove redundant work.

You can then combine the results using Apex code and sort as you wish in that Apex code.

Note that this construct that could avoid the need for Apex code:

    where LinkedEntityId in (select Id from Account)
    or LinkedEntityId in (select Id from Contact)

is not allowed as it results in this error:

Semi join sub-selects are not allowed with the 'OR' operator