Retrieving Specific Characters with Specific Items in Database on Apache AGE

Question

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:

1. 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.

2. 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?

Answer 1

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.

Answer 2

I am not sure if your question says: the output is a single item matched and I want the other things>>> If so then I believe you refer to the COLLECT function to aggregate the result into a single row.

MATCH (c:Character)-[:VISITED]->(l:Location)
WITH c, count(DISTINCT l) as visit_count
MATCH (c)-[:HAS_ITEM]->(i:Item)
WHERE i.Rarity >= 4
WITH c, visit_count, COLLECT(i.name) as rare_items
WHERE visit_count >= 3
RETURN c.name, rare_items, visit_count
ORDER BY visit_count DESC, c.name ASC

Answer 3

A combination of cyber queries, aggregation functions and pattern matching can be used to achieve the result that you desire. Here's one way to solve this problem:

MATCH (c:Character)-[:VISITED]->(l:Location)
WITH c, count(DISTINCT l) as visit_count
WHERE visit_count >= 3
MATCH (c)-[:HAS_ITEM]->(i:Item)
WHERE i.Rarity >= 4
WITH c, visit_count, i
ORDER BY c.Name ASC, i.Name ASC
RETURN c.Name, COLLECT(DISTINCT i.Name) as RareItems, visit_count
ORDER BY visit_count DESC, c.Name ASC