cypher multiple optional matches with where conditions

Question

I have a three objects in database: Person, Country and Car, that can be created with

create (:Car { name: 'car-987' })
create (:Car { name: 'car-876' })
create (:Country { name: 'country-123' })
create (:Country { name: 'country-234' })
create (:Country { name: 'country-345' })
match (cnt:Country { name: 'country-123' }), (cr:Car { name: 'car-987' }) create (cr)<-[:HAS_CAR]-(:Person { name: 'person-abc' })-[:LIVES_IN]->(cnt)
match (cnt:Country { name: 'country-234' }) create (:Person { name: 'person-bcd' })-[:LIVES_IN]->(cnt)
match (cr:Car { name: 'car-876' }) create (cr)<-[:HAS_CAR]-(:Person { name: 'person-cde' })

I'm selecting Person objects with optional country and car information

match (prs:Person)
optional match (prs)-[:LIVES_IN]->(cnt:Country)
optional match (prs)-[:HAS_CAR]->(cr:Car)
return id(prs) as id, prs.name as person, cnt.name as country, cr.name as car
order by person asc

The result is:

╒════╤════════════╤═════════════╤═════════╕
│"id"│"person"    │"country"    │"car"    │
╞════╪════════════╪═════════════╪═════════╡
│62  │"person-abc"│"country-123"│"car-987"│
├────┼────────────┼─────────────┼─────────┤
│63  │"person-bcd"│"country-234"│null     │
├────┼────────────┼─────────────┼─────────┤
│64  │"person-cde"│null         │"car-876"│
└────┴────────────┴─────────────┴─────────┘

The problems starts if i'm trying to use some conditions. For example i need to get only records where country.name contains '4' OR car.name contains '6'. With such conditions i expect to get:

╒════╤════════════╤═════════════╤═════════╕
│"id"│"person"    │"country"    │"car"    │
╞════╪════════════╪═════════════╪═════════╡
│63  │"person-bcd"│"country-234"│null     │
├────┼────────────┼─────────────┼─────────┤
│64  │"person-cde"│null         │"car-876"│
└────┴────────────┴─────────────┴─────────┘

How can i achieve it? If i'm trying to use WHERE inside OPTIONAL MATCH

match (prs:Person)
optional match (prs)-[:LIVES_IN]->(cnt:Country) where cnt.name contains '4'
optional match (prs)-[:HAS_CAR]->(cr:Car) where cr.name contains '6'
return id(prs) as id, prs.name as person, cnt.name as country, cr.name as car
order by person asc

getting not expected results:

╒════╤════════════╤═════════════╤═════════╕
│"id"│"person"    │"country"    │"car"    │
╞════╪════════════╪═════════════╪═════════╡
│62  │"person-abc"│null         │null     │
├────┼────────────┼─────────────┼─────────┤
│63  │"person-bcd"│"country-234"│null     │
├────┼────────────┼─────────────┼─────────┤
│64  │"person-cde"│null         │"car-876"│
└────┴────────────┴─────────────┴─────────┘

Was also thinking to use something like

match (prs:Person),
(prs)-[:LIVES_IN*0..1]->(cnt:Country),
(prs)-[:HAS_CAR*0..1]->(cr:Car)
where cnt.name contains '4' or cr.name contains '6'
return id(prs) as id, prs.name as person, cnt.name as country, cr.name as car
order by person asc

but this one don't return any records.

InverseFalcon InverseFalcon · Accepted Answer · 2017-05-13T19:45:30

You're close, try keeping your OPTIONAL MATCHes, but use a WITH along with a WHERE clause to enforce the rest of your filtering:

match (prs:Person)
optional match (prs)-[:LIVES_IN]->(cnt:Country)
optional match (prs)-[:HAS_CAR]->(cr:Car) 
with prs, cnt, cr
where cnt.name contains '4' or cr.name contains '6'
return id(prs) as id, prs.name as person, cnt.name as country, cr.name as car
order by person asc

cypher multiple optional matches with where conditions

1 Answers