Dealing with not existent properties and null values

[fsportelli0]

Hello everyone.
I have the same problem for some Cypher queries I'm implementing. I think that my approach is wrong, but I'm trying to understand how to solve the problem.
I have a problem with those queries with non existent properties or null values.
A simple example starting from a working query:

MATCH (entity:XmlTag)
WHERE entity._name="orm_EntityType"
RETURN DISTINCT entity.id, entity.Name, entity._ReferenceMode, entity.IsIndependent

This query is fine, it returns the exact number of rows.

If I modifiy the query as follows:

MATCH (entity:XmlTag)
MATCH (dataType:XmlTag)
WHERE entity._name="orm_EntityType" AND dataType._name="odt_dataType" AND dataType.name=entity._ReferenceMode
RETURN DISTINCT entity.id, entity.Name, entity._ReferenceMode, entity.IsIndependent, dataType.id

I only got a subset of the previous result, but I want to display all of them and in case the new condition is not satisfied (wheter dataType is null or not existent) I want to show the same rows as before, but with null values.
Here, instead of null, the expected rows are not showed.
I've tried to play with NULL, but nothing changed.
Probably my approach is not correct.
Could you please suggest a solution?
Thanks in advance.

[andrew_bowman]

You can use coalesce() to supply defaults when the property is null in your WHERE clause, allowing it to meet your criteria, or you can use OR entity._name IS NULL null checks in your WHERE clause.

[fsportelli0]

fsportelli0:

MATCH (entity:XmlTag) MATCH (dataType:XmlTag) WHERE entity._name="orm_EntityType" AND dataType._name="odt_dataType" AND dataType.name=entity._ReferenceMode RETURN DISTINCT entity.id, entity.Name, entity._ReferenceMode, entity.IsIndependent, dataType.id

Thanks, I think I'm pretty close to the solution.
Using COALESCE in this way RETURNS the rows I want:

MATCH (entity:XmlTag)
MATCH (dataType:XmlTag)
WHERE entity._name="orm_EntityType" AND dataType._name="odt_dataType" AND COALESCE (dataType.name=entity._ReferenceMode) <> TRUE
RETURN DISTINCT entity.id, entity.Name, entity._ReferenceMode, entity.IsIndependent

BUT, I need to display to property dataType.id as well.
So, I run this query:

MATCH (entity:XmlTag)
MATCH (dataType:XmlTag)
WHERE entity._name="orm_EntityType" AND dataType._name="odt_dataType" AND COALESCE (dataType.name=entity._ReferenceMode) <> TRUE
RETURN DISTINCT entity.id, entity.Name, entity._ReferenceMode, entity.IsIndependent, dataType.id

the number of rows grows exponentially and this is unwanted.
Maybe I'm missing something?

[andrew_bowman · Accepted solution]

You may be using coalesce() wrong, you typically supply at least two parameters, and the first non-null entry will be used.

Looking at your requirements again, we may need to use an OPTIONAL MATCH for dataType. We may not need coalesce() at all. Give this a try

MATCH (entity:XmlTag)
WHERE entity._name="orm_EntityType"
OPTIONAL MATCH (dataType:XmlTag)
WHERE dataType._name="odt_dataType" AND dataType.name=entity._ReferenceMode
RETURN DISTINCT entity.id, entity.Name, entity._ReferenceMode, entity.IsIndependent, dataType.id

[fsportelli0]

It works!
Thank you very much.