Subquery?

Elastic Stack Elasticsearch
1

I have an index which contains posts made by a user:

{
    "user": 1,
    "thread": 1,
    "content": "This is the first post in a thread",
},
{
    "user": 2,
    "thread": 1,
    "content": "This is the second post in a thread",
},
{
    "user": 1,
    "thread": 2,
    "content": "This is a post in a different thread",
}

I want to construct a query with a filter to only return comments in a thread that the user has posted in (not necessarily comments the user themselves posted) - i.e. in SQL, it would be something like:

SELECT * FROM comments WHERE thread IN ( SELECT thread FROM comments WHERE user=1 )

From what I can see my options are:

  • Index a list of every thread ID a user has posted in and use the "Terms lookup mechanism"
  • Somehow create a parent-child relationship between the various comments and use a has_parent/has_child query... I'm not sure if this is possible though with the structure I have

Is there any other way I'm missing, or does anyone have any advice on what the best way to achieve what I'm looking for would be?

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.