cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Filter data (where condition) in REST API with GraphQL

oguimard
MEGA

Filter data (where condition) in REST API with GraphQL

When building graphQL to query element, it is sometime interesting to filter the data by different criteria : filtering by name, by Id, by relationships... Filters represent a "where" condition in queries.

 

Filtering can  :

  • filter by fields (MetaAttributes)
  • filter by relationships (MetaAssociation)
  • Combine criteria : and, or
  • adapt the criteria to the type : string, date, numbers...

 

How to make a filter ?

 

 The filter is an argument of the query with the keyword "filter". When using the auto-completion (with GraphiQl or Postman) the system propose the possible filter criteria. Technically the filter represent a "where" in an query (ERQL / SQL)

 

  • Filtering an application by it's name

 

Query Result
query {
  application(filter:{name:"Account Management"})
  {
    id
    name
  }
}


{
  "data": {
    "application": [
      {
        "id": "IubjeRlyFfT1",
        "name": "Account Management"
      }
    ]
  }
}

 

The current filter will limit the application that have the name strictly equals to the element given in the double quotes. See below for all string filters options.

 

  • Filtering an application by it's status

 

Query Result
query {
  application(filter:{statusReview_in:[Validated,UpdateInProgress]})
  {
    id
    name
  }
}






{
  "data": {
    "application": [
      {
        "id": "IubjeRlyFfT1",
        "name": "Account Management"
      },
      {
        "id": "pGCe26yn8zc3",
        "name": "Booking Management"
      }
    ]
  }
}

 

The current filter will limit the application that have their status review MetaAttribute strictly equals to Validated or UpdateInProgress . See below for all enumeration filters options.

 

  • Filtering an application by it's owner
Query Result
query {
  application(filter:{iTOwner_PersonSystem_some:{name:"Eric"}})
  {
    name
    iTOwner_PersonSystem {
      name
    }
  }
}










{
  "data": {
    "application": [
      {
        "name": "MyCompany.com",
        "iTOwner_PersonSystem": [
          {
            "name": "Eric"
          }
        ]
      },
      {
        "name": "Splio EmailForge",
        "iTOwner_PersonSystem": [
          {
            "name": "Eric"
          }
        ]
      },
...

 

The current filter will limit the application that have at least one IT owner strictly named "Eric" . See below for all relationships filters options.

 

How to combine filters ?

 

By default combining filtering criteria will be an "and" operator. You can explicitly select the "and" or "or" operator.

 

  • Default "and" behavior
Query Result
query {
  application(filter:{
modificationDate:"2020-04-07" 
name_contains:"CRM"})
  {
    name
    modificationDate  
  }
}






{
  "data": {
    "application": [
      {
        "name": "CRM US",
        "modificationDate": "2020-04-07"
      },
      {
        "name": "CRM Europe",
        "modificationDate": "2020-04-07"
      }
    ]
  }
}

 

In the filter curly bracket just add the filtering criteria and they will need to be all valid to return a result.

 

  • Explicit "and" or "or"

In the filter you can select as a filter "and" or "or" as shown in the screenshot :

filter_and_or.png

 

Query Result
query {
  application(filter:{or:{modificationDate:"2020-04-07" name_contains:"CRM"}})
  {
    name
    modificationDate
  }
}





{
  "data": {
    "application": [
      {
        "name": "MyCompany.com",
        "modificationDate": "2020-04-07"
      },
      {
        "name": "CRM Management",
        "modificationDate": "2017-09-28"
      },
 ...

 

Because this filter contains an "or" one of the criteria must be valid to return a result. In this example either the date or the name.

 

 

Type of filters by types of fields

 

There are several type of fields :

  • String
  • Date and DateTime
  • Numbers : float, int, double, currency
  • Enumeration
  • Boolean

For each fields the available filter allows various combination of criteria : equals, contains, greater than, in, not in...

 

String filters

 

All string fields will propose the following filter criteria. For instance for Name :

 

name: String # matches all fields with exact value
name_not: String # matches all fields with different value
name_in: [String!] # matches all fields with value in the passed list
name_not_in: [String!] # matches all fields with value not in the passed list
name_lt: String # matches all fields with lesser value
name_lte: String # matches all fields with lesser or equal value
name_gt: String # matches all fields with greater value
name_gte: String # matches all fields with greater or equal value
name_contains: String # matches all fields with a value that contains given substring
name_not_contains: String # matches all fields with a value that does not contain given substring
name_starts_with: String # matches all fields with a value that starts with given substring
name_not_starts_with: String # matches all fields with a value that does not start with given substring
name_ends_with: String # matches all fields with a value that ends with given substring
name_not_ends_with: String # matches all fields with a value that does not end with given substring

 

name.png

 

Date or DateTime filters

 

All date fields will propose the following filter criteria. For instance for Creation Date:

 

creationDate: DateTime # matches all fields with exact value
creationDate_not: DateTime # matches all fields with different value
creationDate_in: [DateTime!] # matches all fields with value in the passed list
creationDate_not_in: [DateTime!] # matches all fields with value not in the passed list
creationDate_lt: DateTime # matches all fields with lesser value
creationDate_lte: DateTime # matches all fields with lesser or equal value
creationDate_gt: DateTime # matches all fields with greater value
creationDate_gte: DateTime # matches all fields with greater or equal value

 

creationdate.png

 

Numbers filters

 

All numbers (int, float, double) fields will propose the following filter criteria. For instance for Cost Per User :

 

costPerUser: Number # matches all fields with exact value
costPerUser_not: Number # matches all fields with different value
costPerUser_in: [Number!] # matches all fields with value in the passed list
costPerUser_not_in: [Number!] # matches all fields with value not in the passed list
costPerUser_lt: Number # matches all fields with lesser value
costPerUser_lte: Number # matches all fields with lesser or equal value
costPerUser_gt: Number # matches all fields with greater value
costPerUser_gte: Number # matches all fields with greater or equal value

 

costperuser.png 

Enumeration filters

 

All enumeration fields (MetaAttribute Value) will propose the following filter criteria. For instance for Cloud Computing :

 

cloudComputing: cloudComputing # matches all fields with exact value
cloudComputing_not: cloudComputing # matches all fields with different value
cloudComputing_in: [cloudComputing] # matches all fields with value in the passed list
cloudComputing_not_in: [cloudComputing] # matches all fields with value not in the passed list

 

cloudcomputing.png

 

Boolean filters

 

All boolean will propose the following filter criteria. For instance for Freeze Past Time Period :

 

FreezePastTimePeriod: Boolean # matches all fields with exact value
FreezePastTimePeriod_not: Boolean # matches all fields with different value

 

boolean filter.png

 

 

 

 

 

 

0 Replies