Filter on array of date ranges

Hi everyone. I’m finding it hard to figure out how to do the following query.
I have booking slots for each record and I want to query all records that do not have an intersecting slot (date range). This is, I want to only get a records if it is not yet booked.

First any suggestions on how to approach? I see 2 ways:

  1. I want to EXCLUDE from the result all records that match a condition (A - detailed below)
    or
  2. I want to get records that match a condition (B - detailed below)

My record:

"bookings": [
    {
      "starts_at": 1567299600,
      "ends_at": 1569805200
    },
    {
      "starts_at": 1572566400,
      "ends_at": 1575072000
    }
  ]

The way to check if any of the “bookings” intersects is (condition A):

starts_at < D2 and ends_at > D1

where D1 is the start date and D2 the end date variables.
If going for approach 2, the condition would be (condition B):

(NOT starts_at < D2) OR (NOT ends_at > D1) OR (hasBookings = 0)

The problem with this condition is that as soon as I have more than 1 booking it stops working…

Any tips/suggestions on how to approach this scenario?

Thanks in advance!
(hope it isn’t too confusing)
x

Hi @tiagoff,

Thanks for contacting Algolia!

Filtering on dates is not a strength of Algolia, we can be honest about that :slight_smile: But it is doable.

You’ll have to change your structure so that it does not use an array with multiple date objects. The filtering with Algolia on dates will work best only if looking at a single date in each record - or - if the array is of timestamps.

For inspiration, similar posts on this topic:

In addition, there are some limitations to our filters. Once you have the updated record structure, you can test your syntax with this validator:

We hope this helps!

Hi @ajay.david,

thanks for you prompt response. I’ve checked the post you mention but I don’t think it helps much since it is basically my “condition A”…

The only possibility I see is to run 2 queries, one (A) with all filters except the dates filters, and a second one (B) with the same filters + the dates filter and then remove the intersecting results from A.
Is there a way to do such “composed” query in algolia or will I need to do the intersection on the client side?

Again, many thanks!