You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@age.apache.org by "Munmud (via GitHub)" <gi...@apache.org> on 2023/05/29 02:13:15 UTC
[GitHub] [age] Munmud opened a new issue, #951: How to use WHERE caluse after a (WITH .. AS ...)
Munmud opened a new issue, #951:
URL: https://github.com/apache/age/issues/951
How to use WHERE caluse after a (WITH .. AS ...)
The following query fails whenever I add WHERE caluse inside it
```sql
SELECT * from cypher('munmud', $$
MATCH (u:User {id: 'user1'})-[:WATCHED]->(m:Movie)
WITH DISTINCT m as watchedMovies
MATCH (g:Genre)<-[]-(watchedMovies)
WITH DISTINCT g as genre
MATCH (genre)<-[:BELONGS_TO]-(am)
WITH DISTINCT am as allMovie
WHERE (u:User {id: 'user1'})-[:WATCHED]->(allMovie)
RETURN allMovie
$$) as (V agtype);
```
### Result
```
Syntax error at or near ":"
```
### Nodes Added as follow
```sql
SELECT * from cypher('munmud', $$
CREATE
(:User {id: 'user1'}),
(:User {id: 'user2'}),
(:Movie {id: 'movie1', name: 'The Shawshank Redemption'}),
(:Movie {id: 'movie2', name: 'The Godfather'}),
(:Movie {id: 'movie3', name: 'The Dark Knight'}),
(:Movie {id: 'movie4', name: 'Army of Thieves'}),
(:Movie {id: 'movie5', name: 'Oceans Thirteen'}),
(:Movie {id: 'movie6', name: 'Heat'}),
(:Genre {name: 'Drama'}),
(:Genre {name: 'Crime'}),
(:Genre {name: 'Action'})
$$) as (V agtype);
```
### Edges added as follow
```sql
SELECT * from cypher('munmud', $$
MATCH
(u1:User {id: 'user1'}),
(u2:User {id: 'user2'}),
(m1:Movie {id: 'movie1'}),
(m2:Movie {id: 'movie2'}),
(m3:Movie {id: 'movie3'}),
(m4:Movie {id: 'movie4'}),
(m5:Movie {id: 'movie5'}),
(m6:Movie {id: 'movie6'}),
(gD:Genre {name: 'Drama'}),
(gC:Genre {name: 'Crime'}),
(gA:Genre {name: 'Action'})
CREATE
(u1)-[:WATCHED]->(m1),
(u1)-[:WATCHED]->(m2),
(u2)-[:WATCHED]->(m1),
(m1)-[:BELONGS_TO]->(gD),
(m2)-[:BELONGS_TO]->(gC),
(m3)-[:BELONGS_TO]->(gA),
(m4)-[:BELONGS_TO]->(gD),
(m5)-[:BELONGS_TO]->(gC),
(m6)-[:BELONGS_TO]->(gA)
$$) as (V agtype);
```
--
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
[GitHub] [age] jrgemignani commented on issue #951: How to use WHERE caluse after a (WITH .. AS ...)
Posted by "jrgemignani (via GitHub)" <gi...@apache.org>.
jrgemignani commented on issue #951:
URL: https://github.com/apache/age/issues/951#issuecomment-1568702667
https://neo4j.com/docs/cypher-manual/current/clauses/where/
--
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
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] Munmud closed issue #951: How to use WHERE caluse after a (WITH .. AS ...)
Posted by "Munmud (via GitHub)" <gi...@apache.org>.
Munmud closed issue #951: How to use WHERE caluse after a (WITH .. AS ...)
URL: https://github.com/apache/age/issues/951
--
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
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] Munmud closed issue #951: How to use WHERE caluse after a (WITH .. AS ...)
Posted by "Munmud (via GitHub)" <gi...@apache.org>.
Munmud closed issue #951: How to use WHERE caluse after a (WITH .. AS ...)
URL: https://github.com/apache/age/issues/951
--
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
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] WendelLana commented on issue #951: How to use WHERE caluse after a (WITH .. AS ...)
Posted by "WendelLana (via GitHub)" <gi...@apache.org>.
WendelLana commented on issue #951:
URL: https://github.com/apache/age/issues/951#issuecomment-1567203145
In your particular case, you don't need to use a WHERE clause, it's simply replaced with a MATCH clause, like this:
```
SELECT * from cypher('munmud', $$
MATCH (u:User {id: 'user1'})-[:WATCHED]->(m:Movie)
WITH DISTINCT m as watchedMovies
MATCH (g:Genre)<-[]-(watchedMovies)
WITH DISTINCT g as genre
MATCH (genre)<-[:BELONGS_TO]-(am)
WITH DISTINCT am as allMovie
MATCH (u:User {id: 'user1'})-[:WATCHED]->(allMovie)
RETURN allMovie
$$) as (V agtype);
```
However, I didn't get why you need all this MATCH clauses if your query would return the same result with a simple query like this:
```
SELECT * from cypher('munmud', $$
MATCH (u:User {id: 'user1'})-[:WATCHED]->(m:Movie)
RETURN m
$$) as (V agtype);
```
If you want to get all movies genres watched by a particular person than return all movies that another person watched from those same genres using a WHERE clause, you could do this:
```
SELECT * from cypher('munmud', $$
MATCH (u:User {id: 'user1'})-[:WATCHED]->(m:Movie)
WITH DISTINCT m as watchedMovies
MATCH (g:Genre)<-[]-(watchedMovies)
WITH DISTINCT g as genre
MATCH (genre)<-[:BELONGS_TO]-(am)
WITH DISTINCT am as allMovies
MATCH (u:User)-[:WATCHED]->(allMovies)
WITH u as user, allMovies as movie
WHERE user.id = 'user2'
RETURN movie
$$) as (V agtype);
```
--
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
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] Munmud commented on issue #951: How to use WHERE caluse after a (WITH .. AS ...)
Posted by "Munmud (via GitHub)" <gi...@apache.org>.
Munmud commented on issue #951:
URL: https://github.com/apache/age/issues/951#issuecomment-1569363675
> The problem in your query is due to the use of where clause directly with the WITH clause.
That is what I was talking about.. What about `Where` caluse in a `With` clause
--
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
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] humzakt commented on issue #951: How to use WHERE caluse after a (WITH .. AS ...)
Posted by "humzakt (via GitHub)" <gi...@apache.org>.
humzakt commented on issue #951:
URL: https://github.com/apache/age/issues/951#issuecomment-1570667896
Try this updated query:
```
SELECT * from cypher('munmud', $$
MATCH (u:User {id: 'user1'})-[:WATCHED]->(m:Movie)
WITH DISTINCT m as watchedMovies
MATCH (g:Genre)<-[]-(watchedMovies)
WITH DISTINCT g as genre
MATCH (genre)<-[:BELONGS_TO]-(am)
WHERE EXISTS ((u)-[:WATCHED]->(am))
RETURN DISTINCT am
$$) as (V agtype);
```
--
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
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] abdulmanann commented on issue #951: How to use WHERE caluse after a (WITH .. AS ...)
Posted by "abdulmanann (via GitHub)" <gi...@apache.org>.
abdulmanann commented on issue #951:
URL: https://github.com/apache/age/issues/951#issuecomment-1570655680
The issue you encountered in your query is due to the incorrect placement of the WHERE clause. In the given query, the WHERE clause is used directly after the WITH clause, which is not allowed in this context.
To resolve this issue, you need to make a slight adjustment to your query. Instead of using the WHERE clause directly after the WITH clause, you can incorporate the filtering condition into the preceding MATCH clause.
--
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
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] dukeofhazardz commented on issue #951: How to use WHERE caluse after a (WITH .. AS ...)
Posted by "dukeofhazardz (via GitHub)" <gi...@apache.org>.
dukeofhazardz commented on issue #951:
URL: https://github.com/apache/age/issues/951#issuecomment-1566968030
It would seem that the problematic part is the attempt to use a relationship pattern `(u:User {id: 'user1'})-[:WATCHED]->(allMovie)` directly within the WHERE clause. which could be as a result of a slight variations in the AGE syntax. In order to fix this you should avoid using the relationship pattern directly within the WHERE clause. Instead, add an additional MATCH clause `(u)-[:WATCHED]->(am)` to achieve the desired filtering as I did below.
```
demo=# SELECT * FROM cypher('munmud', $$
MATCH (u:User {id: 'user1'})-[:WATCHED]->(m:Movie)
WITH DISTINCT m AS watchedMovies
MATCH (g:Genre)<-[]-(watchedMovies)
WITH DISTINCT g AS genre, watchedMovies
MATCH (genre)<-[:BELONGS_TO]-(am)
MATCH (u)-[:WATCHED]->(am)
RETURN am
$$) AS (V agtype);
v
------------------------------------------------------------------------------------------------------------------------
{"id": 1125899906842625, "label": "Movie", "properties": {"id": "movie1", "name": "The Shawshank Redemption"}}::vertex
{"id": 1125899906842625, "label": "Movie", "properties": {"id": "movie1", "name": "The Shawshank Redemption"}}::vertex
{"id": 1125899906842626, "label": "Movie", "properties": {"id": "movie2", "name": "The Godfather"}}::vertex
(3 rows)
```
By using an additional MATCH clause, you can specify the relationship pattern separately and include it in the WITH and RETURN clauses to achieve the desired result.
--
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
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] kenwoon commented on issue #951: How to use WHERE caluse after a (WITH .. AS ...)
Posted by "kenwoon (via GitHub)" <gi...@apache.org>.
kenwoon commented on issue #951:
URL: https://github.com/apache/age/issues/951#issuecomment-1566612988
It seems like AGE does not yet support filtering on patterns with properties. According to the [Neo4j documentation](https://neo4j.com/docs/cypher-manual/current/clauses/where/#filter-on-patterns-with-properties) for the `WHERE` clause, properties can also be added as patterns, as such:
```
MATCH (n:Person)
WHERE (n)-[:KNOWS]-({name: 'Timothy'})
RETURN n.name, n.age
```
However, this returns the following error in AGE:
```
ERROR: syntax error at or near ":"
LINE 3: WHERE (n)-[:KNOWS]-({name: 'Timothy'})
^
```
You would have to find a different way to set the filter by using a different clause or changing the contents for your `WHERE` clause. Refer to the documentation earlier for a guide on using the clause.
--
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
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] farooquememon385 commented on issue #951: How to use WHERE caluse after a (WITH .. AS ...)
Posted by "farooquememon385 (via GitHub)" <gi...@apache.org>.
farooquememon385 commented on issue #951:
URL: https://github.com/apache/age/issues/951#issuecomment-1567269339
The syntax error is happening because the WHERE clause is trying to match a user with a movie. The WHERE clause can only be used to filter the results of a MATCH clause. In this case, the WHERE clause should be applied to the results of the first MATCH clause.
--
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
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] Munmud commented on issue #951: How to use WHERE caluse after a (WITH .. AS ...)
Posted by "Munmud (via GitHub)" <gi...@apache.org>.
Munmud commented on issue #951:
URL: https://github.com/apache/age/issues/951#issuecomment-1567682070
> It would seem that the problematic part is the attempt to use a relationship pattern `(u:User {id: 'user1'})-[:WATCHED]->(allMovie)` directly within the WHERE clause. which could be as a result of a slight variations in the AGE syntax. In order to fix this you should avoid using the relationship pattern directly within the WHERE clause. Instead, add an additional MATCH clause `(u)-[:WATCHED]->(am)` to achieve the desired filtering as I did below.
>
> ```
> demo=# SELECT * FROM cypher('munmud', $$
> MATCH (u:User {id: 'user1'})-[:WATCHED]->(m:Movie)
> WITH DISTINCT m AS watchedMovies
> MATCH (g:Genre)<-[]-(watchedMovies)
> WITH DISTINCT g AS genre, watchedMovies
> MATCH (genre)<-[:BELONGS_TO]-(am)
> MATCH (u)-[:WATCHED]->(am)
> RETURN am
> $$) AS (V agtype);
> v
> ------------------------------------------------------------------------------------------------------------------------
> {"id": 1125899906842625, "label": "Movie", "properties": {"id": "movie1", "name": "The Shawshank Redemption"}}::vertex
> {"id": 1125899906842625, "label": "Movie", "properties": {"id": "movie1", "name": "The Shawshank Redemption"}}::vertex
> {"id": 1125899906842626, "label": "Movie", "properties": {"id": "movie2", "name": "The Godfather"}}::vertex
> (3 rows)
> ```
>
> By using an additional MATCH clause, you can specify the relationship pattern separately and include it in the WITH and RETURN clauses to achieve the desired result.
What if I use `WHERE NOT`
```
SELECT * from cypher('munmud', $$
MATCH (u:User {id: 'user1'})-[:WATCHED]->(m:Movie)
WITH DISTINCT m as watchedMovies
MATCH (g:Genre)<-[]-(watchedMovies)
WITH DISTINCT g as genre
MATCH (genre)<-[:BELONGS_TO]-(am)
WITH DISTINCT am as allMovie
WHERE NOT (u:User {id: 'user1'})-[:WATCHED]->(allMovie)
RETURN allMovie
$$) as (V agtype);
```
--
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
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [age] RahimullahShaheen commented on issue #951: How to use WHERE caluse after a (WITH .. AS ...)
Posted by "RahimullahShaheen (via GitHub)" <gi...@apache.org>.
RahimullahShaheen commented on issue #951:
URL: https://github.com/apache/age/issues/951#issuecomment-1567942453
The problem in your query is due to the use of where clause directly with the WITH clause.
Here is another approach which you can use to get the desired result you want.
```
SELECT * from cypher('munmud', $$
MATCH (u:User {id: 'user1'})-[:WATCHED]->(m:Movie)
RETURN m
$$) as (V agtype);
```
I have tried it and it gives the correct result as follow:
```
v
------------------------------------------------------------------------------------------------------------------------
{"id": 1125899906842625, "label": "Movie", "properties": {"id": "movie1", "name": "The Shawshank Redemption"}}::vertex
{"id": 1125899906842626, "label": "Movie", "properties": {"id": "movie2", "name": "The Godfather"}}::vertex
(2 rows)
```
--
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
For queries about this service, please contact Infrastructure at:
users@infra.apache.org