MATCH(g:Geocache)-[l:LOG]->(:Log) WHERE g.referenceCode = 'GC728BR' AND l.typeId = 2 RETURN date(l.loggedDate) AS date, l.favoritePoint AS fp ORDER BY l.loggedDate
MATCH(g:Geocache)-[l:LOG]->(:Log) WHERE g.referenceCode = 'GC728BR' AND l.typeId = 2
RETURN date(l.loggedDate) AS date, l.favoritePoint AS fp
GROUP BY date, fp ORDER BY l.loggedDate
Cypher doesn't have a GROUP BY clause, the grouping key consists of the variables present at the time of the aggregation.
As a result this is a tricky aggregation, as you want to group the occurrence across just one variable (the date), but have the count from two variables (date and the boolean value).
One way you can do this is to aggregate the count by date and collect the fp values at the same time, giving you your count for the date, then get the size of the filtered list by keeping only true values:
MATCH(g:Geocache)-[l:LOG]->(:Log)
WHERE g.referenceCode = 'GC728BR' AND l.typeId = 2
WITH date(l.loggedDate) AS date, count(l) as found, collect(l.favoritePoint) AS fps
RETURN date, found, size([fp in fps WHERE fp = true]) as fp
You can of course just use the single collect() aggregation and use the size to get the counts you want as well:
MATCH(g:Geocache)-[l:LOG]->(:Log)
WHERE g.referenceCode = 'GC728BR' AND l.typeId = 2
WITH date(l.loggedDate) AS date, collect(l.favoritePoint) AS fps
RETURN date, size(fps) as found, size([fp in fps WHERE fp = true]) as fp
Oh I'm using neo4j-bolt-php driver and I got this error: Date is not supported as a return type in Bolt protocol version 1. Please make sure driver supports at least protocol version 2. Driver upgrade is most likely required.
As noted you're using an older version of the driver. This error is only about returning a result that is a date type, so to get around this you can convert this to a string representation. Use toString(date) as date in the RETURN, that should work.