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
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
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
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
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
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
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)?
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.
