function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Michael MMichael M 

Query not returning any records- why not??

Hello, in our Recycle bin, we have SEVERAL ContentDocuments (We have scheduled apex that deletes files every day.) Why, then, does the below query return 0 results? Do i need to structure the query differently? 

SELECT id,IsDeleted,LastModifiedDate FROM ContentDocument WHERE IsDeleted = true and lastmodifieddate >= LAST_N_DAYS:2 ALL rows
Best Answer chosen by Michael M
David Zhu 🔥David Zhu 🔥
You cannot use limit 10000 to replace ALL ROWS. ALL ROWS keywords is to query all records in an organization, including deleted records and archived activities.

You may use batch class if there are that many files to be undelete.

If it is one time shot, you can do :

List<ContentDocument> cds = [SELECT id,IsDeleted,LastModifiedDate FROM ContentDocument 
                             WHERE IsDeleted = true  and FileType != 'SNOTE'
                             and LastModifiedDate >=LAST_N_DAYS:3 
                             and LastModifiedDate <=LAST_N_DAYS:3
                             ALL rows];  


List<ContentDocument> cdList = new List<ContentDocument>();
for (integer i =0;i<cds.size();i++)
{
     cdList.add(cds[i]);

    if (cdList.size() == 10000);
    {  break;
     }
}

if (cdList.size() > 0)
    undelete cdList.


 You may have to run multiple times to complete undelete all files.
 

All Answers

David Zhu 🔥David Zhu 🔥
I guess you might use this soql in Developer Console Query Editor.
LAST_N_DATYS and ALL ROWS are SOQL statement features with Apex code. It won't work in Query Editor.
You may try the code below in developer console anonymous window to get how many files were deleted

List<ContentDocument> cds = [SELECT id,IsDeleted,LastModifiedDate FROM ContentDocument WHERE IsDeleted = true and lastmodifieddate >= LAST_N_DAYS:2 ALL rows];

system.debug(cds.size());
VinayVinay (Salesforce Developers) 
Hi Michael,

The developer console and anonymous apex are running the SOQL query via two different mechanisms.

For anonymous apex the ALL ROWS statement is the correct way to identify that you want to include deleted records and archived activities in the results.

The developer console query window isn't running the query via apex. Instead, it is directly using the APIs to run the SOQL query. While ALL ROWS is valid syntax in the query, it doesn't actually do anything against the API.

I was able to retrieve using workbench.

User-added image

Below query will work in apex.
SELECT id,IsDeleted,LastModifiedDate FROM ContentDocument WHERE IsDeleted = true and lastmodifieddate >= LAST_N_DAYS:2 ALL ROWS

You might also want to review below idea link.

(Allow use of "ALL ROWS" within Developer Console query editor)
https://success.salesforce.com/ideaView?id=08730000000DpgiAAC

https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/dome_queryall.htm

Hope above information was helpful.

Please mark as Best Answer so that it can help others in the future.

Thanks,
Vinay Kumar
 
Michael MMichael M
Hi Vinay and David, thank you for all the information- this is great to know.  This query is still returning 0 results when I run it in the anonymous window:SELECT id,IsDeleted,LastModifiedDate FROM ContentDocument WHERE IsDeleted = true and lastmodifieddate >= LAST_N_DAYS:2 ALL ROWS

And in the query editor, if I run the query without "all rows", it also returns 0 results. How can i get that working?
VinayVinay (Salesforce Developers) 
Hi Michael,

I tried to check and I can see 2 id's in logs.

User-added image

Do you see the same when you try to execute query from workbench?

Can you check if 'Query All Files' permission is enabled on profile.

https://salesforce.stackexchange.com/questions/124173/show-deleted-record-show-on-vfpage-which-are-existing-in-recycle-bin/124174

Thanks,
Vinay Kumar
David Zhu 🔥David Zhu 🔥
I believe the document was deleted by other users, not the user running the query.
To allow the user query all deleted files in org's recycle bin, you have to do this:
1. create an permission set, let's call it query all.
2. in the permisisonset , click App Permissin setction
3. scroll down to Content section, enable "Query All files".
4. Assign the user to the permission set.

The user should be good to get all delete files by using the code below in anonymous window.

List<ContentDocument> cds = [SELECT id,IsDeleted,LastModifiedDate FROM ContentDocument WHERE IsDeleted = true and lastmodifieddate >= LAST_N_DAYS:2 ALL rows];

system.debug(cds.size());
 
Michael MMichael M
Hi all, thank you again for your assistance. I gave myself the Query All Files permission, but on my profile, and as a permission set, but my query is still returning 0 results. What else might it be?
David Zhu 🔥David Zhu 🔥
It is weired. 
Can you try the following code by removing the date condition?

List<ContentDocument> cds = [SELECT id,IsDeleted,LastModifiedDate FROM ContentDocument WHERE IsDeleted = true ALL rows];
Michael MMichael M
Hi David, with that query, it worked and the size of the list is 42,890! I'm not sure why that one worked and the others did not?

I only want to Undelete files that were deleted on 6/22/2020 (3 days ago). Can that be built into the query? 
VinayVinay (Salesforce Developers) 
Hi Michael,

Above query looks good.  Can you try with workbench and check if you see any results?

Also, can you try a few combinations like LAST_N_DAYS:7 or LAST_N_DAYS:4 and see results display.

If you still see issue please log a case with salesforce support.

Thanks,
Vinay Kumar
Michael MMichael M
I'm thinking maybe it is not reading the Last_N_Days variable. For example, I just tried it this way: 

List<ContentDocument> cds = [SELECT id,IsDeleted,LastModifiedDate FROM ContentDocument WHERE IsDeleted = true ALL rows]; 
List<ContentDocument> cdNewList = new List<ContentDocument>();
system.debug('size of list: '+ cds.size());

for (ContentDocument cd : cds){
    if (cd.lastmodifieddate < THIS_WEEK){
        cdNewList.add(cd);
    }
} system.debug('Size of new list: '+ cdNewList.size());


and i got this error: Line: 6, Column: 31
Variable does not exist: THIS_WEEK
David Zhu 🔥David Zhu 🔥
I think you get Vinay's suggestion wrong.
You should use Last_n_days in the query. It queries the filed deleleted in last 7 days. You can change the number accordingly.

List<ContentDocument> cds = [SELECT id,IsDeleted,LastModifiedDate FROM ContentDocument WHERE IsDeleted = true and LastModifiedDate >=LAST_N_DAYS:7 ALL rows]; 

I just tested and this query definitely works.
Michael MMichael M
I see- thank you! That worked. I just ran this query:

List<ContentDocument> cds = [SELECT id,IsDeleted,LastModifiedDate FROM ContentDocument 
                             WHERE IsDeleted = true 
                             and LastModifiedDate >=LAST_N_DAYS:3 
                             and LastModifiedDate <=LAST_N_DAYS:3
                             ALL rows]; 

and it returned 37,566 rows. Is this the correct query to get all files deleted on 6/22 (i.e. 3 days ago)?
David Zhu 🔥David Zhu 🔥
The above query result includes delete notes and files. Try this one to filter out Notes.

List<ContentDocument> cds = [SELECT id,IsDeleted,LastModifiedDate FROM ContentDocument 
                             WHERE IsDeleted = true  and FileType != 'SNOTE'
                             and LastModifiedDate >=LAST_N_DAYS:3 
                             and LastModifiedDate <=LAST_N_DAYS:3
                             ALL rows]; 
Michael MMichael M
Ok great. Just for the final piece, I just tried to run this:
List<ContentDocument> cds = [SELECT id,IsDeleted,LastModifiedDate FROM ContentDocument 
                             WHERE IsDeleted = true  and FileType != 'SNOTE'
                             and LastModifiedDate >=LAST_N_DAYS:3 
                             and LastModifiedDate <=LAST_N_DAYS:3
                             ALL rows];  
system.debug('size of list: '+ cds.size());
undelete cds;

And received this error: "Line: 7, Column: 1
System.LimitException: Too many DML rows: 10001"

I guess I need to do 10000 at a time. But this returns 0 rows:
List<ContentDocument> cds = [SELECT id,IsDeleted,LastModifiedDate FROM ContentDocument 
                             WHERE IsDeleted = true  and FileType != 'SNOTE'
                             and LastModifiedDate >=LAST_N_DAYS:3 
                             and LastModifiedDate <=LAST_N_DAYS:3
                             limit 10000];  
system.debug('size of list: '+ cds.size());


How can I do this correctly? Thanks again for all the help it is really apprecaited!! 
David Zhu 🔥David Zhu 🔥
You cannot use limit 10000 to replace ALL ROWS. ALL ROWS keywords is to query all records in an organization, including deleted records and archived activities.

You may use batch class if there are that many files to be undelete.

If it is one time shot, you can do :

List<ContentDocument> cds = [SELECT id,IsDeleted,LastModifiedDate FROM ContentDocument 
                             WHERE IsDeleted = true  and FileType != 'SNOTE'
                             and LastModifiedDate >=LAST_N_DAYS:3 
                             and LastModifiedDate <=LAST_N_DAYS:3
                             ALL rows];  


List<ContentDocument> cdList = new List<ContentDocument>();
for (integer i =0;i<cds.size();i++)
{
     cdList.add(cds[i]);

    if (cdList.size() == 10000);
    {  break;
     }
}

if (cdList.size() > 0)
    undelete cdList.


 You may have to run multiple times to complete undelete all files.
 
This was selected as the best answer
VinayVinay (Salesforce Developers) 
If you want to do DML operations to more than 10000 records at a time, call a batch class from the current class to handle it separately.

Thanks,
Vinay Kumar
Michael MMichael M
That is doing the job! Thank you to both you of very much- you've taught me a lot!!!