cancel
Showing results for 
Search instead for 
Did you mean: 

Converting to subquery call loses results

When I convert part of a query into a subquery that I want to call multiple times, the previously successful results now come up empty. What am I overlooking that causes the query to fail after that conversion?

When this code runs:

    // /* find sentences */
match (s1:Sentence) where s1.sentence = $sentenceText

// /* Find sentences' words */
match (s1)-[r1:HAS_WORD]-(w1:Word) where w1.word =~ '.*[a-z].*' and w1.stopword=false
with s1,w1,s1 as s,w1 as w order by w1.count

// /* **************************************************************** */
// /* Down-select to the most important (least common) words */
// /* **************************************************************** */
with s,w
with s,reduce( words = [], n in collect(w) | words + [n]) as words, sum(w.count) as total
with s,words,total,reduce( f =[0,{}], n in words |
case when 1.0* f[0]/total < 0.10 then
case when f[0]=0 then [f[0] + n.count,[n]]
else [f[0] + n.count,f[1]+[n]]
end
else f
end) as frac
with s,words,total,reverse(tail(reverse(frac[1]))) as important

// /* Make list of words assigned probability score of 100% */

unwind important as w
with distinct [w,1.0] as importantScore,s,words,total,important
return distinct s,collect(importantScore) as list,words,total,important

I correctly get

╒══════════════════════════════════════════════════════════════════════╤══════════════════════════════════════════════════════════════════════╤══════════════════════════════════════════════════════════════════════╤═══════╤══════════════════════════════════════════════════════════════════════╕
│"s"                                                                   │"list"                                                                │"words"                                                               │"total"│"important"                                                           │
╞══════════════════════════════════════════════════════════════════════╪══════════════════════════════════════════════════════════════════════╪══════════════════════════════════════════════════════════════════════╪═══════╪══════════════════════════════════════════════════════════════════════╡
│{"sentence":"This is a test of the end of the line ... / or not!","lev│[[{"count":1,"stopword":false,"word":"this"},1.0],[{"count":2,"stopwor│[{"count":1,"stopword":false,"word":"this"},{"count":2,"stopword":fals│538    │[{"count":1,"stopword":false,"word":"this"},{"count":2,"stopword":fals│
│el":"L0","count":10,"topic":"NONE","source":"console","idInSrc":""}   │d":false,"word":"of"},1.0],[{"count":2,"stopword":false,"word":"or"},1│e,"word":"of"},{"count":2,"stopword":false,"word":"or"},{"count":2,"st│       │e,"word":"of"},{"count":2,"stopword":false,"word":"or"},{"count":2,"st│
│                                                                      │.0],[{"count":2,"stopword":false,"word":"the"},1.0],[{"count":2,"stopw│opword":false,"word":"the"},{"count":2,"stopword":false,"word":"not"},│       │opword":false,"word":"the"},{"count":2,"stopword":false,"word":"not"},│
│                                                                      │ord":false,"word":"not"},1.0],[{"count":2,"stopword":false,"word":"is"│{"count":2,"stopword":false,"word":"is"},{"count":2,"stopword":false,"│       │{"count":2,"stopword":false,"word":"is"},{"count":2,"stopword":false,"│
│                                                                      │},1.0],[{"count":2,"stopword":false,"word":"a"},1.0],[{"count":36,"sto│word":"a"},{"count":36,"stopword":false,"word":"line"},{"count":56,"st│       │word":"a"},{"count":36,"stopword":false,"word":"line"}]               │
│                                                                      │pword":false,"word":"line"},1.0]]                                     │opword":false,"word":"end"},{"count":433,"stopword":false,"word":"test│       │                                                                      │
│                                                                      │                                                                      │"}]                                                                   │       │                                                                      │
└──────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────────────┴───────┴──────────────────────────────────────────────────────────────────────┘

But when implemented as a subquery that I want to reuse:

// /* find sentences */
match (s1:Sentence) where s1.sentence = $sentenceText

// /* Find sentences' words */
match (s1)-[r1:HAS_WORD]-(w1:Word) where w1.word =~ '.*[a-z].*' and w1.stopword=false
with s1,w1,s1 as s,w1 as w order by w1.count

// /* **************************************************************** */
// /* Down-select to the most important (least common) words */
// /* **************************************************************** */
CALL {
with s,w
with s,reduce( words = [], n in collect(w) | words + [n]) as words, sum(w.count) as total
with s,words,total,reduce( f =[0,{}], n in words |
case when 1.0* f[0]/total < 0.10 then
case when f[0]=0 then [f[0] + n.count,[n]]
else [f[0] + n.count,f[1]+[n]]
end
else f
end) as frac
with s,words,total,reverse(tail(reverse(frac[1]))) as important

// /* Make list of words assigned probability score of 100% */
unwind important as w
with distinct [w,1.0] as importantScore,s,words,total,important
return distinct collect(importantScore) as list,words,total,important
}
// /* **************************************************************** */
with s1,important as words1,list as list1,total as total1

return s1,words1,list1,total1

I get:

(no changes, no records)

4 REPLIES 4

glilienfield
Ninja
Ninja

You need to add a return statement. The subquery return just returns its columns to the outer query as additional columns. You need to specify and outer query return

The outer query does have a return:

// /* **************************************************************** */
with s1,important as words1,list as list1,total as total1

return s1,words1,list1,total1

My mistake. I thought the red text was commented out. I agree it should work. You do a lot of renaming variables without any need to do so. You can try the following, as it should represent the same code as without the call subquery.

// /* find sentences */
match (s1:Sentence) where s1.sentence = $sentenceText

// /* Find sentences' words */
match (s1)-[r1:HAS_WORD]-(w1:Word) where w1.word =~ '.*[a-z].*' and w1.stopword=false
with s1,w1,s1 as s,w1 as w order by w1.count

// /* **************************************************************** */
// /* Down-select to the most important (least common) words           */
// /* **************************************************************** */
CALL {
with s,w
with s,reduce( words = [], n in collect(w) | words + [n]) as words, sum(w.count) as total 
with s,words,total,reduce( f =[0,{}], n in words | 
case when 1.0* f[0]/total < 0.10 then 
case when f[0]=0 then [f[0] + n.count,[n]] 
else [f[0] + n.count,f[1]+[n]] 
end 
else f 
end) as frac 
with s,words,total,reverse(tail(reverse(frac[1]))) as important 

// /* Make list of words assigned probability score of 100% */
unwind important as w
with distinct [w,1.0] as importantScore,s,words,total,important
return collect(importantScore) as list,words,total,important
}

return distinct s,list,words,total,important


You can also just try 'return *' to see what you get.

ameyasoft
Graph Maven
Variable 'S' is missing in the return statement under CALL.

Try this:
CALL {
with s,w
with s,reduce( words = [], n in collect(w) | words + [n]) as words, sum(w.count) as total 
with s,words,total,reduce( f =[0,{}], n in words | 
case when 1.0* f[0]/total < 0.10 then 
case when f[0]=0 then [f[0] + n.count,[n]] 
else [f[0] + n.count,f[1]+[n]] 
end 
else f 
end) as frac 
with s,words,total,reverse(tail(reverse(frac[1]))) as important 

// /* Make list of words assigned probability score of 100% */
unwind important as w
with distinct [w,1.0] as importantScore,s,words,total,important
return distinct s, collect(importantScore) as list,words,total,important
}
return s as s1, important as words1, list as list1, total as total1