CODEWITHSUNDEEP
neo4jcypher
Vishal G
Vishal G

Reputation: 1531

Neo4j: How to return deep node data

Dataset 

CREATE
  (u1:User {number: 1}), (u2:User {number: 2}),
  (r1:Room {name: 'r1'}), (r2:Room {name: 'r2'}),
  (d1:UnavailableDate {date: '1/1/2016'}), (d2:UnavailableDate {date: '1/2/2016'}),
  (i1:Image {url: 'http://..'}), (i2:Image {url: 'http://..'}),(i3:Image {url: 'http://..'}),
  (pA:Place {name: 'P'}),
  (u1)<-[:house_mate]-(pA)-[:owner_of]->(u2),
  (pA)<-[:place]-(r1),
  (pA)<-[:place]-(r2),
  (r1)<-[:room]-(d1),
  (r1)<-[:room]-(d2),
  (r2)<-[:room]-(i1),
  (r2)<-[:room]-(i2),
  (r1)<-[:room]-(i3)

Following is my query

MATCH (place:`Place` {name: 'P'}),
      (place)-[:place]-(room:Room)
OPTIONAL MATCH (place)-[tenant:owner_of|house_mate]-(u:User)
OPTIONAL MATCH (room)-[:room]-(date:UnavailableDate)
OPTIONAL MATCH (room)-[:room]-(image:Image)
WITH DISTINCT place,
     collect(room) AS r,
     collect(image) AS images,
     collect(date) AS dates,
     type(tenant) AS type,
     u
WITH place,
     collect({type: type, u: u}) AS tenants,
     collect({rooms: r, images: images, dates: dates}) AS rooms
RETURN DISTINCT place,
       rooms,
       [tenant IN tenants WHERE tenant.type = 'owner_of'   | [tenant.u]][0] AS owner_array,
       [tenant IN tenants WHERE tenant.type = 'house_mate' | [tenant.u]] AS house_mates_array

Result enter image description here Here I am trying to figure out to get

  1. place should be distinct in result
  2. every room should have its unavailable dates & images as separate Array in room result
  3. owner & housemate array looks good should be as it is

problem is collect for images & dates should be on room not on place

Any help?

Upvotes: 1

Views: 548

Answers (2)

InverseFalcon
InverseFalcon

Reputation: 30397

The duplicates are coming from the back-to-back OPTIONAL MATCHes. You're getting cross products for all combinations of u, date, and image. To avoid this, you need to collect immediately after each one. Or, even better, use pattern comprehension to get the result into a collection right away, and use map projection in the final collection to add the dates and images into each associated room:

MATCH (place:`Place` {name: 'P'})
WITH place, [(place)-[:owner_of]-(u:User) | u] as owner_array, [(place)-[:house_mate]-(u:User) | u] as house_mates_array
MATCH (p)-[:place]-(room:Room)
WITH place, owner_array, house_mates_array, room, [(room)-[:room]-(date:UnavailableDate) | date] as dates, [(room)-[:room]-(image:Image) | image] as images
RETURN place, collect(room {.*, dates, images}) as rooms, owner_array, house_mates_array

Upvotes: 3

cybersam
cybersam

Reputation: 66999

This may be close to what you want:

MATCH (place:`Place` {name: 'P'})-[:place]-(room:Room)
OPTIONAL MATCH (place)-[tenant:owner_of|house_mate]-(u:User)
WITH place, room, TYPE(tenant) AS type, u
OPTIONAL MATCH (room)-[:room]-(date:UnavailableDate)
OPTIONAL MATCH (room)-[:room]-(image:Image)
WITH place, room,
     collect(image) AS images,
     collect(date) AS dates,
     collect(DISTINCT {type: type, u: u}) AS tenants
RETURN place,
       collect({room: room, images: images, dates: dates}) AS room_data,
       [tenant IN tenants WHERE tenant.type = 'owner_of'   | [tenant.u]][0] AS owner_array,
       [tenant IN tenants WHERE tenant.type = 'house_mate' | [tenant.u]] AS house_mates_array;

Here are the results:

╒════════════╤══════════════════════════════════════════════════════════════════════╤══════════════╤═══════════════════╕
│"place"     │"room_data"                                                           │"owner_array" │"house_mates_array"│
╞════════════╪══════════════════════════════════════════════════════════════════════╪══════════════╪═══════════════════╡
│{"name":"P"}│[{"room":{"name":"r2"},"images":[{"url":"http://.."},{"url":"http://..│[{"number":2}]│[[{"number":1}]]   │
│            │"},{"url":"http://.."},{"url":"http://.."}],"dates":[]},{"room":{"name│              │                   │
│            │":"r1"},"images":[{"url":"http://.."},{"url":"http://.."},{"url":"http│              │                   │
│            │://.."},{"url":"http://.."}],"dates":[{"date":"1/1/2016"},{"date":"1/2│              │                   │
│            │/2016"},{"date":"1/1/2016"},{"date":"1/2/2016"}]}]                    │              │                   │
└────────────┴──────────────────────────────────────────────────────────────────────┴──────────────┴───────────────────┘

Upvotes: 1

Related Questions