[GitHub] [age] Leelst opened a new issue, #944: Suggest that NOT operator can be used in WHERE clause including edge

["Leelst (via GitHub)" <gi...@apache.org>]

Leelst opened a new issue, #944:
URL: https://github.com/apache/age/issues/944

   - Bitnine DB tech team
   
   **Is your feature request related to a problem? Please describe.**
   
   I have a graph with vertices representing 'person' and 'movie', and edges representing the relationship 'acted_in' and I want to find actors who have not appeared in any movies. While there could be several ways to get the answer, In my opinion, I found the simplest method in OpenCypher. 
   
   
   **Describe the solution you'd like**
   
   => NOT operator used in WHERE clause including edge
   
   In opencypher document([LINK](https://s3.amazonaws.com/artifacts.opencypher.org/openCypher9.pdf)) , there is good reference. 
   (p.89, Filter on patterns using NOT)
   ```
   MATCH (person)
   WHERE NOT (person)-[acted_in]->(movie) 
   RETURN person.name
   ```
   
   **Describe alternatives you've considered**
   In AgensGraph, which I frequently use, the following alternative queries are suggested as the ones that work, but may not work in other graph databases like AGE.
   
   1. We can consider 'except' clause in AGE.
   ```
   # AgensGraph(Work? Y) / AGE (Work? N) 
   
   SELECT COUNT(*)
   FROM (
   MATCH (v1:person)
   RETURN v1.name
   EXCEPT
   MATCH (v1:person)-[e:acted_in]->(v2:movie) 
   RETURN v1.name
   ) t ;
   ```
   
   
   
   ```
   # AgensGraph
   
   SELECT COUNT(*)
   FROM (
   MATCH (v1:person)
   RETURN v1.name
   EXCEPT
   MATCH (v1:person)-[e:acted_in]->(v2:movie) 
   RETURN v1.name
   ) t ;
   
    count
   -------
       31
   (1 row)
   ```
   ```
   # AGE
   
   SELECT * FROM cypher ('movie_graph', $$ 
   MATCH (v1:person)
   RETURN v1.name
   EXCEPT
   MATCH (v1:person)-[e:acted_in]->(v2:movie)
   RETURN v1.name
   $$) AS (name agtype ) ;
   
   ERROR:  syntax error at or near "EXCEPT"
   LINE 3: EXCEPT
           ^
   ```
   
   2. Since it is based on an RDBMS, considering hybrid queries that combine both relational and graph aspects can also be an option to explore.
   ```
   # AgensGraph(Work? Y) / AGE (Work? N) 
   
   MATCH(a:person) 
   WHERE NOT ( a.name in ( SELECT t.* FROM ( MATCH (v1:person)-[e:acted_in]->(v2:movie) return v1.name ) t ) )
   RETURN COUNT(a.name) ;
   ```
   
   ```
   # AgensGraph
   
   MATCH(a:person) 
   WHERE not ( a.name in ( SELECT t.* FROM ( MATCH (v1:person)-[e:acted_in]->(v2:movie) return v1.name ) t ) )
   RETURN count(a.name) ;
   
    count
   -------
    31
   (1 row)
   
   ```
   ```
   # AGE
   
   SELECT * FROM cypher ('movie_graph', $$
   MATCH(a:person)
   WHERE not ( a.name in ( SELECT t.* FROM ( MATCH (v1:person)-[e:acted_in]->(v2:movie) return v1.name ) t ) )
   RETURN count(a.name) 
   $$) AS (cnt agtype)
   
   ERROR:  syntax error at or near "t"
   LINE 3: WHERE not ( a.name in ( SELECT t.* FROM ( MATCH (v1:person)-...
                                          ^
   ```
   
   **Additional context**
   Add any other context or screenshots about the feature request here.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscribe@age.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org