2

I am executing 2 similar queries on salesforce soap api which has similar columns (infact the first query has all the columns that the second one has) yet the number of records are coming different, Following are the queries

QUERY 1:

select Source_Medium__c, Lead_Source__c, Source_Channel__c, CreatedDate, Ca_pk__c, 
      (select Id, CreatedDate, Reason__c, Call_Result__c, Next_Action__c, Due_Date__c 
          from Logs__r order by CreatedDate desc limit 1) 
              from Contact where  Ca_pk__c in :ca_pks order by CreatedDate desc

QUERY 2:

select CreatedDate, Ca_pk__c, (select Id from Logs__r) from Contact where  Ca_pk__c 
        in :ca_pks order by CreatedDate desc

Total records in the first case is 1501 and in second case it is 1461.

P.S. Note: when I executed both these queries using Developer Console Query Editor the results are same i.e. 1501

5
  • 3
    Does the SOAP API user running the 1st query have the correct FLS permissions to retrieve all the fields in the records you're querying for? If not, that would explain the discrepancy between the two queries.
    – crmprogdev
    Jun 26 '18 at 11:21
  • Are the items in your Ca_pk__c filter the same? Could you remove them in favor of replacing them with merge syntax for the sake of readability, like Ca_pk__c IN :IdList? I assume they come from the same source & have the same values, which is why you expect them to return the same values. Jun 26 '18 at 13:41
  • @crmprogdev the user running both the queries is same and has administrative privileges, so i dont think that could be the reason for the discrepancy and even when running from query editor the user is the same administrative user Jun 27 '18 at 5:15
  • What I further figured out is that when i remove limit 1 from the first query it also returns 1461 (although its an incorrect answer).. And i cannot add limit in the second query as I need total count of logs for each contact Jun 27 '18 at 5:19
  • @battery.cord, Yes the values come from the same source(variable) and hence have same values, I have edited the query and replaced it with the variable – Jun 27 '18 at 5:48
1

In addition to there not being a Limit1 clause, the one other thing of significance that's different in Query 2 from Query 1, is that there's no OrderBy clause in the Query 2's sub select clause. It would seem that the Limit clause must be the reason you're seeing a difference in what's being returned in your SOAP query.

This would also seem to be a function of the WSDL. That would explain the difference between what you're seeing between what's returned from the SOAP query vs what you see running Anonymous Apex in the Developer Console.

EDIT: further explanation in response to comments

When querying an org via the SOAP API, the query is constrained by the WSDL. In essence, the WSDL controls how the query is actually processed which may not be exactly how it would be processed if run in the Developer Console. It may be close, but not exactly the same. That's because the WSDL defines the relationships between the objects involved and thus, exactly how they can be queried.

In addition to the differences in the number of fields queried (trivial at best), there really are only two differences of importance. The first is the Limit 1 clause having been removed from Query 2. The other is the OrderBy clause not being included in that same sub select clause.

Hopefully, its clear why the OrderBy clause would have no impact on the number of records returned. Once that's observed, one can easily conclude that its omitting the Limit 1 clause that allows Query 2 to return more records than Query 1; thereby increasing the number of records it returns.

As for advice on how to form the query to ensure you get all of what you desire, I would recommend removing the limit 1 clause from Query 1 and see if it runs correctly. It's Id will still need to be in the list of records that you're querying and if you're not getting duplicates from Query 2, I wouldn't expect them from Query 1 if executed without the limit clause.

BTW, someplace in the editing of your question, the order of the records returned seems to have gotten reversed from what was stated in your original question: 1461 for Query 1 vs 1501 for Query 2.

4
  • I am not able to understand you suggestion, could you be more explanatory on it, also I have already tried to add the order by clause in the second query but it isn't making any difference in the output Jun 28 '18 at 4:59
  • My Intention overall here is that i want certain fields from Contact table, Then I want the latest log record and a count of all logs for each contact, i would appreciate if you could suggest me any other way to fire a query for this requirement, I have tried a few other ways of forming this query but no luck, any help on query formation would be appreciated. Thanks. Jun 28 '18 at 5:13
  • Yes the answers were incorrect earlier which i corrected later to 1501 in the first case and 1461 in second case; which is why i am not able to understand the behavior as in first case even after limit being their total contacts coming are more then the one in the second case when there is not limit. Any further help on this would be appreciated as i have tried lot of things and possibilities yet i am not getting the desired output. Jun 29 '18 at 7:06
  • My answer was based on the original information. Because of that, the only thing left would be to attribute the discrepancies to something related to the WSDL and how it processes the difference in the query structure.
    – crmprogdev
    Jun 29 '18 at 12:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.