search

superfell / SoqlX

SoqlXplorer is an awesome tool for developers using the Salesforce.com platform.
https://pocketsoap.com/osx/soqlx/
Other
273 stars 54 forks source link

Unusual Query and File Export Behavior #104

Open unclejieves opened 3 years ago

unclejieves commented 3 years ago

I am querying Product2 records which can be hierarchical i.e. Product2 record may have a parent which is a lookup to another product2 record. Not all Product2 records have a parent.

When I query all records( 6k+), which would be inclusive of records with and without a parent, the parent lookup I specify in the query, tk_base_productr.Name, only shows as tk_base_productr in the column heading. The results set also does not sort properly when sorting on the parent lookup. Even when i specify DESC it will show records w/o a parent first.

Screen Shot 2021-02-11 at 7 13 51 AM

Even more strange; When I export to CSV it shows the data structure in the relationship field instead of the value of the field i'm specifying.

Screen Shot 2021-02-11 at 7 14 54 AM

However, if I only query a small dataset the column heading and values are as expected in the query and resulting file. Sorting on the parent field still doesn't work though.

Screen Shot 2021-02-11 at 7 16 45 AM

Screen Shot 2021-02-11 at 7 17 22 AM

SOQLx version is 3.4 MacOs 11.1 w/M1 chip

superfell commented 3 years ago

On your first point about ordering, you need to use NULLS LAST to have the NULLs ordered to the bottom of the results. see https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_orderby.htm#!

On the column headers issue, the headers are calculated from the data seen in the rows of the query result, if there's no name field populated in those rows then it won't appear as a header. There's no API to have sfdc describe what the columns of the query are, so the only ways to determine the columns is from looking at the actual data returned, or by parsing the soql query itself

unclejieves commented 3 years ago

Thank you for the info on NULLS LAST. Embarrassed I'd never used or figured that out until today.

With the large data set, there are rows populated with a value in the __r.Name column. Of the 6k records only 600 of them don't have a value there. When it does not resolve the header correctly the data that shows up in the .csv for those rows is the JSON structure you see in the second screen shot.

Since it does resolve the header and populate the date in the column as expected with a result set of 4, I'm guessing it only resolves those headers based on what is returned in the UI and not the entire data set that can be exported?

On Thu, Feb 11, 2021 at 11:20 AM Simon Fell notifications@github.com wrote:

On your first point about ordering, you need to use NULLS LAST to have the NULLs ordered to the bottom of the results. see https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_orderby.htm# !

On the column headers issue, the headers are calculated from the data seen in the rows of the query result, if there's no name field populated in those rows then it won't appear as a header. There's no API to have sfdc describe what the columns of the query are, so the only ways to determine the columns is from looking at the actual data returned, or by parsing the soql query itself

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/superfell/SoqlX/issues/104#issuecomment-777655833, or unsubscribe https://github.com/notifications/unsubscribe-auth/AGYKGICEAMH6YC4AMNOYDJLS6QGXPANCNFSM4XO27YRQ .

superfell commented 3 years ago

It's resolved from the first set of rows returned by the query API. So all the results you see after clicking Query, but not any of the rows that appear when you subsequently click Query More. (or use the export all rows option in the save dialog) Using NULLS LAST should work as a work around. I'll have to look into having it recheck the column headers after a query more.

unclejieves commented 3 years ago

Thank you for the guidance. The recheck would be a nice feature!

On Thu, Feb 11, 2021 at 1:21 PM Simon Fell notifications@github.com wrote:

It's resolved from the first set of rows returned by the query API. So all the results you see after clicking Query, but not any of the rows that appear when you subsequently click Query More. (or use the export all rows option in the save dialog) Using NULLS LAST should work as a work around. I'll have to look into having it recheck the column headers after a query more.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/superfell/SoqlX/issues/104#issuecomment-777728750, or unsubscribe https://github.com/notifications/unsubscribe-auth/AGYKGIGQJM5RBP5K6SUVKXTS6QU4LANCNFSM4XO27YRQ .