Filter query to records with blank/null relationship?

Dev Support
1

I want to query for records without a relationship to a given table - could query for all records and filter in JS easily enough, but that seems inefficient, and the table could get pretty large.

I’ve tried a bunch of guesses and clicked through the OneGraph explorer (such a useful tool by the way) but the closest I’ve got was
pOPartsList(filter: {purchaseOrder: {id: {is_empty: true}}}) {
The problem is that this returns an empty array, as there are no purchase orders without IDs - there are pOParts without purchase orders though.

What I really want is something more like:
pOPartsList(filter: {purchaseOrder: null}) {
This doesn’t work though, nor does using an empty string instead.

Is there a way to filter for this or does it have to be client-side JS (easy to do but uses more compute resources so feels wasteful)?

2

Hey sam! We’re going to fix this one in the next 48-hours. Mission on!

1 Like
3

@samwoolertonLW

this should work now

2 Likes
4

What a turnaround. Thanks team, works perfectly!

2 Likes
5

That solution doesn’t work for us. We are trying to filter out only not released files with the query below but see only an empty array of items. We are absolutely sure that there are files with the release field is not set.

mapFilesList(filter: {release: null})

6

Well, I think it’s an edge case but still a valid one.

How to reproduce the problem:

  1. create two tables and make relation between (not mandatory, one release to many files in my case)
  2. create one file and one release those are related to each other
  3. remove the release entry
  4. try to filter files those haven’t release
    It’s expected that you should see the file because the release is not existing anymore but you will see nothing.

I think that’s because of another problem I experienced while I configuring table schemas and relations. I guess when you delete entry from the table it’s not deleted actually but just got marked as deleted, so maybe related to that (Does delete operation on a table's record actually delete it?)

7

Hey @eddig. Just ran your experiment. Here’s how to accomplish it.

Table A

  • Name: Text
  • TableBs: has_many

Table B

  • Name: Text
  • TableA: belongs_to (mandatory)

To query a list of TableA records that have no related TableB records, you’d use the following filter.

query {
  tableASList(filter: {
    tableBS: {
      every: {
        id: {
          is_empty: true
        }
      }
    }
  }) {
  	items {
      name
      tableBS {
        count
      }
    }
  }
}

Since there are no TableB records without a TableA record (mandatory relationship) you wouldn’t need to query the other way. However, if it were simple a has_one relationship from TableB to TableA, you could query TableB records that have no associated TableA record like so.

query {
  tableBSList(filter: {
    tableA: null
  }) {
		items {
      name
      tableA {
        id
      }
    }
  }
}

Hope this helps.

8

Hi @sebastian.scholl
Thanks for providing examples they work well in most cases, but what I’m trying to explain is an edge case where tableBSList(filter: {tableA: null}) fails. To see that you should follow my instructions, which I simplified as I can.
You need two tables TableA and TableB which have one to one non-mandatory relationship.
Create an entry in the TableA and another one in the table TableB, make sure you set relation between them.
Delete the entry in the TableB (that’s important, you should exactly delete the entry)
Now try to get entries from the TableA which haven’t relation to the TableB
{ tableASList(filter: {tableB: null}) { items { id } } }
You will see an empty list of items, but there should be the entry from TableA that now doesn’t have any relationship because you deleted the corresponding entry in the TableB.

image
image.png1686×468 33 KB

9

image
image.png1148×296 20.7 KB

10

@sebastian.scholl Following your instructions from Does delete operation on a table's record actually delete it? I destroyed all entries in the TableB and now I see the correct response with the entry in TableA.

It seems that the filter tableB: null doesn’t check that the related entry is deleted.

1 Like
11

This could be it. I’ll make our guys aware of it!