I am using elasticsearch and gem searchkick in my application. I have an Event model, where users are able to click on the event etc.

In the event list I have a requirement for filter "viewed in the last 7 days". I am wondering what's the best way to index data so it serves the requirement appropriately.

At first I thought about storing user ids on the Event model ES index like so :

Event: {
 _id: 1,
 user_ids: [1,2,3,4...]
}

But this would give me all the event views, not only in the last 7 days. How would I create indexed data to accommodate this case?

Update per comments to provide more context:

This is an example of some events (what is being stored) :

[
  {
    _id: 1,
    name: 'Company get together',
    start_time: '10:00',
    start_date: '2022-10-01',
    duration: 3600
    ...
  },
  {
    _id: 2,
    name: 'Company break',
    start_time: '00:05',
    start_date: '2022-01-01',
    duration: 172800
    ...
  },
  {
    _id: 3,
    name: 'Sales webinar',
    start_time: '09:00',
    start_date: '2022-15-01',
    duration: 7200
    ...
  },
  ...
]

This would be an example of what is in the events right now. If some user clicks on this event, we want a record this (somehow) on the event model, so that we could return the list of events that user has clicked on in the last 7 days.

If we used a structure like this:

{
    _id: 3,
    name: 'Sales webinar',
    start_time: '09:00',
    start_date: '2022-15-01',
    duration: 7200,
    user_click_ids: [1, 2, 3]
    ...
  }

And we append to user_click_ids every time the user clicks on a specific event, then I can use user_click_ids to return the list of the users who clicked on that event (and filter on it providing current user id).

But then I need another dimension to this, not only which user clicked, but also was the click in the last 7 days or not.

How do I create an index for this case or how would I solve this?

CodePudding user response：

Maybe a solution that uses less searchkick, but you could use another model to handle this? Off the top of my head (haven't run this):

class CreateEventClicks < ActiveRecord::Migration
  def change
    create_table :event_clicks do |t|
      t.belongs_to :event, null: false, foreign_key: true
      t.belongs_to :user, null: false, foreign_key: true
      t.timestamps
    end

    add_index :event_clicks, :event, :user
  end
end

class EventClicks < ApplicationRecord
  belongs_to :user
  belongs_to :event
end

def find_clicked_events(user, event, time)
  EventClicks.where(user_id: user.id, event_id: event.id, created_at: time..Time.now)
end

CodePudding user response：

I'm not sure about searchkick since I'm .net guy but I'd store entire information about the click instead of just the id. I.e.

Event: {
 _id: 1,
 user_clicks: [{
   user_id: 1,
   timestamp: 2022-02-24 4:00:00
 }, ...]
}

However, depending on the expected usage pattern (i.e. a lot of users clicking a lot of events) documents might become large. If that is the case you might consider doing additional filtering on your backend instead of elasticsearch.

In such a case you'd go with something like user_activities index

user_id: 1,
event_clicks: [{
  event_id: 1
  timestapmp: 2022-02-24 04:00:00
}, ...]

The obvious downside of such approach is additional code on a backend with might be hard to support and complicate things.

The thing with NoSQL databases (and elasticsearch as well) is that there is no single one-size-fits-all solution and you have to understand your query patterns well. So it's up to you to decide what hinders you more: growing document size with approach 1 or adding some custom logic on your backend with approach 2.