+ Start a Discussion
Duc Ha 4Duc Ha 4 

SOQL strange behavior

I use Workbench to run some SOQL queries to get data from my ContentDocument table. My first query simply lists all the records: 
"Select Id, Title from ContentDocument"
The number of records matchs what's returned by the SOQL API. However, if I select to include "Deleted and Archived Records" from the console, I see a couple more data. I am new to SFDC so this is a bit strange for me. But that wasn't the strangest part - the strangest part is by accident I ran into some DocumentIDs that were NOT included in the table, but if I search for them specifically, Workbench returns them! 

"Select Id, Title from ContentDocument where Id=<foo>"

I double-checked several times, using Title and ID so it's not my eyes. Plus there are more than 1 records missing.

So I have 2 questions: 
1> What do I need in my SOQL query to get everything from the table? I will only use API for production 
2> Why do I not see some records with a statement that should return everything (I come from vanilla SQL camp)?










 

 
ArmouryArmoury
Can you check while doing the SOQL query with the condition (where Id ='xyz'), the 'Deleted & Archived records' option's 'Include' value is not selected. If it is selected, then probably the missing records returned were from the deleted ones. Try to add the field 'IsDeleted' in your select statement to check the value.
Duc Ha 4Duc Ha 4
Thanks for your suggestion. I did that, and it's neither deleted nor archived. I can actually see it in SFDC from the context menu. 
David ZhuDavid Zhu
I don't have experience with ContentDocument object in SFDC. Just curious if you don't mind.

ContentDocument id has prefix '069'. Do you mean it returns record if your soql in workbench is like this?
select id, title from ContentDocument where id = '001xxxxxxxxxxx'       -- prefix 001 is for account object.

if use ALL ROW keyword in your apex code, you can get all records including deleted and archived ones. This keyword won't work in Developer Console.
Or you can use Connection.QueryAll( 'your soql command') to get the same result as All ROW keyword in Java or C#.

 
Duc Ha 4Duc Ha 4
David: Ah - interesting I didn't know that.  But yes all of the missing IDs start with 069... 
On the ALL ROW keyword, I use  REST calls so looks like it's not supported. But anyway, the missing records are not deleted (based on isDeleted property).
David ZhuDavid Zhu
I guess there are large number of records in Contentdocument (more than 50,000). SOQL only returns up to 50,000 records per Salesforce governor limits.
Duc Ha 4Duc Ha 4
@David: Thanks, but we are not that big, only < 400 records returned anyway :(. I also tried SOQL query through Developer Console and it's consistent with what I see on Workbench. This is really mind boggling, I first thought it could have been some security setting on the missing records, but this doesn't make sense if I could still them individually. 
 
Duc Ha 4Duc Ha 4
I also had our admin tried on workbench and she got the same results, so I think it's safe to cross permission issues out.
 
ArmouryArmoury
Someone already asked this qn before.. Looks like known issue but not sure what is the solution..
http://salesforce.stackexchange.com/questions/2206/obtaining-all-contentdocument-records-using-soql
Duc Ha 4Duc Ha 4
We found out the missing records were in the private space of the owners. For an admin to see them, the query needs to use the scope "allPrivate".