Hasura graphql query between dates

Question

In my hasura.io database, I have a booking table, where I store bookings with the following properties:

• id (autoincrement)
• from (timestamptz)
• to (timestamptz)
• description (text)

Now, from the UI, when a user makes a booking, I would like to check if there are any bookings made previously that touch the range of those dates.

eg. User wants to do a booking with the following dates:

{
  "from": "2020-03-31T14:00:00+00:00",
  "to": "2020-03-31T17:00:00+00:00"
}

But in the same date there is a booking:

{
  "from": "2020-03-31T15:00:00+00:00",
  "to": "2020-04-01T17:00:00+00:00"
}

Notice, this booking is been made not within the range of the dates the user is trying to book. So the following query will return nothing.

query CheckBooking($from: timestamptz!, $to: timestamptz!) {
  booking(where: {_and: [{from: {_lte: $from}}, {to: {_gte: $to}}]}) {
    id
    from
    to
  }
}

This above is what I've tried without success. Could anyone help to figure out what's the correct query to check if there are any bookings in the range or within the users new booking?

Nostoc

Answer 1

We want to consider both from and to separately and apply a range to both. So you can do something like:

query CheckBooking($from: timestamptz!, $to: timestamptz!) {
  booking(where: {_or: [
    {_and: [{from: {_gte: $from}}, {from: {_lt: $to}}]},
    {_and: [{to: {_gt: $from}}, {to: {_lte: $to}}]},
  ]}) {
    id
    from
    to
  }
}

Obliquely