CODEWITHSUNDEEP
neo4jcypher
brooklyn718
brooklyn718

Reputation: 13

Sorting by Elements in Collection in Cypher Query

I'm working on an application using Neo4J and I'm having problems with the sorting in some of the queries. I have a list of stores that have promotions so I have a node for each store and a node for each promotion. Each store can have multiple promotions so it's a one to many relationship. Some of the promotions are featured (featured property = true) so I want those to appear first. I'm trying to construct a query that does the following:

So far I have the following:

MATCH (p:Promotion)-[r:BELONGS_TO_STORE]->(s:Store) WITH p, s, collect(p.featured) as featuredCol WITH p, s, LENGTH(FILTER(i IN featuredCol where i='true')) as featuredCount ORDER BY p.featured DESC, featuredCount DESC RETURN s, collect(p) skip 0 limit 10

First, I try to create a collection using the featured property using a WITH clause. Then, I try to create a second collection where the featured property is equal to true and then get the length in a second WITH clause. This sorts the collection with the promotions correctly but not the stores. I get an error if I try to add another sort at the end like this because the featuredCount variable is not in the RETURN clause. I don't want the featuredCount variable in the RETURN clause because it throws my pagination off.

Here is my second query:

MATCH (p:Promotion)-[r:BELONGS_TO_STORE]->(s:Store) WITH p, s, collect(p.featured) as featuredCol WITH p, s, LENGTH(FILTER(i IN featuredCol where i='true')) as featuredCount ORDER BY p.featured DESC, featuredCount DESC RETURN s, collect(p) ORDER BY featuredCount skip 0 limit 10

I'm very new to Neo4J so any help will be greatly appreciated.

Upvotes: 1

Views: 1577

Answers (1)

cybersam
cybersam

Reputation: 66947

Does this query (see this console) work for you?

MATCH (p:Promotion)-[r:BELONGS_TO_STORE]->(s:Store)
WITH p, s
ORDER BY p.featured DESC 
WITH s, COLLECT(p) AS pColl
WITH s, pColl, REDUCE(n = 0, p IN pColl | CASE
                      WHEN p.featured
                      THEN n + 1
                      ELSE n END ) AS featuredCount
ORDER BY featuredCount DESC 
RETURN s, pColl
LIMIT 10

This query performs the following steps:

  • It orders the matched rows so that the rows with featured promotions are first.
  • It aggregates all the p nodes for each distinct s into a pColl collection. The featured promotions still appear first within each pColl.
  • It calculates the number of featured promotions in each pColl, and orders the stores so that the ones with the most features promotions appear first.
  • It then returns the results.

Note: This query assumes that featured has a boolean value, not a string. (FYI: ORDER BY considers true to be greater than false). If this assumption is not correct, you can change the WHEN clause to WHEN p.featured = 'true'.

Upvotes: 3

Related Questions