Making a contains query where I aggregate multiple properties

[nickhall122]

This should be an easy one for the community, but I have struggled with it for a decent number of hours

I am writing a query that searches the users that I am following in the database. The below query works if I am just searching for the name "Nick", but when I search for "Nick H" or "Nick Hall" it doesn't (which makes sense) because I'm not combining the firstName and lastName properties.

MATCH (currentUser:User { cognitoId: $cognitoId }) - [:FOLLOWING] -> (users:User)
WHERE users.firstName CONTAINS $textInput OR users.lastName CONTAINS $textInput OR users.email CONTAINS $textInput
RETURN users

I want to combine the properties for something that looks like "Nick Hall nick@gmail.com" so it will search on all the properties and not get hung up on the spaces. Unfortunately I get the error of adding

WHERE users.firstName + " " + users.lastName CONTAINS $textInput 

and this query forgets what users is after the with.

MATCH (currentUser:User { cognitoId: $cognitoId }) - [:FOLLOWING] -> (users:User)
WITH users.firstName + " " +  users.lastName + " " + users.email as name
WHERE name CONTAINS $textInput 
RETURN users

Thanks, you all are the bomb!

[dana_canzano]

suspect this may be a bug. Using the default :play movies in the browser if I run

match (n:Movie) return n.title, n.tagline,toString(n.title) + ' ' + toString('abcd')  limit 3;

this returns

╒════════════════════════╤════════════════════════════════════════════╤════════════════════════════════════════════╕
│"n.title"               │"n.tagline"                                 │"toString(n.title) + ' ' + toString('abcd')"│
╞════════════════════════╪════════════════════════════════════════════╪════════════════════════════════════════════╡
│"The Matrix"            │"Welcome to the Real World"                 │"The Matrix abcd"                           │
├────────────────────────┼────────────────────────────────────────────┼────────────────────────────────────────────┤
│"The Matrix Reloaded"   │"Free your mind"                            │"The Matrix Reloaded abcd"                  │
├────────────────────────┼────────────────────────────────────────────┼────────────────────────────────────────────┤
│"The Matrix Revolutions"│"Everything that has a beginning has an end"│"The Matrix Revolutions abcd"               │
└────────────────────────┴────────────────────────────────────────────┴────────────────────────────────────────────┘

and the last column is properly constructed.

but if I change my Cypher to

match (n:Movie) return n.title, n.tagline,toString(n.title) + ' ' + toString(n.tagline)  limit 3;

then the output is

╒════════════════════════╤════════════════════════════════════════════╤═══════════════════════════════════════════════════════════════════╕
│"n.title"               │"n.tagline"                                 │"toString(n.title) + ' ' + toString(n.tagline)"                    │
╞════════════════════════╪════════════════════════════════════════════╪═══════════════════════════════════════════════════════════════════╡
│"The Matrix"            │"Welcome to the Real World"                 │"The Matrix Welcome to the Real World"                             │
├────────────────────────┼────────────────────────────────────────────┼───────────────────────────────────────────────────────────────────┤
│"The Matrix Reloaded"   │"Free your mind"                            │"The Matrix Reloaded Free your mind"                               │
├────────────────────────┼────────────────────────────────────────────┼───────────────────────────────────────────────────────────────────┤
│"The Matrix Revolutions"│"Everything that has a beginning has an end"│"The Matrix Revolutions Everything that has a beginning has an end"│
└────────────────────────┴────────────────────────────────────────────┴───────────────────────────────────────────────────────────────────┘

and here you will see the last column is not concatenating the 2 properties

[brant_boehmann · Accepted solution]

Could could include users in your WITH clause so that it can also retain users:

MATCH (currentUser:User { cognitoId: $cognitoId }) - [:FOLLOWING] -> (users:User)
WITH users, users.firstName + " " +  users.lastName + " " + users.email as name
WHERE name CONTAINS $textInput 
RETURN users

[nickhall122]

brant.boehmann:

MATCH (currentUser:User { cognitoId: $cognitoId }) - [:FOLLOWING] -> (users:User) WITH users, users.firstName + " " + users.lastName + " " + users.email as name WHERE name CONTAINS $textInput RETURN users

Thanks Brant! I even was saying it out loud that I wasn't carrying the users match along with me haha.

[dana_canzano]

correction to my initial response above which indicated this appears to be a bug an can be demonstrated using the `:play movies' database. I overlooked the output and you can see that in fact the last column is indeed a concatenation of the 1st two properties. So at least the Cypher demonstrated in my initial response is correct.

And back to using the ':play movies' databases, the following Cypher will suffice

match (n:Movie) where (toString(n.title) + ' ' + toString(n.tagline)) contains 'Welcome' return id(n),n.title, n.tagline,toString(n.title) + ' ' + toString(n.tagline);

which returns

╒════════════╤═══════════════════════════╤═══════════════════════════════════════════════╕
│"n.title"   │"n.tagline"                │"toString(n.title) + ' ' + toString(n.tagline)"│
╞════════════╪═══════════════════════════╪═══════════════════════════════════════════════╡
│"The Matrix"│"Welcome to the Real World"│"The Matrix Welcome to the Real World"         │
└────────────┴───────────────────────────┴───────────────────────────────────────────────┘

[nickhall122]

Thanks Dana. Brant's was more along the lines of what I needed. I didn't want the "Nick Hall nick@gmail.com" to be in my return, I just wanted it to get aliased and be able to run a contains on it. That way I could still return the user nodes.

[brant_boehmann]

However, if you don't need/want to return the combined string or use it later in your query, then you don't really need to alias it or use WITH:

MATCH (currentUser:User { cognitoId: $cognitoId }) - [:FOLLOWING] -> (users:User) 
WHERE (users.firstName + ' '  + users.lastName + ' ' + users.email) CONTAINS $textInput 
RETURN users