search

jetstreamapp / soql-parser-js

Javascript SOQL parser
https://jetstreamapp.github.io/soql-parser-js/
MIT License
77 stars 20 forks source link

How can I achieve this Identation #137

Closed AllanOricil closed 3 years ago

AllanOricil commented 3 years ago

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 commented 3 years ago

@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 commented 3 years ago

@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 commented 3 years ago

@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 commented 3 years ago

@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.

image

AllanOricil commented 3 years ago

@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 commented 3 years ago

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 commented 3 years ago

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.