Cypher query don't return relations when finding multiple records

Question

I'm writing a query that searches for recipes. I want to return the current users interactions (likes etc) with the recipes that matches the query.

This query returns all the interactions for the current user properly:

MATCH (recipe)<-[reaction:REACTS]-(beholder:User {cuid: 'some-id'})
RETURN reaction

However when I add the match to my existing search query, the reaction variable is null for every record:

MATCH (recipe:Recipe)
OPTIONAL MATCH (recipe)-[a:CONTAINS]->(i:Ingredient)
OPTIONAL MATCH (recipe)-[:IS]->(c:Category)
OPTIONAL MATCH (recipe)<-[:AUTHORED]-(u:User)
OPTIONAL MATCH (recipe)<-[reaction:REACTS]-(beholder:User {cuid: 'some-id'})
WHERE ALL(
  ingredient IN ['tomato', 'banana']
  WHERE (recipe)-[:CONTAINS]->(:Ingredient {name: ingredient})
)
AND ALL(
  category IN ['smoothie']
  WHERE (recipe)-[:IS]->(:Category {name: category})
)
RETURN DISTINCT recipe,
{username: u.username, cuid: u.cuid} AS author,
{love: reaction.love, favorite: reaction.favourite} AS interactions,
collect( DISTINCT {name: i.name, amount: a.amount}) AS ingredients,
collect( DISTINCT {name: c.name}) AS categories

The query I use for getting a single recipe by id works as it should though:

MATCH (recipe:Recipe {cuid: {recipeCuid}})
OPTIONAL MATCH (recipe)-[a:CONTAINS]->(i:Ingredient)
OPTIONAL MATCH (recipe)-[:IS]->(c:Category)
OPTIONAL MATCH (recipe)<-[:AUTHORED]-(u:User)
OPTIONAL MATCH (recipe)<-[reaction:REACTS]-(beholder:User {cuid: {beholderCuid}})
RETURN recipe,
{username: u.username, cuid: u.cuid} AS author,
{love: reaction.love, favorite: reaction.favorite} AS interactions,
collect( DISTINCT {name: i.name, amount: a.amount}) AS ingredients,
collect( DISTINCT {name: c.name}) AS categories

Any pointers towards what I'm doing wrong?

InverseFalcon · Accepted Answer

Gabor's query is a good step forward, as it moves your WHERE to after the WITH instead of keeping it with the OPTIONAL MATCH...that was the primary reason for your inability to get the correct results.

However, the query still needs some efficiency improvements. For one, multiple MATCHes or OPTIONAL MATCHes in a row, especially those that will return multiple rows (ingredients, categories), will impact the efficiency of the rest of your MATCHes or OPTIONAL MATCHes if you don't run your aggregations immediately.

For example, for a single recipe with 3 ingredients and 2 categories, that's 3 x 2 = 6 rows emitted by the time your first two OPTIONAL MATCHes are done, meaning the rest of your OPTIONAL MATCHes need to execute across all 6 of those rows, but your intent is for them only to execute once per recipe, not multiple times.

That's why aggregating as soon as possible is useful, as it can reduce the number of rows per recipe to one, instead of multiple (recipe with a single ingredient and a single category, for each combination of recipe and ingredient and category).

Additionally, you're only filtering down (based on ingredients and categories) only after you've matched on all the rest, meaning you're running many OPTIONAL MATCHes upon rows that will definitely be filtered out. That's wasted work and wasted db hits. Better to do your filtering as soon as possible, and THEN run the additional OPTIONAL MATCHes you need on rows that you know you will be returning.

Lastly, since you only seem to want rows back for recipes with certain ingredients and only certain categories, we should use a MATCH to ingredients and categories, not OPTIONAL MATCHes.

I'd recommend something like this for an improved query:

MATCH (cat:Category) 
WHERE cat.name IN ['smoothie']
WITH COLLECT(cat) as desiredCategories
MATCH (i:Ingredient)
WHERE i.name IN ['tomato', 'banana']
WITH desiredCategories, COLLECT(i) as desiredIngredients
MATCH (recipe:Recipe)
WHERE ALL(
  category IN desiredCategories
  WHERE (recipe)-[:IS]->(category)
) 
AND ALL(
  ingredient IN desiredIngredients
  WHERE (recipe)-[:CONTAINS]->(ingredient)
)
WITH recipe
MATCH (recipe)-[:IS]->(c:Category)
WITH recipe, COLLECT(c) as categories
MATCH (recipe)-[a:CONTAINS]->(i:Ingredient)
WITH recipe, categories, COLLECT({name: i.name, amount: a.amount}) as ingredients
OPTIONAL MATCH (recipe)<-[:AUTHORED]-(u:User)
// only one author, so okay to use optional matches back to back
OPTIONAL MATCH (recipe)<-[reaction:REACTS]-(beholder:User {cuid: 'some-id'})
RETURN recipe,
  {username: u.username, cuid: u.cuid} AS author,
  {love: reaction.love, favorite: reaction.favourite} AS interactions,
  ingredients,
  categories

You should be able to see that by running our COLLECTS() right after we do the matches returning multiple rows, we keep the built-up rows to 1 per recipe along with the collections (a collection is a single row, vs multiple rows when uncollected).

You should also be able to see that since we filter out recipes that don't have the desired categories or ingredients early, the OPTIONAL MATCHes at the end for author and reaction will only run for recipes that have the desired categories and ingredients, instead of running uselessly for recipes that will be filtered out later.

EDIT

I noticed, late, that there's a problem with the way you're checking for categories and ingredients, you can't compare the nodes directly with the names in your arrays. You likely have a name property on :Ingredient and :Category nodes, and we should be using that to match on the necessary ingredients and categories, and then filter the recipes near the start so we're only working with the recipes with those categories and ingredients. That also lets us avoid matching and collecting categories and ingredients until we've done the filtering. I've updated the query accordingly.

Cypher query don't return relations when finding multiple records

Answers (2)

Related Questions

Cypher query don&#39;t return relations when finding multiple records

Answers (2)

Related Questions

Cypher query don't return relations when finding multiple records