Querying Data Sets that are available for a given Date

Hi there,

I am trying to filter my index by a “virtual status”. All data items have a start_date and an end_date as a timestamp. There are 4 virtual status cases:

  • DRAFT - start_date = 0
  • SCHEDULED - start_date in the future
  • ONLINE - start_date in the past, end_date in the future
  • OFFLINE - end_date in the past

So if I want all data sets for ONLINE this would be where todays date is between the start_date and the end_date. I can get that by adding a filter like start_date < 1603727634 AND 1603727634 < end_date that for example.

Now, I want to query for DRAFT and ONLINE so I tried to combine some filters like this: start_date = 0 OR (start_date < 1603727634 AND 1603727634 < end_date). Unfortunately this leads to the error filter (X AND Y) OR Z is not allowed, only (X OR Y) AND Z is allowed.

How am I supposed to query in this scenario? Do I have to restructure my data? If yes, how could I do that since the status depends on the current date? :frowning:

I really didn’t expect a blocker like that. I am really hoping for help and would appriciate every idea how to go about this problem :slight_smile:

Greetings
Sascha

Hi @sascha.siemer ,

Consider enhancing your records with additional attributes.

What this means is that you will have to more actively update your records in Algolia, based on data changes in your database. We encourage you to think of Algolia as a resource for search but not as the real source of truth - that is your database.

For example, instead of just the numeric forms of the dates:

{
    start_date: 123456789,
    end_date: 987654321
}

Consider enriching with more explicit boolean attributes that you can filter on:

{
    start_date: 123456789,
    end_date: 987654321,
    isDraft: // true or false,
    isOnline: // true or false,
    isOffline: // true or false
}

Then you can do filters for DRAFT and ONLINE filters: isDraft: true AND isOnline:true

Hi @ajay.david,

thanks for the very quick reply! :slight_smile: The problem I see with this approach is that it means we would have to update the data in algolia not only on active changes in our database but also as time goes by. An end_date that is not reached yet could be reached in 5 minutes… or even in 2 minutes.

So, as I understand your answer my only choice is to run a cron job to check all the data sets in my database and if one of them has reached its end_date update it on algolia? :thinking:

It seem like we found a solution for our use case even without changing the data:

Instead of querying something like start_date = 0 OR (start_date < 1603727634 AND 1603727634 < end_date) we now query (start_date = 0 OR start_date < 1603727634) AND (start_date = 0 OR 1603727634 < end_date) which gives the expected result.

The drawback is that it bloats the filter string (almoset doubles it) and it takes a bit more programming logic to build that filter string, especially when combining more status cases, but otherwise it works just fine. :tada:

Thanks a lot for the feedback tough! :smile:

1 Like