function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Viraj MViraj M 

SOQL Query issue - Not displaying Max Date when max column added last in query

Select  MAX(Date__c),Parent__c parentid
From Child__c  
group by Parent__c
If i write max Date column first before Parent__C  then query returns proper output with Date and Parent__C (parentid) 
User-added image

But when i write max date column after Parent__c then query only returns Parent__c(parentid) - Can any one tell why this weird behaivor happens 
Select  Parent__c parendId,MAX(Date__c)
From Child__c
group by Parent__c
User-added image

 
Alain CabonAlain Cabon
Hi,

It is clearly a bug of the workbench. Easy to reproduce with Account and ParentId.

The REST responses are like below with expr0 and are always correct. You can test these requests from Utilities > REST Explorer

But the workbench uses PHP and SOAP : you can read the entire source code of the workbench here:
https://code.google.com/archive/p/forceworkbench/downloads

https://developer.salesforce.com/blogs/engineering/2009/11/php-client-for-forcecom-bulk-api.html

In fact, the workbench considers all the aggregated fields as "Unkown_fields".
If the "Unkown_fields" are the last fields of the response, they are just ... ignored (no array push).
Otherwise, you can have the results of these Unkown_fields because the following fields are pushed.

SOQL: SELECT Parentid parent , MAX(createddate) From Account group by Parentid
Rest request OK: /services/data/v39.0/query/?q=SELECT+Parentid+parent,MAX(createddate)+From+Account+group+by+Parentid

{
  "totalSize" : 3,
  "done" : true,
  "records" : [ {
    "attributes" : {
      "type" : "AggregateResult"
    },
    "parent" : null,
    "expr0" : "2016-12-24T23:13:47.000+0000"
  }, {
    "attributes" : {
      "type" : "AggregateResult"
    },
    "parent" : "0010Y000009SvCyQAK",
    "expr0" : "2016-10-28T20:19:08.000+0000"
  } ]
}

SOQL: SELECT MAX(createddate), Parentid parent From Account group by Parentid​
Rest request OK: /services/data/v39.0/query/?q=SELECT+MAX(createddate),Parentid+parent+From+Account+group+by+Parentid

{
  "totalSize" : 3,
  "done" : true,
  "records" : [ {
    "attributes" : {
      "type" : "AggregateResult"
    },
    "expr0" : "2016-12-24T23:13:47.000+0000",
    "parent" : null
  }, {
    "attributes" : {
      "type" : "AggregateResult"
    },
    "expr0" : "2016-10-28T20:19:08.000+0000",
    "parent" : "0010Y000009SvCyQAK"
  }]
}

But the PHP code of the workbench (KO) is like that and you find easily : Unknown_Field__​
  
class QueryResult {
    public $queryLocator;
    public $done;
    public $records;
    public $size;

    public function __construct($response) {
        $this->queryLocator = $response->queryLocator;
        $this->done = $response->done;
        $this->size = $response->size;

        $this->records = array();

        if (isset($response->records)) {
            if (is_array($response->records)) {
                foreach ($response->records as $record) {
                    $sobject = new SObject($record);
                    array_push($this->records, $sobject);
                };
            } else {
                $sobject = new SObject($response->records);
                array_push($this->records, $sobject);
            }
        }
    }
}


class SearchResult {
    public $searchRecords;

    public function __construct($response) {

        $this->searchRecords = array();

        if (isset($response->searchRecords)) {
            if (is_array($response->searchRecords)) {
                foreach ($response->searchRecords as $record) {
                    $sobject = new SObject($record);
                    array_push($this->searchRecords, $sobject);
                };
            } else {
                $sobject = new SObject($response->searchRecords);
                array_push($this->searchRecords, $sobject);
            }
        }
    }
}

/**
 * Salesforce Object
 *
 * @package SalesforceSoapClient
 */
class SObject {
    public $type;
    public $fields;
    public $fieldsToNull;
    //  public $sobject;

    public function __construct($response=NULL) {
        if (isset($response)) {
            if (isset($response->Id)) $this->Id = $response->Id[0];
            if (isset($response->type)) $this->type = $response->type;
            if (isset($response->fieldsToNull)) $this->fieldsToNull = $response->fieldsToNull;
            if (isset($response->any)) {
                // If ANY is an object, it is either a nested sObject or a nested QueryResult
                if ($response->any instanceof stdClass) {
                    //Nested sObjects (child to parent relationships)
                    if ($this->isSObject($response->any)) {
                        $parentSObjects = array();
                        $sobject = new SObject($response->any);
                        array_push($parentSObjects, $sobject);
                        $this->sobjects = $parentSObjects;
                        //Nested QueryResult (parent to child relationships)
                    } else {
                        $this->queryResult = new QueryResult($response->any);
                    }
                    //Otherwise ANY must be just a field
                } else {
                    //if ANY is not an array, make it one.
                    if (!is_array($response->any)) {
                        $response->any = array($response->any);
                    }

                    $anArray = array();
                    $fieldsToConvert = "";
                    $unknownFieldNum = 1;
                    foreach ($response->any as $item) {
                        if ($item instanceof stdClass) {
                            if ($this->isSObject($item)) {
                                $sobject = new SObject($item);
                                array_push($anArray, $sobject);
                            } else {
                                // this is for parent to child relationships
                                if (!isset($this->queryResult)) {
                                    $this->queryResult = array();
                                }
                                array_push($this->queryResult, new QueryResult($item));
                            }
                        } else {
                            //if the field is not wrapped in sf tag, consider it of an unknown type
                            if (!strstr($item, '<sf:')) {
                                $unknownTag = "Unknown_Field__" . $unknownFieldNum++;
                                $item = "<$unknownTag>$item</$unknownTag>";
                            }
                            $fieldsToConvert .= $item;
                        }
                    }
                    if (isset($fieldsToConvert)) {
                        $convertedFields = preg_replace('/sf:/', '', $fieldsToConvert);
                        $convertedFields = '<Object xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'.$convertedFields.'</Object>';
                        $this->fields = simplexml_load_string($convertedFields);
                    }
                    if (sizeof($anArray) > 0) {
                        $this->sobjects = $anArray;
                    }
                }
            }
        }
    }
We can read the entire source code of the workbench (php) and the dataloader (java). 

Best regards
Alain
Alain CabonAlain Cabon
select max(createddate)
from account

..  works owith workbench because there is only one result (no group by) and there is the push.

A PHP specialist could support the hypothesis. I found the code but I am not sure of the reason for the last aggregated fields of the response without debugging finely the PHP code.

Regards