I'm currently working with a database that represents an RPG game. It contains nodes for characters, items, and locations. The relationships between these nodes are represented as 'HAS_ITEM' (between characters and items) and 'VISITED' (between characters and locations).
Items are categorized by a 'Rarity' property which is a value between 1 (common) and 5 (legendary). Similarly, locations are quantified by a 'Visit_Count' property, indicating how often they've been visited by any character.
Here's an excerpt of the database:
**Characters.csv:**
Character_Id,Name
1,John
2,Mary
3,Bob
4,Alice
**Items.csv**:
Item_Id,Name,Rarity
1,Sword of Valor,5
2,Shield of Hope,4
3,Wand of Sorcery,3
4,Bow of Speed,4
5,Dagger of Stealth,2
**Locations.csv**
Location_Id,Name,Visit_Count
1,Desert of Sands,15
2,Forest of Mysteries,8
3,Castle of Shadows,25
4,Mountain of Trials,10
5,Sea of Silence,5
**HAS_ITEM.csv**
Character_Id,Item_Id
1,1
2,4
3,2
3,3
4,2
4,5
**VISITED.csv**
Character_Id,Location_Id
1,1
1,2
2,3
2,4
2,5
3,1
3,2
3,3
3,4
4,1
Here's where I need some help:
I want to filter and retrieve all characters who have a 'HAS_ITEM' relationship with any item of rarity 4 or above. These characters should also have a 'VISITED' relationship with at least 3 different locations. For each of these characters, I want to display their name, the name(s) of the rare item(s) they own, and the total count of unique locations they've visited.
The results should be sorted by the total number of locations visited in descending order. If multiple characters have visited the same number of locations, then they should be further sorted by their name in ascending alphabetical order.
I tried this:
MATCH (c:Character)-[:VISITED]->(l:Location)
WITH c, count(DISTINCT l) as visit_count
MATCH (c)-[:HAS_ITEM]->(i:Item)
WHERE i.Rarity >= 4 AND visit_count >= 3
RETURN c.name, i.name, visit_count
ORDER BY visit_count DESC, c.name ASC
but it returns just one item for each character:
c.name | i.name | visit_count
-------------------------------------------
Bob | Shield of Hope | 4
Mary | Bow of Speed | 3
I wanted all the items that a character has that matched the criteria.
Can anyone help me construct a Cypher query for this task?
You should group the names of items for each character using a function like collect(). So, to retrieve all the items that a character has, you can modify the query as follows:
MATCH (c:Character)-[:VISITED]->(l:Location)
WITH c, count(DISTINCT l) as visit_count
MATCH (c)-[:HAS_ITEM]->(i:Item)
WHERE i.Rarity >= 4 AND visit_count >= 3
RETURN c.name, collect(i.name) as items, visit_count
ORDER BY visit_count DESC, c.name ASC
By doing so, the query will return all the items a character has rather than just one item.