How to get only elements with related values in GraphQL

[yorek]

In GraphQL you can ask for nested items. How can you create a request so that only items with some nested item will be returned?

For example, let's say you have Category and Subcategory objects. You want to get all the categories that have some subcategories. If a category doesn't have any subcategory, it should not be returned.

How this can be achieved with Data API builder?

[yorek]

You can get what you want by using also filter parameter, using the nested object also to filter out unwanted results. In this specific case the GraphQL query would be:

query Test {
  categories(filter: {subcategories: {id: {isNull: false}}}) {
    items {
      id
      category      
    }
  }
}

The Category and Subcategory entities have both a relationship defined in the configuration file, so that such filtering is possible. Here's the entity section of the configuration file:

"entities": {
  "Category": {
    "source": "dbo.category",
    "permissions": [{
        "role": "anonymous",
        "actions": [ "*" ]
      }],
    "relationships": {
      "subcategories": {
        "cardinality": "many",
        "target.entity": "Subcategory"
      }
    }
  },
  "Subcategory": {
    "source": "dbo.subcategory",
    "permissions": [{
        "role": "anonymous",
        "actions": [ "*" ]
      }],
    "relationships": {
      "category": {
        "cardinality": "one",
        "target.entity": "Category"
      }
    }
  }
}