WHERE clause didn't work as expected

Hi TG Team

Here are my two queries:

Query 1:

SetAccum<VERTEX<Claim>> @claims;
_t1 = SELECT c1 FROM claim:c1 - (<ASSOCIATE_WITH.ASSOCIATE_WITH>) - claim:c2
            WHERE abs(datetime_diff(c1.accident_time, c2.accident_time)) < 100000
            AND 
  count(c1.neighbors("REV_ASSOCIATE_WITH") INTERSECT c2.neighbors("REV_ASSOCIATE_WITH")) > 0
            AND c1 != c2
            ACCUM 
              c1.@claims += c2 END
            POST-ACCUM c1.@claims += c1;

Query 2:

SetAccum<VERTEX<Claim>> @claims;
_t1 = SELECT c1 FROM claim:c1 - (<ASSOCIATE_WITH.ASSOCIATE_WITH>) - claim:c2
                ACCUM 
                      IF abs(datetime_diff(c1.accident_time, c2.accident_time)) < 100000
                         AND count(c1.neighbors("REV_ASSOCIATE_WITH") INTERSECT c2.neighbors("REV_ASSOCIATE_WITH")) > 0
                         AND c1 != c2 THEN
                               c1.@claims += c2 END
                POST-ACCUM c1.@claims += c1;

If I understand correctly, in the first query, the WHERE clause will first filter the matching table produced by the FROM statement, and the ACCUM will iterate over the resulting matching table by row.
In the second query, the ACCUM will iterate over the matching table produced by FROM, and the accumulation is only done for rows satisfying certain conditions.

I thought the above two queries, while behaving differently, should give me the same result. But they did not.
In the first query, the WHERE clause was not working, since I was still able to find claims in @claims where the difference in accident time is larger than 100000. The second query worked as intended.

So my question is why did they yield different results? Did I misunderstand something? Thanks!

Hello,

Here are a few reasons why you may be seeing different results between the queries:

  • Query 1 has a misplaced (unnecessary) “END” statement at the end of the ACCUM clause. Perhaps an older version of this query is being run due to the syntax error.

  • In Query 1, the WHERE clause conditions apply to all the clauses that follow it (essentially like an IF condition that is considered for each additional clause). On the other hand, in Query 2, your IF condition only applies to the ACCUM clause since it is contained within it. This means that the condition does not apply for the later POST-ACCUM clause, so the behavior of the POST-ACCUM clauses between the two queries will differ.

Let me know if Query 1 is still giving you issues and I can investigate further.

Hi Ishestakov,

The “END” in query 1 is a typo which was not present in my actual code. And while the where clause and the IF statement inside accum will impact the post-accum differently, in my case they should be the same.

I have run multiple tests and the two queries still have different results. I suspect the issue lies in the multiple hop pattern matching.

Hello @Iuyilun
Can you please help me to find out the function which returns path from the given source and destination vertex . It will be very helpful for me
Suppose we have a Account vertex and City vertex and State vertex and Country vertex
function(Account, City)-> Account:a-(lives_in:l)-City:c-(state:ci)-…Country:c

Thanks

Hello @Ishestakov
Can you please help me to find out the function which returns path from the given source and destination vertex . It will be very helpful for me
Suppose we have a Account vertex and City vertex and State vertex and Country vertex
function(Account, City)-> Account:a-(lives_in:l)-City:c-(state:ci)-…Country:c

Thanks

@pkr2 If you are using GraphStudio pathfinding is built in. If you go to “Explore Graph” then click on the network looking icon you will find the path finding features. Documentation on that feature https://docs.tigergraph.com/ui/graphstudio/explore-graph#TigerGraphGraphStudioUIGuide-ExploreGraph
image

You can also access hit that path finding features from the built in REST++ endpoints. See here for examples: https://docs.tigergraph.com/dev/restpp-api/built-in-endpoints#path-finding-algorithms

Thanks for your response
Sir i am asking in backend code
I just want to make the select statement from given vertex to destination vertex thats all
Suppose we have a Account vertex and City vertex and State vertex and Country vertex:
Below is the example
function_to_get_select_path(Account, City)-> Account:a-(lives_in:l)-City:c-(state:ci)-…Country:c

First question - are you specifying SYNTAX V2 in your query? You need to use V2 syntax to run multi-hop queries. I have gotten to the point where I use V2 syntax for all of my queries, but I suspect others have different preferences. This is a totally made up example, but may help you

SYNTAX V2

SetAccum<STRING>   @locations;
Select a FROM StartAccount:a -(LIVES_IN>) - City:c  -(IN_STATE>)- State:s
               ACCUM a.@locations += (c.cityName + ", " + s.stateCode);
1 Like

@markmegerian
@Jon_Herke
Hi i appreciate your response but this is not my query , I just want to know a function or something like that which will provide the path corresponding to the given parameters, Suppose we give a vertex name and a destination vertex name so that will gives us the path to that source to destination
Below is the example of my use case:
def function_to_get_select_path(Account, City)
…
return “Account:a-(lives_in:l)-City:c-(state:ci)-…Country:c”