cypher distinct is returning duplicate using with parameter

Question

MATCH (c:someNode)   WHERE  LOWER(c.erpId) contains (LOWER("1")) 
OR  LOWER(c.constructionYear) contains (LOWER("1")) 
OR  LOWER(c.label) contains (LOWER("1"))
OR  LOWER(c.name) contains (LOWER("1")) 
OR  LOWER(c.description) contains (LOWER("1"))with collect(distinct c) as rows, count(c) as total 
    MATCH (c:someNode)-[adtype:OFFICIAL_someNode_ADDRESS]->(ad:anotherObject) 

WHERE toString(ad.streetAddress) contains "1"     
OR toString(ad.postalCity) contains "1" 
with distinct rows+collect( c) as rows, count(c) +total as total
 UNWIND rows AS part

RETURN part order by part.name SKIP 20 Limit 20

When I run the following cypher query it returns duplicate results. Also it the skip does not seem to work. What am I doing worng

cybersam · Accepted Answer · 2018-03-23T00:02:10.117

1

When you use WITH DISTINCT a, b, c (or RETURN DISTINCT a, b, c), that just means that you want each resulting record ({a: ..., b: ..., c: ...}) to be distinct -- it does not affect in any way the contents of any lists that may be part of a, b, or c.

Below is a simplified query that might work for you. It does not use the LOWER() and TOSTRING() functions at all, as they appear to be superfluous. It also only uses a single MATCH/WHERE pair to find all the the nodes of interest. The pattern comprehension syntax is used as part of the WHERE clause to get a non-empty list of true value(s) iff there are any anotherObject node(s) of interest. Notice that DISTINCT is not needed.

MATCH (c:someNode)
WHERE
  ANY(
    x IN [c.erpId, c.constructionYear, c.label, c.name, c.description]
    WHERE x CONTAINS "1") OR
  [(c)-[:OFFICIAL_someNode_ADDRESS]->(ad:anotherObject)
    WHERE ad.streetAddress CONTAINS "1" OR ad.postalCity CONTAINS "1"
    | true][0]
RETURN c AS part
ORDER BY part.name SKIP 20 LIMIT 20;

edited Mar 23 '18 at 00:02

answered Mar 22 '18 at 19:50

cybersam

63,203
6
53
76

to lower was added to make the query case-insensitive – Abdullah Al Noman Mar 23 '18 at 09:08
1

Lowercasing "1" does not change its encoding. – cybersam Mar 23 '18 at 09:32
yes the values does not necessary means one it can be any string value since I am generating this query based on some requirements dynamically – Abdullah Al Noman Mar 23 '18 at 09:45
You should pass the comparison value as a parameter, and keep the query itself unchanged. That is faster and more secure. Also, you should pass the lowercase value so that your query does not constantly use the lowercase function for it. – cybersam Mar 23 '18 at 15:52

cypher distinct is returning duplicate using with parameter

1 Answers1