CODEWITHSUNDEEP
neo4jcypher
ASX
ASX

Reputation: 1725

filtering a filtered a Cypher query result

Hi here's my current query i'd like to 're-filter':

START movie = node(*)
MATCH user-[:LIKE]->category-[:SIMILAR*0..3]-()<-[:TAGGED]->movie
WHERE user.name = "current_user"

WITH DISTINCT movie, user, category

RETURN user.name, category.name, ID(movie), movie.name
ORDER BY movie.name;

http://console.neo4j.org/r/u19iim

Here's how it looks like after current query:

+--------------+----------------+-----------+-------------------------+
| user.name    | category.name  | ID(movie) | movie.name              |
+--------------+----------------+-----------+-------------------------+
| current_user | c              | 14        | movie_c_and_d_and_e     |
| current_user | d              | 14        | movie_c_and_d_and_e     |
| current_user | e              | 14        | movie_c_and_d_and_e     |
| current_user | a              | 9         | movie_of_a_and_b_and_b1 |
| current_user | b              | 9         | movie_of_a_and_b_and_b1 |
| current_user | b              | 10        | movie_of_b2_first       |
| current_user | b              | 11        | movie_of_b2_second      |
| current_user | c              | 12        | movie_of_c              |
| current_user | d              | 13        | movie_of_d_and_e        |
| current_user | e              | 13        | movie_of_d_and_e        |
+--------------+----------------+-----------+-------------------------+

I'd like to GROUP BY COUNT(sugg) AS category_count to extract this:

+--------------+----------------+-----------+-------------------------+
| user.name    | category_count | ID(movie) | movie.name              |
+--------------+----------------+-----------+-------------------------+
| current_user | 3              | 14        | movie_c_and_d_and_e     |
| current_user | 2              | 9         | movie_of_a_and_b_and_b1 |
| current_user | 2              | 13        | movie_of_d_and_e        |
| current_user | 1              | 10        | movie_of_b2_first       |
| current_user | 1              | 11        | movie_of_b2_second      |
| current_user | 1              | 12        | movie_of_c              |
+--------------+----------------+-----------+-------------------------+

How can I accomplish this?

Similar questions: - how to have two aggregation in cypher query in neo4j?

Update
Here's the working result (with demo: http://tinyurl.com/cywlycc):

START movie = node(*)
MATCH user-[:LIKE]->category-[:SIMILAR*0..3]-()<-[:TAGGED]->movie
WHERE user.name = "current_user"
WITH DISTINCT movie, category WITH COUNT(movie) AS category_count, movie, collect(category.name) as categorized
RETURN category_count, ID(movie), movie.name, categorized
ORDER BY category_count DESC;

Upvotes: 1

Views: 615

Answers (1)

Werner Kvalem Vester&#229;s
Werner Kvalem Vester&#229;s

Reputation: 10346

START movie = node(*) 
MATCH user-[:LIKE]->category-[:SIMILAR*0..3]-()<-[:TAGGED]->movie 
WHERE user.name = "current_user" 
WITH DISTINCT movie, user, category 
RETURN user.name, count(category.name) as category_count, ID(movie), movie.name 
ORDER BY category_count desc, movie.name asc

http://console.neo4j.org/r/69rfkn

Upvotes: 2

Related Questions