+ Start a Discussion
Peter KayePeter Kaye 

Why do deleted items appear in SOQL queries for some objects and not for others ?

Why do some custom objects show deleted items in SOQL queries and others don't ?
I have two SOQL queries for different custom objects both with deleted items ( as seen in the Recycle bin ) and both including the IsDeleted field. Niether query has any WHERE statement.  For one object the query retrieves records where IsDeleted = 1 or blank.  But for the other object only IsDeleted = blank   records show..  Why is this ?  Is there some object setting that could explain this.  Thanks. 
Best Answer chosen by Peter Kaye
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello Peter,

Great! I've never worked using PHP with Salesforce but looking at your code I can suggest you a test:

Note that the query that does not show the deleted records has
$mySforceConnection->query($query);
And the one that works has
$mySforceConnection->queryAll($query);

So my best guess is that queryAll implies the use of ALL ROWS keyword. I suggest you to change the other query to use queryAll as well.

Hope to have helped!

Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.
 
 

All Answers

Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello Peter,

How are you executing this your query? Can you share it? 

It sounds weird first because IsDeleted is never blank, either is false (default) or true (when deleted). And second because if you don't use the ALL ROWS keyword is not supposed to show deleted records.
 
// Show all records including deleted
system.debug([SELECT Name, Isdeleted FROM Account ALL ROWS]);

// Does not show deleted records
system.debug([SELECT Name, Isdeleted FROM Account]);

Hope to have helped!

Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.
 
Peter KayePeter Kaye
Hi Zuinglio

Thanks for coming back on this.  Yes,  you have helped !

The SOQL is executed using the Force.com php toolkit.  Here is the php code snippet for the object that fails to show deleted records.  
 
$query = "SELECT Id, OwnerId, IsDeleted, Name, CreatedDate, CreatedByID, LastModifiedDate, LastModifiedById, SystemModStamp, LastViewedDate, LastReferencedDate, Event_Date__c, Event_Start__c, Description__c, Event_Title__c, Church_Location__c, Other_Location__c, Event_Type__c, Lectionary6__c, Event_Daten__c  from Church_Event__c ";

$response = $mySforceConnection->query($query);

echo "<pre>";
print_r ($response);
echo "</pre>";

Here is the snippet for the object where deleted records do appear.  
 
$query = "SELECT Id, IsDeleted, OwnerId, Name, CreatedById, Historic__c, SystemModstamp, LastModifiedById, LastViewedDate, LastReferencedDate, Service_Date_and_Time__c,  Service_Date__c,  Lectionarysv__c, LectionaryNew__c, Service_Time__c, CreatedDate, LastModifiedDate, Notes__c, Leader__c, Location__c, Service_Type__c,  Lectionary6__r.Lectionary_Date__c,Lectionary6__r.Colour__c,Lectionary6__r.Period_Name__c, Lectionary6__r.Selected_Reading_Line__c, Funeral__c, Web_Publish__c, Wedding__c, Calendar_AllCh__c, Calendar_SpCh__c, Preacher__c, Service_Title__c, Lectionary6__c from Service3__c";

$response = $mySforceConnection->queryAll($query);

echo "<pre>";
print_r ($response);
echo "</pre>";
Here is the print_r response string showing IsDeleted blank for record 258  and  1 for 259.
..
[258] => stdClass Object
                (
                    [Id] => a0O58000003OJpOEAW
                    [Calendar_AllCh__c] => Ashburto Weddi GF
                    [Calendar_SpCh__c] => Wedding GF
                    [CreatedById] => 00558000001OPa8AAG
                    [CreatedDate] => 2017-01-18T21:32:43.000Z
                    [Historic__c] => 
                    [IsDeleted] => 
                    [LastModifiedById] => 00558000001OPa8AAG
                    [LastModifiedDate] => 2017-04-20T11:16:09.000Z
                    [LastReferencedDate] => 2017-04-20T11:16:09.000Z
                    [LastViewedDate] => 2017-04-20T11:16:09.000Z
                    [Leader__c] => GF
                    [Lectionary6__c] => a0P58000001DxL3EAK
                    [Lectionary6__r] => stdClass Object
                        (
                            [Id] => 
                            [Colour__c] => Red/Green
                            [Lectionary_Date__c] => 2017-11-04
                            [Period_Name__c] => All Saints Day
                            [Selected_Reading_Line__c] => Romans 11.1–2, 11–12, 25–29, Psalm 94.14–19, Luke 14.1, 7–11
                        )

                    [Location__c] => Ashburton
                    [Name] => S-000452
                    [OwnerId] => 00558000001OPa8AAG
                    [Service_Date__c] => 2017-11-04
                    [Service_Date_and_Time__c] => 2017-11-04T12:00:00.000Z
                    [Service_Time__c] => 13:00
                    [Service_Title__c] => 04/11/2017 Ashburton Wedding(Gill Drummond and Gareth Griffiths)
                    [Service_Type__c] => Wedding
                    [SystemModstamp] => 2017-04-21T17:03:42.000Z
                    [Web_Publish__c] => 
                    [Wedding__c] => a0M580000014t84EAA
                )

            [259] => stdClass Object
                (
                    [Id] => a0O58000003OJpTEAW
                    [Calendar_AllCh__c] => Buckland Weddi
                    [Calendar_SpCh__c] => Wedding
                    [CreatedById] => 00558000001OPa8AAG
                    [CreatedDate] => 2017-01-18T21:36:18.000Z
                    [Historic__c] => 
                    [IsDeleted] => 1
                    [LastModifiedById] => 00558000001OPa8AAG
                    [LastModifiedDate] => 2017-03-12T08:04:22.000Z
                    [LastReferencedDate] => 2017-03-12T08:04:22.000Z
                    [LastViewedDate] => 2017-03-12T08:04:22.000Z
                    [Lectionary6__c] => a0P580000048Br2EAE
                    [Lectionary6__r] => stdClass Object
                        (
                            [Id] => 
                            [Lectionary_Date__c] => 2018-03-03
                        )

                    [Location__c] => Buckland
                    [Name] => S-000453
                    [Notes__c] => Date uncertain.
Amy Grant and Robert
                    [OwnerId] => 00558000001OPa8AAG
                    [Service_Date__c] => 2018-03-03
                    [Service_Date_and_Time__c] => 2018-03-03T22:59:00.000Z
                    [Service_Time__c] => 23:59
                    [Service_Title__c] => 03/03/2018 Buckland Wedding(Amy Grant and Robert)
                    [Service_Type__c] => Wedding
                    [SystemModstamp] => 2017-03-13T02:59:27.000Z
                    [Web_Publish__c] => 
                    [Wedding__c] => a0M580000014t89EAA
                )

Maybe I need to look at this data in Eclipse and run the Apex code you have suggested.. I haven't done this so far because I thought there might be a simple explanation !

There is something funny about the custom object that fails to show deleted items. I have 6 other custom objects that do not have this issue. The need to access deleted items is important because I run an hourly program that synchronises SF data with a MySQL db by selecting all SF records with a  LastModifiedDate > than a stored value and then deleting and recreating the records in the MySQL db.  If I can't pick up deleted records then  this approach won't work - I'll need to sort deletions by comparing and Id list between MySQL and SF. 
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello Peter,

Great! I've never worked using PHP with Salesforce but looking at your code I can suggest you a test:

Note that the query that does not show the deleted records has
$mySforceConnection->query($query);
And the one that works has
$mySforceConnection->queryAll($query);

So my best guess is that queryAll implies the use of ALL ROWS keyword. I suggest you to change the other query to use queryAll as well.

Hope to have helped!

Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.
 
 
This was selected as the best answer
Peter KayePeter Kaye
Thanks Zuinglio.  Brilliant - I had a feeling the answer was straightforward.  I guess my eyes focussed on the SOQL statement and so didn't pick up that I was using two different query objects !    Thanks again for your help on this.