1

i have a problem with import from csv.

i'm running the following in the shell, and the last part (MERGE (e1)-[:NEXT]->(hit)))) never happens. little frustrating...

Each session has x hits. I want to find the last hit of inserted session, and connect it with new hit by a NEXT relation

PSV sample:

Session_id|date_time Xxx|2015-01-01T01:00:00 Xxx|2015-02-02T09:00:00 Yyy|2015-03-03T06:00:44

Code:

USING PERIODIC COMMIT 100
 LOAD CSV WITH HEADERS FROM 'file:///home/xxx.csv' AS line FIELDTERMINATOR '|'


 MERGE (session :Session { session_id:line.session_id })
 MERGE (hit:Hit{date:line.date_time})

//........ More merges......

//relations

CREATE (hit)-[:IN_SESSION]->(session) 
 CREATE ....//more relations

 WITH session

 MATCH (prev_hit:Hit)-[:IN_SESSION]->(session)
 WITH prev_hit ORDER BY prev_hit.date_time DESC LIMIT 2
 WITH collect(prev_hit) as entries

 FOREACH(i in RANGE(0, length(entries)-1) | 
   FOREACH(e1 in [entries[i]] | 
        MERGE (e1)-[:NEXT]->(hit)))
Lior Goldemberg
  • 866
  • 13
  • 26

1 Answers1

3

I don't see what you are trying to achieve with the nested FOREACH loops.

If you really get both the hit node and session node, a simple MERGE should be fine. I think you have to include hit in the WITH statement though.

MERGE (session :Session { id: "xxx" })
MERGE (hit:Hit { date_time:"2015-04-03T06:00:44" })
CREATE (hit)-[:IN_SESSION]->(session)
WITH session, hit
MATCH (prev_hit:Hit)-[:IN_SESSION]->(session)
WHERE prev_hit <> hit // make sure that you only match other hits
WITH hit, prev_hit 
ORDER BY prev_hit.date_time DESC LIMIT 1
MERGE (prev_hit)-[:NEXT]->(hit) // create relationship between the two

Update

I updated the query to only match prev_hit which are not the current hit. The query above works as you want, that is it creates a single NEXT relationship to a single Hit node related to the same Session. See here: http://console.neo4j.org/?id=ov7mer

There might be issues with the date_time. You store it as a String I think, sorting might not always give you the expected result.

Update 2

Regarding your second comment: If you go over your file line by line and add Hit nodes, you can only add relationships to Hit nodes which have been added already. If you want a continuous chain of NEXT relationships between Hit nodes you can only do this in one query if you make sure that the entries of your CSV file are ordered ascending by date_time.

You can add the NEXT relationships between the Hit nodes later as described here: http://www.markhneedham.com/blog/2014/04/19/neo4j-cypher-creating-relationships-between-a-collection-of-nodes-invalid-input/

Start your query with:

MATCH (s:Session)--(hit:Hit)
// first order by hit.date_time
WITH DISTINCT s, hit ORDER BY hit.date_time DESC
// this will return one row per session with the hits in a collection
WITH s, collect(hit) AS this_session_hits
// try this to check the ordering:
// RETURN s.session_id, this_session_hits

// the following queries will be done on each row, this is like iterating over the sessions
FOREACH(i in RANGE(0, length(this_session_hits)-2) | 
    FOREACH(e1 in [this_session_hits[i]] |
        FOREACH(e2 in [this_session_hits[i+1]] |
            MERGE (e1)-[:NEXT]->(e2))))

Final Answer ;)

This query works on the dataset in your neo4j console (http://console.neo4j.org/?id=mginka). It connects all Hit from a session with NEXT relationships.

MATCH (s:Session)<--(hit:Hit)
WITH DISTINCT s, hit
ORDER BY hit.date_time ASC 
WITH s, collect(hit) AS this_session_hits
FOREACH (i IN RANGE(0, length(this_session_hits)-2)| 
    FOREACH (e1 IN [this_session_hits[i]]| 
        FOREACH (e2 IN [this_session_hits[i+1]]| 
            MERGE (e1)-[:NEXT]->(e2))))
Martin Preusse
  • 9,151
  • 12
  • 48
  • 80
  • the following code work, but creates a relationship for all the hits - even from a different session. how can i iterate all sessions and then all the hits from the same session? **`WITH hit ORDER BY hit.date_time DESC
    WITH collect(hit) as entries
    FOREACH(i in RANGE(0, length(entries)-2) | FOREACH(e1 in [entries[i]] | FOREACH(e2 in [entries[i+1]] | MERGE (e1)-[:NEXT]->(e2))))`**     – Lior Goldemberg May 19 '15 at 17:02
  • thanks @Martin-preusse this creates a relation between the first and the last Hit (changed to Limit 1). i want to create a relation between the first hit to the second, between the scond to the third and so on. any help would be appreciated. – Lior Goldemberg May 19 '15 at 18:22
  • thanks a lot @Martin. the query looks perfect, but you have started it with {id='xxx'}. what i want is to take all sessions > iterate them, and then run the query with the session id of each iteration: all the hits of 'xxx'-> then all the hits of 'xxx1' and so on thanks again – Lior Goldemberg May 20 '15 at 15:52
  • Look at what `MATCH (s:Session)--(hit:Hit)` returns. You iterate over all sessions with their hits. – Martin Preusse May 21 '15 at 12:03
  • thanks a lot man, sorry for all the bother, but i've install neo4j last week the first time. can you do the extra mile, and put the full query (assume that the load had been done already, and that is a different script, so no need to add the firs relation between his and session - only between hits) – Lior Goldemberg May 21 '15 at 12:11
  • Full query. If you want more you have to prepare a test dataset on http://console.neo4j.org/ – Martin Preusse May 21 '15 at 16:02
  • thanks a lot. here is the sample [link](http://console.neo4j.org/?id=mginka) i want to connect all the hits of each session by the date_time field with [NEXT] relation – Lior Goldemberg May 25 '15 at 08:24
  • See the final answer, this query works on you console. – Martin Preusse May 26 '15 at 08:19