CODEWITHSUNDEEP
neo4jcypher
monjohn
monjohn

Reputation: 65

Neo4j/ Cypher: sort results based on a sorted collection

Here is the start of my query. 

MATCH (person:Person)<-[:NAMES]-(name:PersonName)-[:COMES_FROM]->(source:Source)
WITH DISTINCT person, COLLECT([name, source]) AS tuples
WITH person, REDUCE(result=[], tuple in tuples | 
    ...  ) AS personName
ORDER BY personName.lastName
RETURN person

Here's the rub: each person has a number of names, each coming from 1 of 5 sources. But not all sources are created equal. I want to get the name that comes from best source, if that is not available, then the next best, etc. E.g. ["source.best", "source.very-good", "source.ok", "source.worst"] I want to do something like a custom sort on the personName collection, with the sources as the predicate, and return the first one. It would be easier if the sources were were numbers or something.

Any suggestions?

Upvotes: 3

Views: 778

Answers (2)

Christophe Willemsen
Christophe Willemsen

Reputation: 20185

This was very interesting. I came up with this idea : score every source and return the best one, the score will be the position of its value in a list of all sources rankings.

Assumption : the best, very-good are defined on the value property of the Source node :

WITH ["awesome","very-good","worst","best"] as ranking
MATCH (person:Person)<-[:NAMES]-(name:PersonName)-[:COMES_FROM]->(source:Source)
WITH person, name, source, 
 reduce(z = 0, x IN range(0, size(ranking)-1) | 
        z + CASE source.value WHEN ranking[x] THEN x ELSE 0 END) as score
ORDER BY person.name, score ASC
RETURN person, collect([name, source])[0] as bestTuple

Note : The WITH in the beginning is a trick for simulating that you pass this as parameters normally.

I used this simple graph to test :

CREATE (p:Person {name:"John"})<-[:NAMES]-(n:PersonName {name:"Jo"})-[:COMES_FROM]->(source:Source {v:"awesome"}),
(p)<-[:NAMES]-(n2:PersonName {name:"Jonathan"})-[:COMES_FROM]->(s:Source {v:"very-good"}),
(x:Person {name:"Xavier"})<-[:NAMES]-(n3:PersonName {name:"Xav"})-[:COMES_FROM]->(s)

Upvotes: 0

Michael Hunger
Michael Hunger

Reputation: 41706

[√] don't use distinct and aggregation together

You can use a literal map as mapping for your sorting, i.e. mapping strings to values. Then you just grab the first (head) of each of those names, when you aggregate per person.

WITH {`source.best`:1, `source.very-good`:2, `source.ok`:3, `source.worst:4} as sourceSort
MATCH (person:Person)<-[:NAMES]-(name:PersonName)-[:COMES_FROM]->(source:Source)
WITH person, name ORDER BY sourceSort[source.name] 
RETURN person, HEAD(COLLECT(name.lastName)) AS name
ORDER BY name

Upvotes: 4

Related Questions