How can I achieve this Identation

[AllanOricil]

• [X ] Feature
• Bug

Description

I can't achieve the identation shown below.

SELECT
  AccountNumber,
  AccountSource,
  Active__c,
  AnnualRevenue,
  BillingAddress,
  BillingCity,
  BillingCountry,
  BillingGeocodeAccuracy,
  BillingLatitude,
  BillingLongitude,
  BillingPostalCode,
  BillingState,
  BillingStreet,
  CleanStatus,
  CreatedById,
  CreatedDate,
  CustomerPriority__c,
  DandbCompanyId,
  Description,
  DunsNumber,
  Fax,
  Id,
  Industry,
  IsDeleted,
  Jigsaw,
  JigsawCompanyId,
  LastActivityDate,
  LastModifiedById,
  LastModifiedDate,
  LastReferencedDate,
  LastViewedDate,
  MasterRecordId,
  NaicsCode,
  NaicsDesc,
  Name,
  NumberOfEmployees,
  NumberofLocations__c,
  OwnerId,
  Ownership,
  ParentId,
  Phone,
  PhotoUrl,
  Rating,
  ShippingAddress,
  ShippingCity,
  ShippingCountry,
  ShippingGeocodeAccuracy,
  ShippingLatitude,
  ShippingLongitude,
  ShippingPostalCode,
  ShippingState,
  ShippingStreet,
  Sic,
  SicDesc,
  Site,
  SLA__c,
  SLAExpirationDate__c,
  SLASerialNumber__c,
  SystemModstamp,
  TickerSymbol,
  Tradestyle,
  Type,
  UpsellOpportunity__c,
  Website,
  YearStarted,
  (
    SELECT
      AccountNumber,
      AccountSource,
      Active__c,
      AnnualRevenue,
      BillingAddress,
      BillingCity,
      BillingCountry,
      BillingGeocodeAccuracy,
      BillingLatitude,
      BillingLongitude,
      BillingPostalCode,
      BillingState,
      BillingStreet,
      CleanStatus,
      CreatedById,
      CreatedDate,
      CustomerPriority__c,
      DandbCompanyId,
      Description,
      DunsNumber,
      Fax,
      Id,
      Industry,
      IsDeleted,
      Jigsaw,
      JigsawCompanyId,
      LastActivityDate,
      LastModifiedById,
      LastModifiedDate,
      LastReferencedDate,
      LastViewedDate,
      MasterRecordId,
      NaicsCode,
      NaicsDesc,
      Name,
      NumberOfEmployees,
      NumberofLocations__c,
      OwnerId,
      Ownership,
      ParentId,
      Phone,
      PhotoUrl,
      Rating,
      ShippingAddress,
      ShippingCity,
      ShippingCountry,
      ShippingGeocodeAccuracy,
      ShippingLatitude,
      ShippingLongitude,
      ShippingPostalCode,
      ShippingState,
      ShippingStreet,
      Sic,
      SicDesc,
      Site,
      SLA__c,
      SLAExpirationDate__c,
      SLASerialNumber__c,
      SystemModstamp,
      TickerSymbol,
      Tradestyle,
      Type,
      UpsellOpportunity__c,
      Website,
      YearStarted
    FROM
      ChildAccounts
    WHERE
      CreatedDate = 2017-04-05T10:41:42.000+0000
  )
FROM
  Account
WHERE
  CreatedDate = 2017-04-05T10:41:42.000+0000

[paustint]

@AllanOricil - this exact indentation is not supported right now - I will consider adding this as an option.

This is the closest currently supported:

SELECT 
	AccountNumber,
	AccountSource,
	Active__c,
	AnnualRevenue,
	BillingAddress,
	BillingCity,
	BillingCountry,
	BillingGeocodeAccuracy,
	BillingLatitude,
	BillingLongitude,
	BillingPostalCode,
	BillingState,
	BillingStreet,
	CleanStatus,
	CreatedById,
	CreatedDate,
	CustomerPriority__c,
	DandbCompanyId,
	Description,
	DunsNumber,
	Fax,
	Id,
	Industry,
	IsDeleted,
	Jigsaw,
	JigsawCompanyId,
	LastActivityDate,
	LastModifiedById,
	LastModifiedDate,
	LastReferencedDate,
	LastViewedDate,
	MasterRecordId,
	NaicsCode,
	NaicsDesc,
	Name,
	NumberOfEmployees,
	NumberofLocations__c,
	OwnerId,
	Ownership,
	ParentId,
	Phone,
	PhotoUrl,
	Rating,
	ShippingAddress,
	ShippingCity,
	ShippingCountry,
	ShippingGeocodeAccuracy,
	ShippingLatitude,
	ShippingLongitude,
	ShippingPostalCode,
	ShippingState,
	ShippingStreet,
	Sic,
	SicDesc,
	Site,
	SLA__c,
	SLAExpirationDate__c,
	SLASerialNumber__c,
	SystemModstamp,
	TickerSymbol,
	Tradestyle,
	Type,
	UpsellOpportunity__c,
	Website,
	YearStarted,
	(
		SELECT 
			AccountNumber,
			AccountSource,
			Active__c,
			AnnualRevenue,
			BillingAddress,
			BillingCity,
			BillingCountry,
			BillingGeocodeAccuracy,
			BillingLatitude,
			BillingLongitude,
			BillingPostalCode,
			BillingState,
			BillingStreet,
			CleanStatus,
			CreatedById,
			CreatedDate,
			CustomerPriority__c,
			DandbCompanyId,
			Description,
			DunsNumber,
			Fax,
			Id,
			Industry,
			IsDeleted,
			Jigsaw,
			JigsawCompanyId,
			LastActivityDate,
			LastModifiedById,
			LastModifiedDate,
			LastReferencedDate,
			LastViewedDate,
			MasterRecordId,
			NaicsCode,
			NaicsDesc,
			Name,
			NumberOfEmployees,
			NumberofLocations__c,
			OwnerId,
			Ownership,
			ParentId,
			Phone,
			PhotoUrl,
			Rating,
			ShippingAddress,
			ShippingCity,
			ShippingCountry,
			ShippingGeocodeAccuracy,
			ShippingLatitude,
			ShippingLongitude,
			ShippingPostalCode,
			ShippingState,
			ShippingStreet,
			Sic,
			SicDesc,
			Site,
			SLA__c,
			SLAExpirationDate__c,
			SLASerialNumber__c,
			SystemModstamp,
			TickerSymbol,
			Tradestyle,
			Type,
			UpsellOpportunity__c,
			Website,
			YearStarted
		FROM ChildAccounts
		WHERE CreatedDate = 2017-04-05T10:41:42.000+0000
	)
FROM Account
WHERE CreatedDate = 2017-04-05T10:41:42.000+0000

[AllanOricil]

@paustint no worries. I would like to have this implemented on your lib to use it format queries on my vs code extension https://marketplace.visualstudio.com/items?itemName=allanoricil.salesforce-soql-editor
The indentation I showed is much more common across other SQL languages according to this lib https://www.npmjs.com/package/sql-formatter. At the moment I'm using it to format SOQL with a fork I created, and then I fix things that it doesn't support, like formatting these inline datetime. The owner of that lib also said he will give support to SOQL in the future, but if you add support to this indentation before him, I will probably switch to your lib.

[paustint]

@AllanOricil - Thank you for the input. I am going to review sql-formatter to see how they do things as there is a lot of room for improvement in my implementation!

Your extension looks really nice!! You can checkout Jetstream - https://getjetstream.app/ to see the project I am working on, which is the reason this library exists.

[paustint]

@AllanOricil - Take a look at https://paustint.github.io/soql-parser-js/ and test out the changes to the formatter I made today (make sure you are not getting a cached version, you will see this option if you are on the latest version: Add newline and indent after keywords - newLineAfterKeywords)

Also, you may be interested in checking out the utility method named getFlattenedFields().

Here is the use-case:
You have a SOQL query and you want to query the results and show the results in a table.
Parse the SOQL, then pass that into getFlattenedFields() and it will return an array of all the fields and their fill path which you can use as the headers of your table.
You will still need to flatten the data returned from SFDC first.

In addition to this, Salesforce has an undocumented API query parameter for their query API - you can pass ?columns=true when calling the API and they will return the columns.

But beware, there are a number of queries that will fail and some others where the results may be missing fields or come out of order - but in general it is pretty solid. The Developer Console uses this, which is how I found it.

[AllanOricil]

@paustint cool. I was using composition api to get the object details + the query results at the same time. I will try this hidden feature too.

[paustint]

was using composition api to get the object details

Yeah, I should probably look into the composition api. The complexity arises when a query exists (e.x. from history) that was not built in the builder, then it is harder to figure out as it could be a very complex query with lots of related objects as well as subqueries with relationships.

Then if you support aggregate queries without aliases, sfdc generates an auto-incrementing identifier that you have to code yourself, which you cannot get from an API.

I take the approach of starting with the base object, fetching that, then going one relationships at a time to fetch all the metadata. (for example, I have a feature to "Restore the query builder from history or any query you already have" so I need to fetch all the data. Of course there are caching techniques at play here, but it is a hard problem to solve.

[AllanOricil]

Oh I thought about that too. I'm not planning to implement two-way query building, because it is too much to care about for something I'm not being paid to do. When the user is typing the query I only fetch the main object and its fields. I don't break his query back to be exhibit in the builder. Like you said, I would need to store the objects, fields and also create a way to control the cache, which would take a lot of effort and I don't know if people would use. Most of the time I worked as a developer I realized that the most "difficult" and time consuming part of writing queries is just to gather the fields, and remember few keywords. I just wanted to reduce this waste while coding inside vs code. I also wanted to stop getting blind every time I needed to switch from my cool vs code dark theme to the white query builder.
Now that I launched the tool, I will see if people are actually using it, and how they are using it. And if developers actually see value on it and I start to recover the more than 1k hours I invested developing it, I will continue with this project. Otherwise I will let the ones that get paid to make it happen. But thanks for the tips and help. I saw your PR in the sql-formatter repo. Looks very good.