How to agregate lowest price nodes 3 nodes down the chain

Neo4j Graph Platform Cypher
1

Hi community, this is my first post to see if anybody can give me direction since I am completely stuck.
Here is the challenge:

I have hotel nodes linked to room nodes which are linked to day prices.
MATCH (ac1:Accommodation-[:ROOM]->(ro1:Room)-[:NIGHTS]->(ni1:PriceRange)

There are 1000 hotels, each hotel carries +/- 10 rooms and each room carries 360 prices
I need to agregate the cheapest room per hotel AND I need the roomID of the cheapest room.

I get a perfect result by leaving out the room and asking min(price). As soon as I add the room, it (logically) expands and showing all rooms and prices in stead of cheapest.

I tried a lot of things and ended up with a sub query, but that also doesn't work.

MATCH (ac1:Accomodation{hotelID:"DALABELOS"})
WITH ac1.hotelID as hotelID
CALL {
WITH hotelID
UNWIND [3..21] as night
MATCH (ac1:Accomodation {hotelID:hotelID})-[:ROOM]->
(ro1:Room)-[:NIGHTS{night:night}]->(ni1:PriceRange)
WITH hotelID as hotelID, PriceRange as minPrice ORDER BY minPrice LIMIT 1
}
RETURN hotelID,roomID,minPrice

How can I build a match that shows the cheapest room and price per hotel?

Thanks in advance helping me making the next step!

Onno

2

MATCH (a:Accomodation)-[:ROOM]->(r:Room)-[n:NIGHTS]->(p:PriceRange)
WHERE a.hotelID = 'DALABELOS' AND
n.night IN range(3, 21)
WITH a.hotelID AS hotelID, apoc.agg.minItems(r.roomID,p.price) AS minData
RETURN hotelID, minData.items[0] AS roomID, minData.value AS minPrice

1 Like
3

Wow Simon, this is great! It does not only give me the wanted result, but it did improve the processing time a lot. I didn't know this function and it is a smart approach to take the first one since this also sorts. Thanks big time!

2 Likes
4

Note that for the larger query, doing this across all hotels, you are on the right track with subquery usage, just combine that with the apoc.agg.minItems():

MATCH (ac:Accomodation)
CALL {
  WITH ac
  MATCH (ac)-[:ROOM]->
(r:Room)-[n:NIGHTS]->(p:PriceRange)
  WHERE n.night IN range(3, 21)
  RETURN apoc.agg.minItems(r.roomID, p.price) as minData
}
RETURN ac.hotelID as hotelID, minData.items[0] AS roomID, minData.value AS minPrice

That further constrains the aggregation to only run per individual accommodation, instead of having to aggregate across the whole data set (so the function will be called 1000 times, once per hotel, instead of once across all hotels). The results should be identical with either approach.

1 Like
5

Hi Andrew,
You are right, with so many accommodations, I will need to loop. To be honest even this loop is failing because of too many memory consumed.
Onno