const query = `
PROFILE
MATCH (me:User {publicId: '${data.userId}'})
USING INDEX me:User(publicId)
OPTIONAL MATCH (me)-[:POSTED]->(mine:Post)-[:HAS_CONTENT]->(mineContent:PostContent)
OPTIONAL MATCH (userInfo:User {publicId: mine.userId})-[:POSTED]->(mine)
OPTIONAL MATCH (user:User)-[:POSTED]->(publicPost:Post {visibility: 'public'})-[:HAS_CONTENT]->(publicContent:PostContent)
OPTIONAL MATCH (me)-[:FOLLOWS]->(follower:User)-[:POSTED]->(followerPost:Post)-[:HAS_CONTENT]->(followerContent:PostContent)
OPTIONAL MATCH (mine)-[:TAG_IN]->(mineTags:User)
OPTIONAL MATCH (publicPost)-[:TAG_IN]->(publicPostTags:User)
OPTIONAL MATCH (followerPost)-[:TAG_IN]->(followerPostTags:User)
WITH me,
COLLECT(DISTINCT {post: mine, content: mineContent, creator: userInfo, tags: mineTags}) +
COLLECT(DISTINCT {post: publicPost, content: publicContent, creator: user, tags: publicPostTags}) +
COLLECT(DISTINCT {post: followerPost, content: followerContent, creator: follower, tags: followerPostTags}) AS posts
UNWIND posts AS postInfo
WITH me, postInfo.post AS post, postInfo.content AS content, postInfo.creator AS creator, postInfo.tags AS taggedUsers
WITH me, post, content, creator, taggedUsers,
CASE
WHEN post.visibility = 'onlyme' AND post.userId = me.publicId THEN true
WHEN post.visibility = 'specific' AND post.userId = me.publicId THEN true
WHEN post.visibility = 'followers' OR post.visibility = 'public' THEN true
ELSE false
END AS canSeePost
WHERE canSeePost OR canSeePost IS NULL
WITH me, post, content, creator, taggedUsers
ORDER BY post.createdAt DESC
SKIP ${data.offset}
LIMIT ${data.limit}
RETURN COLLECT(DISTINCT {post: post, content: content, user: creator, taggedUsers: taggedUsers}) AS posts;
`;
This a lot here.
- In your query, you have a match on a User node related to a specific Post node. In this match you have an additional constraint that the User node have a property that equals a property on the Post node. I don't recommend this ever. This is not very graphy, but more like a relational db. The constraint between the User and Post should be implied with the POSTED relationship, otherwise there should not be a POSTED relationship between them. If there is some additional constraint, it should be the property of the relationship or a more specific relationship type that differentiates it.
- I see the biggest issue with this query is its structure. You have consecutive match statements; some not correlated to the previous one. This creates Cartesian products between the results, thus bigger result sets and redundant work. This needs to be refactored.
- The "collect distinct" operations over this entire data set that probably exploded due to the Cartesian products. This is probably why you needed the distinct to remove a lot of the redundant data caused by the Cartesian products. Also, the distinct is across the maps you are collecting, so the code has to compare each element of each map to determine equality in order to reduce to a distinct set. This may also be adding to the execution time of this query.
- The collect statements are flattening out the data, such that the same post is repeated for each User tagged in the post. Would it be better to have all the Users tagged by a post returned in a list with the Post, instead of a duplicate post record for each tagged User? The same seems to be true for the creator's of the Post (these are the 'userInfo' Users). Would it be better to return these in a list associated with the Post?
- Instead of collecting all the posts, then unwinding and filtering on "canSeePost", you could filter the Posts upfront with the same condition before collecting them.
- Do you still get duplicate Posts in your returned data, so you still need a Distinct at the end as well?
I would be glad to help you rewrite this, but I need the clarity first so I don't change the intended outcome.
If you don't want help with an overhaul, at the very least change the OPTIONAL MATCH and COLLECTS to do them in three successive groups instead. This would eliminate much of the Cartesian product issues. For instance, these can do done in a block:
OPTIONAL MATCH (me)-[:POSTED]->(mine:Post)-[:HAS_CONTENT]->(mineContent:PostContent)
OPTIONAL MATCH (userInfo:User {publicId: mine.userId})-[:POSTED]->(mine)
OPTIONAL MATCH (mine)-[:TAG_IN]->(mineTags:User)
COLLECT(DISTINCT {post: mine, content: mineContent, creator: userInfo, tags: mineTags})
The above will result in one row. You can pass this through when doing the same to the next block of MATCH and COLLECT statements for the 'publicPost' results, then the 'followerPosts'.
Ok, here is something to give you some ideas. I am not sure it is the exact same, but I tried to follow what I thought was the intent of the query. I did not understand the need for the optional matches, so I left them out. You can make adjustments if they are needed.
Of course, I don't have test data so your mileage may vary.
MATCH (me:User {publicId: '${data.userId}'})
Call {
WITH me
MATCH (me)-[:POSTED]->(post:Post)
MATCH (user:User)-[:POSTED]->(post)
WHERE user = me OR user.publicId = post.userId
RETURN user, post
UNION
WITH me
MATCH (user:User)-[:POSTED]->(post:Post {visibility: 'public'})
RETURN user, post
UNION
WITH me
MATCH (me)-[:FOLLOWS]->(follower:User)-[:POSTED]->(post:Post)
RETURN follower as user, post
}
WITH DISTINCT me, user, post
WITH user, post, CASE
WHEN post.visibility = 'onlyme' AND post.userId = me.publicId THEN true
WHEN post.visibility = 'specific' AND post.userId = me.publicId THEN true
WHEN post.visibility = 'followers' OR post.visibility = 'public' THEN true
ELSE false
END AS canSeePost
WHERE canSeePost
WITH user, post
ORDER BY post.createdAt DESC
SKIP ${data.offset}
LIMIT ${data.limit}
RETURN COLLECT({
post: post,
content: [(post)-[:HAS_CONTENT]->(content:PostContent)|content],
user: user,
taggedUsers: [(post)-[:TAG_IN]->(tags:User)|tags]
}) AS posts
Thank you sir its work also
const query = ```
MATCH (me:User {publicId: '${data.userId}'})
Call {
WITH me
MATCH (me)-[:POSTED]->(post:Post)
MATCH (user:User)-[:POSTED]->(post)
WHERE user = me OR user.publicId = post.userId
RETURN user, post
UNION
WITH me
MATCH (user:User)-[:POSTED]->(post:Post {visibility: 'public'})
RETURN user, post
UNION
WITH me
MATCH (me)-[:FOLLOWS]->(follower:User)-[:POSTED]->(post:Post)
RETURN follower as user, post
}
WITH DISTINCT me, user, post
WITH user, post, CASE
WHEN post.visibility = 'onlyme' AND post.userId = me.publicId THEN true
WHEN post.visibility = 'specific' AND (post.userId = me.publicId OR (post)<-[:SHOULD_SEE]-(me)) THEN true
WHEN post.visibility = 'followers' OR post.visibility = 'public' THEN true
ELSE false
END AS canSeePost
WHERE canSeePost
WITH user, post
ORDER BY post.createdAt DESC
SKIP ${data.offset}
LIMIT ${data.limit}
RETURN COLLECT({
post: post,
content: [(post)-[:HAS_CONTENT]->(content:PostContent)|content],
user: user,
taggedUsers: [(post)-[:TAG_IN]->(tags:User)|tags],
specificUser: [(post)<-[:SHOULD_SEE]-(specificUser:User) | specificUser]
}) AS postsSorry, I don’t understand your question/issue, did you want to include the comments in your results?
Is thus version of the query producing the correct results and is it faster? Did we solve your problem?
Yes sir Its work and thank you
1 Like
const query = MATCH (p:Post {postId: $postId}) OPTIONAL MATCH (p)<-[:POST_COMMENT]-(comment:Comment)<-[:HAS_COMMENT]-(user:User) OPTIONAL MATCH (comment)<-[:REPLIED_TO*0..]-(child:Comment) WITH comment, user, child OPTIONAL MATCH (userDetails:User {publicId: user.publicId}) RETURN comment, userDetails, collect(child) AS childComments ;
[
{
"comment": {
"createdAt": "Fri Jan 19 2024 17:41:56 GMT+0530 (India Standard Time)",
"deletedAt": "",
"text": "parent",
"postId": "3675196306686547",
"publicId": "3577041529831424",
"childComments": [
{
"createdAt": "Fri Jan 19 2024 17:42:55 GMT+0530 (India Standard Time)",
"deletedAt": "",
"commentId": "3577041529831424",
"text": "child1",
"userId": "3575189734199296",
"publicId": "3577041652428800"
},
{
"createdAt": "Fri Jan 19 2024 17:44:00 GMT+0530 (India Standard Time)",
"deletedAt": "",
"commentId": "3577041652428800",
"text": "child2",
"userId": "9995055351656459",
"publicId": "3577041789575168"
}
]
},
"userDetails": {}
},
{
"comment": {
"createdAt": "Fri Jan 19 2024 18:29:40 GMT+0530 (India Standard Time)",
"deletedAt": "",
"text": "parent",
"postId": "3675196306686547",
"publicId": "3577047534632960",
"childComments": [
{
"createdAt": "Fri Jan 19 2024 18:31:07 GMT+0530 (India Standard Time)",
"deletedAt": "",
"commentId": "3577047534632960",
"text": "child",
"userId": "3575189734199296",
"publicId": "3577047718506496"
}
]
},
"userDetails": {}
},
{
"comment": {
"createdAt": "Fri Jan 19 2024 18:31:51 GMT+0530 (India Standard Time)",
"deletedAt": "",
"text": "parent",
"postId": "3675196306686547",
"publicId": "3577047809339392",
"childComments": [
{
"createdAt": "Fri Jan 19 2024 18:32:29 GMT+0530 (India Standard Time)",
"deletedAt": "",
"commentId": "3577047809339392",
"text": "child",
"userId": "3575189734199296",
"publicId": "3577047888879616"
}
]
},
"userDetails": {}
},
{
"comment": {
"createdAt": "Fri Jan 19 2024 18:33:11 GMT+0530 (India Standard Time)",
"deletedAt": "",
"text": "parent",
"postId": "3675196306686547",
"publicId": "3577047978762240",
"childComments": [
{
"createdAt": "Fri Jan 19 2024 18:33:53 GMT+0530 (India Standard Time)",
"deletedAt": "",
"commentId": "3577047978762240",
"text": "child",
"userId": "3575189734199296",
"publicId": "3577048065191936"
},
{
"createdAt": "Fri Jan 19 2024 18:33:53 GMT+0530 (India Standard Time)",
"deletedAt": "",
"commentId": "3577047978762240",
"text": "child",
"userId": "3575189734199296",
"publicId": "3577048065191936"
}
]
},
"userDetails": {}
}
]
query create how to get nested child
You have to stop doing this:
OPTIONAL MATCH (userDetails:User {publicId: user.publicId})
Why isn't the user the same as the userDetails node? You should have relationships between entities, and not rely on ids stored on one entity identifying another entity.
What do you mean by nested children? I see in your response that you got a list of comments returned as childComments.
I think your userDetails is empty because match by id is not finding a User. You should use a relationship.
okk but how to get comment and nested comment
can you send socal media type project dummy project
like as one parent comment child comment inside child comment
Are you referring to this data and getting the child nodes?
MATCH (comment)<-[:REPLIED_TO*0..]-(child:Comment)
Can you give me an example of the result you want?
In case you want the comment nodes and all child comment nodes in a response, you can take the following approach. Here I added a "level" attribute that indicates the comment node's position in the chain of comment nodes. Level zero is for the anchor comment node and higher level values mean the comment node is further away (more nested).
match p=(n:Comment)<-[:REPLIED_TO*0..]-(m:Comment)
where n.id = 100 and
not exists((m)<-[:REPLIED_TO]-(:Comment))
unwind range(0, size(nodes(p))-1) as index
return {
node_id: nodes(p)[index].id,
level: index
} as comment_nodes
Test data:
create(c0:Comment{id:100}),(c1:Comment{id:101}),(c2:Comment{id:102}),(c3:Comment{id:103})
create(c0)<-[:REPLIED_TO]-(c1)<-[:REPLIED_TO]-(c2)<-[:REPLIED_TO]-(c3)
return *
Result:
The following predicate eliminates all the shorter partial comment chains from being returned and only returns the longest one:
not exists((m)<-[:REPLIED_TO]-(:Comment))
Query and result without the predicate:
Query and result with predicate:
As you can see, the first three rows in the first query are filtered out in the second query.
If you want the comments nodes returned together in a list as an attribute of your entire response, you can use this approach that gathers the comment nodes into a list on one line of code.
match p=(n:Comment)<-[:REPLIED_TO*0..]-(m:Comment)
where n.id = 100 and
not exists((m)<-[:REPLIED_TO]-(:Comment))
return [index in range(0, size(nodes(p))-1) | {
node_id: nodes(p)[index].id,
level: index
}] as comment_nodes
