Using a predicate within an EXISTS clause

[leelandclay]

I'm working on a Ticket system where a user can input a ticket with a message and an Admin can respond back and forth until it's resolved.

I want to add an indicator to the ticket to show the user or admin if there's an unread message within the ticket. The concept I'm using is that only a user that did NOT submit the ticket message can mark it as read. Also, the non-submitting person will be the only one that sees the unread status (since the submitter has obviously read it).

The query I'm using is:

WITH 'iFhYQnIs7pMFbJLmC7Ko6E39bu33' as _userId
MATCH (tm:TicketMessage)<-[:HAS_MESSAGE]-(t:Ticket)-[:SUBMITTED_TICKET]-(p:Person {userId: _userId}) 
RETURN t, max(tm.created) as lastMessage,
SIZE( [(t)-[:HAS_MESSAGE]-(tm:TicketMessage {read: false}) WHERE tm.submitter <> _userId | tm] ) > 0  as unreadMessages 
ORDER BY unreadMessages DESC, lastMessage DESC

When I first started testing, everything worked great because I only had a single message per ticket. However, now I've noticed that this query will return a line for each TicketMessage. Distinct doesn't help since the "unreadMessages" could have two different responses. I also attempted to use the any() predicate, however I got an error that the function did not exists. I think it's because it's in the RETURN and not the WHERE.

[andrew_bowman · Accepted solution]

I think the tm variable you're using in the pattern comprehension isn't a new variable, but associated with tm that is still in scope, which is why you would be getting this per ticket message.

Try something like this:

WITH 'iFhYQnIs7pMFbJLmC7Ko6E39bu33' as _userId
MATCH (tm:TicketMessage)<-[:HAS_MESSAGE]-(t:Ticket)-[:SUBMITTED_TICKET]-(p:Person {userId: _userId}) 
WITH t, max(tm.created) as lastMessage, _userId
RETURN SIZE( [(t)-[:HAS_MESSAGE]-(tm:TicketMessage {read: false}) WHERE tm.submitter <> _userId | tm] ) > 0  as unreadMessages 
ORDER BY unreadMessages DESC, lastMessage DESC

Adding the WITH in there should allow tm to go out of scope, and for the aggregation to reduce cardinality, allowing your pattern comprehension to use tm for a new variable and for it to work on fewer rows since it executes after the cardinality drops.

[leelandclay]

That worked perfectly! Thank you so much. I was pulling out what little hair I had left 🙂