How can I find the most recent nodes that match a query?

[geronimo4j]

Hi I have Things that are TAGGED in Posts written by Users. Those same Posts might have a Rating.

Since a user can make many posts for the same thing, each with a new rating, I just want to receive the most recent rating for each thing, for that user.

So, if a user rated Thing ABC in 9 different posts, and each post has a date, how can I use only the post with most recent date? The thing is, I want to return the rating for all Things, so I don't want to just do a LIMIT 1 necessarily, since that would return a single rating.

I would like to return all Things that have a rating by a user, and that thing's most recent rating. I was trying all sorts of things but this is the most basic structure of the query:

MATCH (t:Thing)<-[:TAGGED]-(post:Post)<-[:WROTE]-(u:User { id: "abcdef"})
MATCH (post)-[:HAS_RATING]->(rating:Rating)
RETURN rating

MATCH (t:Thing)<-[:TAGGED]-(post:Post)<-[:WROTE]-(u:User { id: "abcdef"})
WITH DISTINCT post.date //this did not work! silly attempt
MATCH (post)-[:HAS_RATING]->(rating:Rating)
RETURN rating

Thank you in advance for any help!

[terryfranklin82]

max(post_date) should get you the biggest (most recent) date.

It still functions the same way, but personally I prefer to write queries with the WHERE clause at the end of the match (I guess it reminds me of how a sql query is structured):

MATCH (t:Thing)<-[:TAGGED]-(post:Post)-[:HAS_RATING]->(rating:Rating)
WHERE (u:User { id: "abcdef"})-[:WROTE]->(post)
RETURN max(post.date), rating

[geronimo4j]

Thanks Terry. This returns the date of every post when in reality I exclusively am looking for just the rating of the posts with the most recent date, for each/exclusively to a single Thing.

Also this doesn't seem to work for another reason, receiving Variable 'u' not defined

[terryfranklin82]

terryfranklin82:

WHERE (u:User { id: "abcdef"})-[:WROTE]->(post)

Sorry, line 2 should have read:

WHERE (:User { id: "abcdef"})-[:WROTE]->(post)

I think the right query for you might be a hybrid of mine and ameyasoft's post (but it's difficult to test without sample data):

MATCH (t:Thing)<-[:TAGGED]-(post:Post)-[:HAS_RATING]->(rating:Rating)
WHERE (:User { id: "abcdef"})-[:WROTE]->(post)
with post order by post.date desc limit 1
RETURN t, rating

[geronimo4j]

Hi Terry this has the same issue where it returns only a single post, rather than all posts that match, any ideas?

[ameyasoft]

Try this:

match (u:User { id: "abcdef"})-[:WROTE]->(post:Post)
with post order by post.date desc limit 1
MATCH (t:Thing)<-[:TAGGED]-(post:Post)-[:HAS_RATING]->(rating:Rating)
return t.thing, rating.value

[geronimo4j]

I wonder, would this return a post that doesn't have a rating because that part comes after the limit 1?

[terryfranklin82]

Can you share statements to create some sample data? It'll make it much easier to figure out.

[Cobra · Accepted solution]

Hello @geronimo4j

This query should do the trick, I first sorted all posts by date than I collected all ratings by Thing and only kept the first element in the list since it's the last rating then I returned ratings for all things:

MATCH (t:Thing)<-[:TAGGED]-(post:Post)<-[:WROTE]-(u:User { id: "abcdef"}), (post)-[:HAS_RATING]->(rating:Rating)
WITH t, post, rating
ORDER BY post.date DESC
WITH t, collect(rating)[0] AS rating
RETURN rating

Regards,
Cobra

[geronimo4j]

Hi @Cobra, thank you- this seems to work but is it performant? Is it calling every single post with a rating during this part: (post)-[:HAS_RATING]->(rating:Rating)?

Is that a common / best practice? Thanks again

[Cobra]

Doing a double MATCH or what I did is the same thing in your case. It's maybe optimizable but without the model and a sample data, I can only do this. You can compare query efficiency with PROFILE or EXPLAIN if you have different ones which give the same result.

[geronimo4j]

Great, thanks again!