I will have an index containing messages, sort of like this:
+---------------------------------+
| thread_id | author_id | message |
+-----------+-----------+---------+
| 1 | 1 | Lorem.. |
+-----------+-----------+---------+
| 1 | 2 | Lorem.. |
+-----------+-----------+---------+
| 1 | 3 | Lorem.. |
+-----------+-----------+---------+
| 2 | 2 | Lorem.. |
+-----------+-----------+---------+
| 2 | 3 | Lorem.. |
+-----------+-----------+---------+
| 3 | 1 | Lorem.. |
+---------------------------------+
I want to be able to get all messages from threads a particular user has participated in. In SQL I am doing this with this query:
SELECT * FROM my_index WHERE thread_id IN ( SELECT DISTINCT thread_id FROM my_index WHERE author_id = 1 )
Is there a way to do something like this with Elastic?
I want to be able to get all messages from threads a particular user has participated in.
Given your example table and query is the solution not the equivalent of:
SELECT * FROM my_index WHERE author_id = 1
? We can do this but if you want the list of unique thread IDs for a user we can do that too.
Need to understand your problem better.
Not quite, that would get all messages a user posted, but I want not only their own posts, but all posts from any thread they have participated in.
So to use this thread as an example, I want a query that would return not only my 2 posts, but your post too.
It's so they then get sorted by newest first to display a sort of "updates to conversations you've participated in" page.
My bad. Coffee obviously required here.
You could use a parent/child relationship to associate individual post docs with thread docs.
The query would then find all parent (ie thread) docs that have a child (i.e. post) that belongs to your author. I't may help to add some additional filter criteria to this query though so that we only consider threads that have a child post which has been added in the last hour/day/week or whatever is reasonable.
This wouldn't necessarily give you threads that had been updated since the user's last post however (if he was the last to post in that thread).
Another option is to update a "thread summary" doc which contains fields for last_updated, last_author and all_authors. You could then query for all threads where last_updated >dateX and last_author!=userY and userY in all_authors.