FOREACH with incremental property value

Hello Everyone,

I watched a tutorial on the FOREACH clause.

What I would like to create is a loop where I create a node for each month with a property named Value with a numerical property. It is a timetree

So MATCH (m:MONTH {Month : "January"}
FOREACH ( i IN m| CREATE (:DAY {Value: Value + 1}) )

I don't know the syntax, but you can see what I am trying to achieve. The idea is that I create days for each month with a loop, so for January it would be 31 Day nodes with each one having Value with the number of the month. I need to tell it how many times to loop. So for February it will be 28.

Kind regards,
Tideon

Mark Needham has an older blog entry on creating a timetree with Cypher that is still applicable.

For the days part, the last FOREACH loop will be the most important for you, using a CASE statement to select the correct day range (handling leap years I believe) depending on the month being handled.

Hello Andew,

I saw that blog post, and being a beginner, I couldn't make heads or tales from it. It was some sort of nested highend solution. I'm really a newbie to this, so I wanted a small step forward that I could manually do just to create 31 nodes in a loop.

I haven't read anything about case tey, just learned about FOREACH

Kind regards,
Tideon

The CASE expression allows the evaluation of a value, and depending on that value it allows us to output a different expression.

In the case of the query in Mark's blog, we were using the numerical value for the month to drive what day range to use.

The range function is a list function that will output a list of values starting with the provided starting value, and ending at the provided ending value. So range(1,3) gives us [1,2,3], and range(1,31) gives a list range starting at 1, and going through 31.

For the month of January there are always 31 days, so if we know we're only processing January, and we want to create the :Day nodes for January and connect them, we could do this with a single FOREACH:

MATCH (m:Month {month:1, name:"January"})
FOREACH ( i IN range(1,31) | 
  CREATE (d:Day {day: i})
  CREATE (m)-[:HAS_DAY]->(d) )

But if we want to process every month, we have to iterate over them, needing another FOREACH, and since days are variable per month we need a CASE statement for the range of days to create in the inner FOREACH:

WITH range(1,12) as months
FOREACH(month IN months | 
    CREATE (m:Month {month: month})
    FOREACH(day IN (CASE 
                      WHEN month IN [1,3,5,7,8,10,12] THEN range(1,31) // these months have 31 days
                      WHEN month = 2 THEN range(1,28) // not working with years, so no leapyear calc, default Feb has 28 days
                      ELSE range(1,30) // all other months have 30 days
                    END) |      
      CREATE (d:Day {day: day})
      MERGE (m)-[:HAS_DAY]->(d)))

This is similar to what's on Mark's blog, but since we're not working with years, we don't have enough info to calculate leapyear/leapday for February. Marks' query does deal with specific years, so we can calculation leapyear/leapday, which used a nested CASE.

1 Like

Hello Andrew,

Really thank you for explaining it like that. I bought the graph database book from o'reilly.
And after reading the timetree in there I went online to search, then found that blog, then learned how FOREACH works, then how ranges work, then how variable path length works. Then I realized I was going down a rabbit hole, so I came here for help. I was being pulled out with the tide.

I started a few days ago trying to do the simple thing you just typed out in a matter of minutes. It makes me laugh.

Really thank you.

Now this gives me a better foot hold to understand the rest, because you solved my particular problem and gave me insight how I can expand the rest.

Kind regards,
Tideon

Glad to help!

I would guess that the mental block here was you were looking for how to do some kind of increment in the FOREACH, the +1.

As Cypher is not a procedural language, we can't really do that, we have to use the list itself to drive the iteration, making range() very useful.

Cypher is a lot to take in, and there are some tradeoffs when it comes to it. Some things are much easier to express, other things require some alternate approaches, and some things can be clumsy even if you know the tricks.

Happy to help if you happen to have any additional questions.

1 Like

Hello Adrew,

It is funny that you mentioned the +1 thing, as I am learning Python also and was looking for a sort similar solve, that is what had me stuck. I could not find anything that allowed me to increment the i value.

I really like cypher, t is straight forward and I like that. I come from SQL and all it's craziness. So this is a real relief. I want to master Cypher before I start to mess with APOC. I used it to load a few JSON files, and loved it, but realized that I needed to get the foundation first in place before APOC.

I ran into one problem, I can't figure this out. As far as I can see the syntax is fine, but it give me the following error on the following code.

match (y:YEAR {Value:2004})-[:MONTH]->(m:MONTH{Name:"Febuary"})
FOREACH (i IN RANGE (1,29)|
CREATE (d:DAY {Value:i})
CREATE (m)-[:DAY]->(d))
return y,m,d

Error:
Variable d not defined (line 5, column 12 (offset: 158))
"return y,m,d"
^

Sidenote;
Without the d variable at the return, it creates the nodes and returns them perfectly. So that makes me a bit confused.

Hello Adrew,

I am guessing it has something to do with the variable in the FOREACH being some sort of local variable in the loop as it is in Python, and cannot be seen outside of the loop.

Kind regards,
Tideon

That's exactly it. FOREACH is meant to support write-only operations (which is a reason why you'd get an error if you used any non-writing clause in there, like MATCH, WITH, etc), and any variables created within the FOREACH are not exposed in scope once it ends.

FOREACH doesn't change the cardinality, so the rows/records that were present when hitting the FOREACH will be the same on output, no additional variables, no additional rows.

Hello Adrew,

Very insightful. Where do you learn all this type of stuff. As I read the manual on the NEO4J site and don't see this type of nuance information. The manual often isn't helping me. MArk's blog explains things better and then I also wonder where did he get that from.

Is there a way return the newly created nodes in the one cypher execution?

I was trying the following

match (y:YEAR {Value:2004})-[:MONTH]->(m:MONTH{Name:"May"})
FOREACH (i IN RANGE (1,31)|
CREATE (d:DAY {Value:i})
CREATE (m)-[:DAY]->(d))

return y,m [(m)-->(d) WHERE d:DAY | d.Value ] AS day

Here is the error:
Invalid input 'W': expected whitespace, comment, a relationship pattern, '.', node labels, '[', '^', '*', '/', '%', '+', '-', "=~", IN, STARTS, ENDS, CONTAINS, IS, '=', '~', "<>", "!=", '<', '>', "<=", ">=", AND, XOR, OR, ']' or ".." (line 6, column 23 (offset: 170))
"return y,m [(m)-->(d) WHERE d:DAY|d.Value ] AS day"

Kind regards,
Tideon

Hello Adrew,

UPDATE!!!

This code execute with no error, but didn't show the new nodes. I was missing a comma after the "m".

match (y:YEAR {Value:2004})-[:MONTH]->(m:MONTH{Name:"May"})
FOREACH (i IN RANGE (1,31)|
CREATE (d:DAY {Value:i})
CREATE (m)-[:DAY]->(d))

return y,m,[(m)-->(d) WHERE d:DAY | d.Value ] AS day

Kind regards,
Tideon

It just hit me what you meant with the cardinality. And this is what I mean with that extra knowledge.

Do you have the (y:YEAR {Value:2004}), (m:MONTH{Name:"May"}) and [:MONTH]-> exists?

If not since the first MATCH does not return any data, nothing below gets executed.

Try the query

match (y:YEAR {Value:2004})-[:MONTH]->(m:MONTH{Name:"May"}) return *

to see what is returned. If nothing gets returned, then the query is working correctly and not doing any work.

Hello Anthapu,

Yes the all the years, months and links all exist. From 2004 - 2020. It is only the days that I am now creating.

Sidenote: I made a few replies behind each other where I asked about where this extra knowledge / nuance came from and as to where can I find this stuff. It is a few post back. Something like three or four post behind each other.

Here is a screen grab of the nodes by the way:

Kind regards,
Tideon

Can you please execute the query and post the results?

match (y:YEAR {Value:2004})-[:MONTH]->(m:MONTH{Name:"May"}) return *

FOREACH does not have any issue and is working as it is supposed to do. When I ran it, it created all the DAY nodes appropriately.

One of the easiest ways to do this is to use UNWIND vs. FOREACH - ala:

// create years
UNWIND range(1950,2025) as r
CREATE (cy:CalendarYear) SET cy.year=r;

// create months
UNWIND range(1,12) as monthNum
MATCH (cy:CalendarYear)
CREATE (cm:CalendarMonth) set cm.year=cy.year, cm.month=monthNum
MERGE (cm)-[:IS_MONTH_IN]->(cy)

You can then build the rest of the time tree (dates) by using something like:

//WITH date("1950-01-01") AS startDate, date("1959-12-31") AS endDate
WITH date("1960-01-01") AS startDate, date("1989-12-31") AS endDate
WITH startDate, duration.inDays(startDate, endDate).days AS days
WITH [day in range(0, days) | startDate + duration({days: day})] AS dates
UNWIND dates as curDate
WITH curDate, curDate+duration({days: -1}) as prevDate
CREATE (cd:CalendarDate {date: date(curDate) })
MERGE (pd:CalendarDate {date: date(prevDate) })
MERGE (pd)-[:NEXT_DATE]->(cd)
WITH cd, cd.date.month as curMon, cd.date.quarter as qtr, cd.date.year as curYear
MATCH (cm:CalendarMonth) WHERE cm.month=curMon AND cm.year=curYear
MATCH (cy:CalendarYear) WHERE cy.year=curYear
MERGE (cd)-[:IS_DATE_IN]->(cm)
MERGE (cq:CalendarQuarter {quarter: qtr, year: curYear})
MERGE (cm)-[:IS_MONTH_IN]->(cq)
MERGE (cq)-[:IS_QUARTER_IN]->(cy)
WITH cd, cd.date.week as curWeek, CASE when cd.date.week > cd.date.month then cd.date.year-1 else cd.date.year end as week_yr
MATCH (wy:CalendarYear) WHERE wy.year=week_yr
MERGE (cw:CalendarWeek {week: curWeek, year: week_yr})
MERGE (cd)-[:IS_DAY_IN]->(cw)
MERGE (cw)-[:IS_WEEK_IN]->(wy)

....using ranges of dates to avoid txns that might be too large for whatever reason you choose.

Thank you Jeffrey for the response.

Being new to Neo4J, this will take me a moment to grasp and apply in my own workflow.

Kind regards,
Tideon

Hello Anthapu,

This query execute fine.

Here is a screen grab of the code you asked me to execute. One thing, YEAR had to be Year for the label, and MONTH had to be Month for the label.

Greetings,
Tideon