3

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 :(

2
  • Could you clarify which property it is that you need? How are you wanting the properties to be grouped? Also, is this essentially a single record query? – crmprogdev Jul 1 '15 at 14:46
  • I'm sorry @crmprogdev, hope it's more clear now. – Thiago Coelho Jul 1 '15 at 14:58
0

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.

2
  • Thank you for taking time to answer my question :) I think that the result is what I'm expecting from GROUP BY. The problem is how to parse the content that is being returned by the query, inside the ANY param. <sf:Name>Vehicle Name</sf:Name><sf:Id>01tj0000001EbUZAA9</sf:Id><sf:Id_Test__c>23</sf:Id_Test__c> – Thiago Coelho Jul 1 '15 at 16:53
  • Ahh, your question is much clearer now. It sounds like you're wanting to strip the salesforce fields names and brackets from the query results? If so, the only way I know of off hand is to do that using string methods or pattern matcher methods with regex. – crmprogdev Jul 1 '15 at 17:14
0

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"
    }
  ]
0

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"
}

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.