How to get complex information in one GSQL SELECT

GSQL
#1

It’s all abount SYNTAX V2.
I have a star-like graph with main PERSON vertex and many other vertices that add new info about person: address, phone, college, insurance, bank_account,labour account,etc
рр

And I want to get in one QSGL query all information about person
Now I use such method:

  SetAccum<STRING> @PersonsCourses;
  SetAccum<STRING> @PersonsFriends;
  SetAccum<STRING> @PersonsNetworkAccounts;
  SetAccum<STRING> @PersonsInsurances;
  SetAccum<STRING> @PersonsLabour;
  
  GetPersonsInfo = SELECT pin
                  FROM person:pin - (:e) - :tgt 
                    where pin == person_in
                    ACCUM CASE WHEN tgt.type == "person" 
                              THEN pin.@PersonsFriends += tgt.name
                               WHEN tgt.type == "course" 
                              THEN pin.@PersonsCourses += tgt.name
                               WHEN tgt.type == "network_account" 
                              THEN pin.@PersonsNetworkAccounts += tgt.network_type
                               WHEN tgt.type == "insurance" 
                              THEN pin.@PersonsInsurances += tgt.detail
							   WHEN tgt.type == "labour_account" 
                              THEN pin.@PersonsLabour += tgt.position
							  //and so on
                          END;

But if I need to use more difficult logic, for example:
Get all information about person who does not have account on FACEBOOK
Even these who have no information at all(isolated vertices)
I can do it in such way

////First mark all people who have a FACEBOOK account
OrAccum  @haveFacebook = false;
OrAccum  @notIsolated = false;
  PersonWithFacebook  =  SELECT p
        FROM person:p - (posts_on>:e1) - network_account:s2
        WHERE s2.network_type == "facebook"
        ACCUM p.@haveFacebook += true;  

////And then get all their information 		
  GetPersonsInfo = SELECT pin
                  FROM person:pin - (:e) - :tgt 
                    where pin.@haveFacebook == false
                    ACCUM CASE WHEN tgt.type == "person" 
                              THEN pin.@PersonsFriends += tgt.name
                               WHEN tgt.type == "course" 
                              THEN pin.@PersonsCourses += tgt.name
                               WHEN tgt.type == "network_account" 
                              THEN pin.@PersonsNetworkAccounts += tgt.network_type
                               WHEN tgt.type == "insurance" 
                              THEN pin.@PersonsInsurances += tgt.detail
							   WHEN tgt.type == "labour_account" 
                              THEN pin.@PersonsLabour += tgt.position
							  //and so on
                          END;
//After that get not isolated vertices
  NotIsolatedPerson  =  SELECT p
        FROM person:p - (:e1) - :s2
        ACCUM p.@notIsolated += true; 
//And find those who are isolated
  IsolatedPerson = SELECT p 
                  FROM person:p
                  WHERE p.@notIsolated == false;
//And finally we can get result
  ResultPerson = GetPersonsInfo UNION IsolatedPerson;

So I wrote 5 statements to solve this question in GSQL.In classic SQL I can easily do it in one statement using WITH and LEFT JOIN.
Maybe I miss a way to do the same in GSQL?
Is there an equivalent of WITH and LEFT JOIN clauses in GSQL?

Thank you

#2

I think you only need 3 statements to do that.

after the first two statements, then add:

all_person = {person.*};

This way you get all person vertexes, isolated and non-isolated.

Thanks.

#3

I would also recommend a MapAccum to avoid the need for the CASE statement in the accum clause. Not only would it be a simpler query, but it would not require a change if a new type was added. Something like this

MapAccum<STRING, STRING> @personInfo;
OrAccum @notIsolated;

GetPersonsInfo = SELECT pin
FROM person:pin - (:e) - :tgt
where pin == person_in
ACCUM pin.@personInfo += (tgt.type -> tgt.name).
pin.@notIsolated += true;

2 Likes