Independent matches in cypher query

Question

I have a Neo4j database with User, Content, and Topic nodes. I want to calculate the proportion of content consumed by a given user for a given topic.

MATCH (u:User)-[:CONSUMED]->(c:Content)<-[:CONTAINS]-(t:Topic)
WHERE ID(u) = 11158 AND ID(t) = 19853
MATCH (c1:Content)<-[:CONTAINS]-(z)
RETURN toFloat(COUNT(DISTINCT(c))) / toFloat(COUNT(DISTINCT(c1)))

Two things strike me as really ugly here:

Firstly, is COUNT(DISTINCT()) a hack to get round the fact that the two MATCH queries cross-join?
Float division is ugly.

The second is something I can live with, but the first seems inefficient; is there a better way to express this idea?

Dave Bennett Dave Bennett · Accepted Answer · 2017-03-01T17:02:30

The count of content should return the number of pieces of content a user consumed unless of course they consumed the same content more than once.

Instead of matching all of the content from the topic, if your model permits, you could just get the size of the outbound CONTAINS relationships.

MATCH (u:User)-[:CONSUMED]->(c:Content)<-[:CONTAINS]-(t:Topic)
WHERE ID(u) = 11158 AND ID(t) = 19853
RETURN toFloat(count(distinct c))/ size((t)-[:CONTAINS]->()) as proportion

Your original query returns a cartesian product of the number of user-content-topic matches x the number of topic-content matches. As an alternative to the above, you could re-write your original query something like this. This gets the content that is consumed by a user for the topic, does the aggregation and then passes the topic and resulting count to the next clause in the query. This will work, however, using size((t)-[:CONTAINS]->()) will be more effiecient.

MATCH (u:User)-[:CONSUMED]->(c:Content)<-[:CONTAINS]-(t:Topic)
WHERE ID(u) = 11158 AND ID(t) = 19853
WITH t, count(distinct c ) as distinct_content
MATCH (t)-[:CONTAINS]->(c1:Content)
RETURN toFloat(distinct_content) / count(c1)

Independent matches in cypher query

1 Answers