How to parse the returned value of a query using a GROUP BY and Force.com-Toolkit-for-PHP

Question

I'm selecting custom fields from an object and using GROUP BY to return those fields without repeating them.

Something like this:

    SELECT 
      Produto__r.Name,
      Produto__r.Id, 
      Produto__r.Id_Test__c
    FROM
      Veiculos__c
    WHERE
      Ativo__c = true
    AND
      Produto__r.Marca__c = 'a02j0000004kRhJAAA'
    GROUP BY
      Produto__r.Name,
      Produto__r.Id,
      Produto__r.Id_Test__c

When I convert the result to json the output is an array of objects with two properties, one of those properties is what I need.

  [
    {
      Id: [
        "01tj0000001EbUZAA9",
        "01tj0000001EbUZAA9"
      ],
      any: "<sf:Name>Vehicle Name</sf:Name><sf:Id>01tj0000001EbUZAA9</sf:Id><sf:Id_Test__c>23</sf:Id_Test__c>"
    }
  ]

I was expecting the content of property "any" to be my parsed object. I'm making something wrong or this is the correct result and I need to parse the XML manually?

Thanks in advance.

UPDATE:

I'm showing just one record but it will return more than one. I was expecting the property any to be my result like:

  [
    {
      'Name': 'Vehicle Name',
      'Id': '01tj0000001EbUZAA9',
      'Id_Test__c': '23'
    },
    {
      'Name': 'Vehicle Name',
      'Id': '01tj0000001EbUZAA8',
      'Id_Test__c': '24'
    }
  ]

* UPDATE 2 *

Actually my question is HOW can I parse the value inside ANY param

<sf:Name>Vehicle Name</sf:Name><sf:Id>01tj0000001EbUZAA9</sf:Id><sf:Id_Test__c>23</sf:Id_Test__c>

If I try to parse the string using simplexml_load_string I get this error:

simplexml_load_string(): namespace error : Namespace prefix sf on Name is not defined

Sorry for the mess in this question :(

Answer 1

I think you're perhaps had different expectations of the Group By Clause than how it actually works.

You've queried for 3 fields and I sense that you wanted the 3 fields to be "joined". That isn't what a Group By clause does. Instead, it's more like a "sorting of records" where if you'd have put Group By on just Produto__r.Name, all records with the most number of identical Produto__r.Name values would have been returned 1st followed by the records with the 2nd most number of identical Produto__r.Name values.

That's essentially what you should be seeing now where the grouping order is first based on Produto__r.Name. When the quantity of Name field values is the same, the group order would be based on the number of records returned with the most identical Produto__r.Id records. The last grouping criteria would be based on the most number of identical Produto__r.Id_Test__c records. The latter didn't need to be specified since that would have happened by default since there was nothing left to group results by.

Answer 2

So after much research I've come up with this solution, maybe isn't the best and I HOPE not (just 'cause I think that has to exist a solution better than mine) but I have my problem solved for now (:

To stripe down the result of the query as an usable Object:

  $query = Salesforce::query("
    SELECT 
      Produto__r.Name,
      Produto__r.Id, 
      Produto__r.Id_Test__c
    FROM
      Veiculos__c
    WHERE
      Produto__r.Marca__c = 'a02j0000004kRhJAAA'
    GROUP BY
      Produto__r.Name,
      Produto__r.Id,
      Produto__r.Id_Test__c");

  $result = $query->records;
  $objects = [];

  foreach($result as $obj) {
    $str = preg_replace('{sf:}', '', $obj->any);

    $xml = '<?xml version="1.0" encoding="UTF-8"?><obj>'.$str.'</obj>';

    $objects[] = simplexml_load_string($xml);
  }

Basically I'm removing the sf: namespace from the xml tags, adding the xml doc type and loading it as a xml string. Here is what I get when the result is converted to json:

  [
    {
      Name: "Label",
      Id: "01tj0000001EbUZAAA",
      Id_Test__c: "23"
    },
    {
      Name: "Label 2",
      Id: "01tj0000001EbUeAAB",
      Id_Test__c: "22"
    }
  ]

Answer 3

You can use $sobject = new SObject($obj) to get the required result. It will essentially parse $sobject->any in pretty similar way to what you're doing.

var_dump($sobject) will provide output similar to:

class SObject#5 (3) {
  public $type =>
  NULL
  public $fields =>
  class stdClass#66 (3) {
    public $Name =>
    string(10) "Label"
    public $Id_Test__c =>
    string(10) "23"
    public $Id =>
    string(18) "01tj0000001EbUZAAA"
  }
  public $Id =>
  string(18) "01tj0000001EbUZAAA"
}