search message in a conversation like messenger

Question

I want to implement a search feature in my chat application like a messenger, skype, whatsapp has done. What they have done is when we search a word then it would not only show the message with that word in the current viewable message list but also on the past messages. To see the message with matching word, they provide next and previous icon to traverse. My difficulty on solving this problem is, initially, we will have only 10-20 number of messages, but how can we now show the message with matching word which is around 1 month or say 1 year old.

For showing the list of messages I am following as per relay spec cursor based pagination which gives me edges, node, pageCursors which will have hasNextPage, hasPreviousPage, startCursor and endCursor.

How would you implement such search functionality? I am just in a need of algorithm instead of code.

UPDATE

This is what I am talking about. Here you can see, When a user searched for the word "Netflix" it showed 1 of 27 results for "Netflix" and beside it there is an icon for traversing to previous messages and recent messages which when clicked will reach you to the next messages with a word "Netflix" and that next messages might be 1 month old or 10 months old. While showing next messages it will show all the messages up to it and that message might be 400, 500 behind.

Answer 1

It is not entirely clear to me what you want to achieve, but to me it sounds like basic text search. If you are saving the message in the database I would imagine it looks something like (id, chatId, senderId, text). I would then do the following query:

SELECT * 
FROM messages 
WHERE chatId = :chatId 
  AND senderId = :senderId 
  AND text LIKE '%' || :query || '%'
LIMIT :limit
OFFSET :offset

And return the result to the user.

UPDATE: Thanks OP for clarifying the question. I would slightly modify the query in that case.

1. get the number of results:

SELECT count(id) 
FROM messages 
WHERE chatId = :chatId 
  AND text LIKE '%' || :query || '%'

2. get the Nth result:

SELECT *
FROM messages 
WHERE chatId = :chatId 
  AND text LIKE '%' || :query || '%'
OFFSET :offset

3. get context (this is what front end is responsible for):

SELECT *
FROM messages 
WHERE chatId = :chatId 
  AND created_at >= NthMessage.createdAt
LIMIT 5

The flow is the following (I'll make front end do more, but you can move this to back end pretty easily I think).

1. query 1 and 2 happen on chats/:chatId/search?query=whatever-text-you-search-for&offset=0
2. front end makes that request and receives the number of potential results the first result
3. it then uses the message to display it's context (uses message's created date and displays the messages that followed). It requests /chats/:chatId/search?created-at=${NthMessage.createdAt}&limit=5

I hope this makes sense.