Full text search on nodes that have existing relationships efficiently

[tarendran.vivekanand (Tarendran Vivekananda)]

I want to search nodes that only have a specific relationship efficiently.
Example

CREATE (a:Asset {asset_name:'John', asset_id:'001'})
CREATE (as:Asset {asset_name:'John', asset_id:'002'})
CREATE (g:Group {group_name:'local_employee'})
MERGE (g)-[:GROUP]-(as)

call db.index.fulltext.createNodeIndex('search', ['Asset'], ['asset_name'], {analyzer:'standard-no-stop-words'})

call db.index.fulltext.queryNodes('search', 'john')

However this will return both nodes. I just want to return the one asset node that has the relationship.
I understand that you can just add a match statement at the bottom and then return the results. However this method is inefficient when the nodes I have to search through are in the tens of thousands.
Basically I require a more efficient method.

[cobra]

Hello @tarendran.vivekanand

Can you provide the unefficient Cypher query?

Regards,
Cobra

[tarendran.vivekanand (Tarendran Vivekananda)]

Hello @cobra . Sorry should have provided it.

WITH ("*" +$usersearchtext+ "*") as txt
WITH split(txt,' ') as stxt
WITH apoc.text.join(stxt, '* AND *') as jtxt
CALL db.index.fulltext.queryNodes('livesearch', jtxt)
YIELD node
MATCH (g:Group)-[:GROUP]->(a:Asset)-[:OWNS]->(d:Device)
WHERE a.asset_name = node.asset_name
RETURN (a)

[cobra]

First, I think you can remove all the WITH clauses and build your string directly in the CALL

Moreover you can do this:

CALL db.index.fulltext.queryNodes('search', 'john')
YIELD node WHERE exists((node)<-[:GROUP]-(:Group))
RETURN node

Regards,
Cobra

[tarendran.vivekanand (Tarendran Vivekananda)]

I tried doing what you said however it didnt work. It still returned all without filtering. Only if I put the match then it will filter.

[cobra]

Hello @tarendran.vivekanand

The cypher request I showed you works on my database

Which version of Neo4j are you using?

Regards,
Cobra

[tarendran.vivekanand (Tarendran Vivekananda)]

Hello @cobra :)
Im using the latest one Neo4j 4.0
When I use the match on the outside

CALL db.index.fulltext.queryNodes('livesearch', '*a*')
YIELD node
OPTIONAL MATCH (g:Group)-[:GROUPS]->(a:Asset)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(d:Device)
WHERE a.asset_id = node.asset_id
WITH DISTINCT collect(d{.sigfox_id,a}) as assetdata

When I use yours

CALL db.index.fulltext.queryNodes('livesearch', '*a*')
YIELD node WHERE EXISTS ((:Group)-[:GROUPS]->(:Asset)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(:Device))
return node

[cobra]

I'm using the version 4.0.4 of Neo4j, otherwise I don't know why it works on my database and not on yours

The WHERE clause checks if there is a relationship linked to the node or not.

[tarendran.vivekanand (Tarendran Vivekananda)]

I just edited my comment with my query and results

[cobra]

@tarendran.vivekanand can you try to return only the node id on my cypher request ?

[tarendran.vivekanand (Tarendran Vivekananda)]

Still doesnt give me the same result as my query. @cobra

[cobra]

Can you give me all the queries to build the same database as yours?

[tarendran.vivekanand (Tarendran Vivekananda)]

I put the queries I made in the attach .txt file
Query.txt (1.4 KB)

[cobra]

You must use the node return by the db.index.fulltext.queryNodes in the WHERE clause

CALL db.index.fulltext.queryNodes('livesearch', '*a*')
YIELD node WHERE EXISTS ((:Group)-[:GROUPS]->(node)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(:Device))
return node

[tarendran.vivekanand (Tarendran Vivekananda)]

Then what if I want to return all so the asset,group and device?

[cobra]

CALL db.index.fulltext.queryNodes('livesearch', '*a*')
YIELD node
WHERE EXISTS ((:Group)-[:GROUPS]->(node)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(:Device))
OR EXISTS ((node)-[:GROUPS]->(:Asset)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(:Device))
OR EXISTS ((:Group)-[:GROUPS]->(:Asset)<-[:ON]-(:Deploy)<-[:SCHEDULED]-(node))
RETURN node

[tarendran.vivekanand (Tarendran Vivekananda)]

@cobra