Or condtion between 2 different node filters (anti join)

Hi, I am trying to do something like an anti join. Assuming I have 2 types

type Room {
  Name: String! @id  @search(by: [hash])
  Booked: [Meeting]
}

type Meeting {
  Room: Room! @hasInverse(field: Booked)
  StartTime: DateTime! @search
  EndTime: DateTime!  @search
}

Now, I want to find all the free rooms in a given date range. That means they either have no related meeting at all or the meeting start and end date don’t fall within the date range.

I manage to filer for no booked or no meeting in date range, but I don’t manage to combine the two.

The below looks promising, but It doesn’t bring me room that don’t have any booked edges

{
  queryRoom @cascade {
    Name
    Booked(filter: {
        not: {
          StartTime: {between: {min: "2021-06-06T09:00:00Z", max: "2021-06-06T11:00:00Z"}}, 
          EndTime: {between: {min: "2021-06-06T09:00:00Z", max: "2021-06-06T11:00:00Z"}}
        }
      }) {
      StartTime
      EndTime
    }
  }
}

When I add a filter for has no bookings to the room, then I also don’t get the ones that have edges to Meetings, but the meeting is in not in the date range.

{
  queryRoom(filter: {not: {has: Booked}})  @cascade {
    Name
    Booked(filter: {
        not: {
          StartTime: {between: {min: "2021-06-06T09:00:00Z", max: "2021-06-06T11:00:00Z"}}, 
          EndTime: {between: {min: "2021-06-06T09:00:00Z", max: "2021-06-06T11:00:00Z"}}
        }
      }) {
      StartTime
      EndTime
    }
  }
}
"data": { "queryRoom": []},

Removing cascade does also not want I want. It will bring all non-booked but not the ones that are free in the date range.

Currently, I wish I could have a OR condition between the two filters.

how about writing like this, have you tried this

Booked (filter: {
      and: [
         {not: { StartTime: {between: {min: "2021-06-06T09:00:00Z", max: "2021-06-06T11:00:00Z"}}}},
         {not: { EndTime: {between: {min: "2021-06-06T09:00:00Z", max: "2021-06-06T11:00:00Z"}}}},
      ]
})

Hi Thanks for the answer. Unfortunately this is identical to my first example.

My problem is that, yes I can filter the date range but when there is a room that has no edge to meetings, it will not show, as the filter on booked enforces that the room has at least 1 edge to meetings.

It needs to be one of them, hence I am saying OR between 2 different node filters, one for Room Type and one for Meeting type.

I want to ask dgraph this:

Either give me all rooms that have not been booked (at all)
Or give me all the rooms that have been booked but not in this date range

Apart from having an OR between 2 filters which I don’t think is possible, I think there should be something like isNull

{ 
    or: {
         is: Null, # pseudo syntax
         not: {
             StartTime: {between: {min: "2021-06-06T09:00:00Z", max: "2021-06-06T11:00:00Z"}}, 
             EndTime: {between: {min: "2021-06-06T09:00:00Z", max: "2021-06-06T11:00:00Z"}}
        }
    }
}

This maybe doesn’t make much sense either because if there is no meeting, then what node are we applying the filter to in the first place?

ok, So there is also a has filter.
How about combining that with a not

btw, I am also new to dgraph, just thinking out loud here:

I think this should work though, I just made it with the User+Post schema I have and following is the pseudo code:

Booked (filter: { or: [{ not: { has: StartTime } }, {  YOUR_TIME_CONDITIONS } }] })

this should give you all rooms where no bookings plus the ones matching your filters.

Instead of checking for StartTime, you can also check for existence of an ID if there is one.

I have actually tried that already but as I mentioned above, if there is no meeting, you cannot check if it has a start time or not.

The issues that everything we are currently doing is tied to the meeting, so it will not bring a room that has no edge to meetings. Keep in mind we are dealing with 2 different types.

That is why I want to say a room that has no booked or a room that has no edge to a meeting that is within date range. See my second example.

In that sense, the problem is simple to speak, I want all rooms that have not been booked during a given date range. Its just not as easy to implement it seems. In SQL the keyword for something like this is sometimes called an anti join.

Can you try this:

queryRoom(filter: {not: {has: Booked}}) {
    Name
}

I suspect the issue is because you have all the rooms booked up already (i.e. ∀r.r∈Room, r.Booked is not null)

In addition to what Chew said, There are ways to achieve this in DQL for sure.

Oh guys, now you come from the other angle, I tell you one alone is not enough.

With your query I will never get a room that has a booking but not in my time range. That’s wrong. If the room has a booking yesterday, it should be valid to book for today right?

I post the exact same sample as in my initial question again, I am stating that I need kind of an OR condition between the 2 different filters.

See all the mentioned conditions in this thread are already in this snipet. Its just they don’t work together like that, which makes kind of sense.

As a dirty workaround I was thinking of taking all the bookings that are in the time range and ignore(remove those rooms from a list of all rooms (on the frontend). This doesn’t feel right as the whole point of graphql is “fetch only what you need”.

Hi @Nico_Braun , currently we don’t support combining root and nested level fields in filters. You have to do it via custom DQL queries as of now because we can write such queries in DQL.
This feature is requested by many other users , and there are proposals for the implementation also .Proposal Nested Object Filters for GraphQL rewritten as var blocks in DQL

We will discuss it with team and try to add this in future , probably in next release.
Thanks.

1 Like

This is exactly the work around we implemented in our app for the time being. We allow users to build these complex filters dynamically so it was tricky getting all of this working to give users a list of filter options to dynamically build the queries and then combining multiple lists of ids logically into a single set of ids.

To get a list of ids from a deeply nested query give this function a try:

I modified it slightly in my actual production since Apollo Client auto adds the __typename to every node.

FYI, for a better performance run all of the queries to get these filtered ids in one operation.

I put many, many, many! hours into getting all of this to work well, so I probably will not share this code publicly as it is largely the crux of our app’s upperhand on other apps filtering data with GraphQL in Dgraph. I plan to showcase this filter builder part of our app in the upcoming Dgraph Day.

Hi @Nico_Braun

I’m not just coming from one angle. I’m trying to figure out why your query returns nothing. If all your rooms are booked up then

queryroom(filter: {not: {has: Booked}}){ Name } 

Would return an empty data set. Thus, querying for an additional field:

{
  queryRoom(filter: {not: {has: Booked}})  @cascade {
    Name
    Booked # doesn't matter what filter is passed in here.
  }
}

Would not have effect.

Another way you would do it is to put two queries together and them merge them outside of graphql:

{
   unbooked: queryRoom(filter: {not: {has: Booked}}) {
        Name 
   }

   boooked_in_time: queryRoom(filter: {has: Booked}}) {
        Name
        Booked(filter: ...)
   }

}

1 Like

Hi Chewxy,

yes, I was thinking the same yesterday as what you wrote as your last example.

I will try first to do it with custom dgraph query if possible, if not I will go for this approach.

Still thrilled to hear that dev is tinkering on these “nested object filters”

Cheers