Counting relationships while filtering

[tarendran_vivek]

UNWIND $test as rows
MATCH (b:Batch)
WHERE all(test in keys(rows) WHERE b[test]=rows[test])
RETURN DISTINCT b{
.*, driver_count: [ (d:Driver)-[:Yes]->(b:Batch) | count(d)]
} as data

I am trying to filter the batch nodes and also count the number of devices that are attached to it simultaneously. So if there are no driver nodes related to the batch node, I want to return the count as zero. However this query gives me an error. If I use MATCH (d:Driver)-[:Yes]->(b:Batch) then I wont take the batch nodes with no relationships

[Cobra]

Hello @tarendran.vivekanand

Did you try with OPTIONAL MATCH?

Regards,
Cobra

[tarendran_vivek]

Hello @Cobra

UNWIND $test as rows
OPTIONAL MATCH (d:Driver)-[:Yes]->(b:Batch)
WHERE all(test in keys(rows) WHERE b[test]=rows[test])
RETURN DISTINCT b{
.*, driver_count: count(d)
} as data

Actually this was my query when i started but I realized that it wont return the batch nodes that dont have this specific relationship.

I am trying to return all batch nodes. That is why I am using the above query which takes into account for all the batch nodes but that gives me an error since I cant count in the return function.

[Cobra]

Try WITH and after RETURN 🙂

[tarendran_vivek]

Sorry I don't get for which query are you referring too. Also I dont get where to implement the WITH clause.

[Cobra]

On the first query, you said you cannot count in the return, so instead of returning directly, you can add a WITH clause to count and after RETURN the result 🙂

[tarendran_vivek]

UNWIND $test as rows
MATCH (b:Batch)
WHERE all(test in keys(rows) WHERE b[test]=rows[test])
WITH b
OPTIONAL match (d:Driver)-[:Yes]->(b:Batch) 
RETURN DISTINCT [b,count(d)] as data

This worked for me however i want there to be a name attached to the count.
So when i get the data returned
[{"batch_id":"batch_01","batch_name":"batch","_uuid":"7f8dfc37-e23d-4c│
│9a-b805-c1ec8e084735"},0] there is a 0 at the end with no indication that it is for the count driver

[Cobra · Accepted solution]

UNWIND $test as rows
MATCH (b:Batch)
WHERE all(test in keys(rows) WHERE b[test]=rows[test])
WITH b
OPTIONAL MATCH (d:Driver)-[:Yes]->(b) 
RETURN DISTINCT b AS b, count(d) AS counter

If you want all data in the same dictionnary:

UNWIND $test as rows
MATCH (b:Batch)
WHERE all(test in keys(rows) WHERE b[test]=rows[test])
WITH b
OPTIONAL MATCH (d:Driver)-[:Yes]->(b) 
WITH DISTINCT b AS b, count(d) AS c
RETURN apoc.map.mergeList([properties(b), {counter:c}])

[tarendran_vivek]

Thanks so much and sorry for the confusion and me being slow.

[Cobra]

No problem don't worry, I was happy to help