0

I'm trying to use existing data to create a new relationship type. I'm trying the strategy in this question but I'm not getting the expected results.

This:

MATCH (user)-[r:POSTS_TO]->(thread)<-[s:POSTS_TO]-(other) 
MERGE (user)-[act:TALKS_TO]->(other)

works, creating unique relationships between users who have posted in the same forum threads, but there are no relationship properties set for TALKS_TO. I'd like to add the number of threads each user share to the relationship, with the aim to delete the underlying POSTS_TO relationships.

When I try this: 

MATCH (user:User)-[r:POSTS_TO]->(thread:Thread)<-[s:POSTS_TO]-(other:User) 
WITH thread, count(r.thread_id) as total_threads
MERGE (user)-[act:TALKS_TO]->(other)
ON CREATE SET act.total_threads = thread.total_threads

only two nodes (neither of which are user nodes) and one relationship are set, and no properties.

I have also tried 

MATCH (user:User)-[r:POSTS_TO]->(thread:Thread)<-[s:POSTS_TO]-(other:User) 
WITH thread, count(thread) as total_threads
MERGE (user)-[act:TALKS_TO]->(other)
ON CREATE SET act.total_threads = thread.total_threads

with the same results.

I can't see where I am going wrong in comparison with this question. Critiques & corrections please?

This is the full set of data that I'd like to set as a property for each TALKS_T0 relationship:

MATCH (user)-[r:POSTS_TO]->(thread)<-[s:POSTS_TO]-(other) 
WITH r, 
    min(r.event_time) as first_post, 
    max(r.event_time) as last_post, 
    count(distinct r.post_id) as total_posts, 
    count(distinct r.thread_id) as total_threads,
    count(distinct r.forum_id) as total_forums
MERGE (user)-[act:TALKS_TO]->(other)
ON CREATE SET act.first_post = first_post,
          act.last_post = last_post,
          act.total_posts = total_posts,
          act.total_threads = total_threads,
          act.total_forums = total_forums
Community
  • 1
  • 1
caitiewrites
  • 1
  • 2

2 Answers2

0

There were several issues with the first query you tried:

  1. You did not include user and other in the WITH clause, so the MERGE clause did not see them, and was forced to create new nodes instead.
  2. Your SET clause was using the value of thread.total_threads (which does not exist) instead of total_threads. Thus, the SET clause never created any properties.

This query fixes both problems:

MATCH (user:User)-[r:POSTS_TO]->(thread:Thread)<-[s:POSTS_TO]-(other:User)
WITH user, other, thread, count(r.thread_id) as total_threads
MERGE (user)-[act:TALKS_TO]->(other)
ON CREATE SET act.total_threads = total_threads;

COMMENT 1: The POSTS_TO relationship has a redundant thread_id property, since the relationship's Thread end node presumably has the same id value. COMMENT 2: If you get rid of the POSTS_TO relationships, then it looks like the Thread nodes will end up totally disconnected. You may need to think more carefully about what you are trying to accomplish.

cybersam
  • 63,203
  • 6
  • 53
  • 76
  • Thanks for your help! The purpose of this is to try and remove the `Thread` nodes and `POSTS_TO` relationships entirely and use the aggregated information to set properties about the `TALKS_TO` relationships, so that is what I would expect. I've tried the solution you suggested and it does set relationships for each person-to-person interaction, but now I'm finding that `total_threads` is always 1, which I would not expect. I'll keep tinkering. – caitiewrites May 15 '16 at 11:43
  • Update: after a lot of pondering, poking around and trying different things, I changed the structure of the data to include a `total_posts_count` as a property of the `POSTS_TO` relationship. Then when I created the `TALKS_TO` relationship, I could sum the `total_posts_count` to get a sense of relationship strength between each pair of users. I still can't figure out why the number of threads appears to be distinct, as in reality pairs of users may communicate together in multiple threads, but the important thing was to get the `TALKS_TO` relationship to supercede `POSTS_TO`. – caitiewrites May 15 '16 at 23:14
0

This is what ended up working correctly:

MATCH (user:User)-[r:POSTS_TO]->(thread:Thread)<-[s:POSTS_TO]-(other:User) 
WITH 
user, 
other, 
thread, 
r.first_post as first_posts,
r.last_post as last_posts,
r.total_posts as total_posts_to_thread
WITH 
user, 
other,
sum(total_posts_to_thread) as total_posts,
count(thread) as total_threads, 
min(first_posts) as first_post, 
max(last_posts) as last_post
MERGE (user)-[act:TALKS_TO]->(other)
ON CREATE SET act.first_post = first_post,
          act.last_post = last_post,
          act.total_posts = total_posts,
          act.total_threads = total_threads
caitiewrites
  • 1
  • 2