Developers' Blog

GraphQL 101: Part 2 — Elastic Query Language (EQL) Filters

  • BY SOPHIE WHEELER
  • DATE : 04 JAN 2023
    • Dive into part two of our GraphQL 101 series. Learn about using EQL filters with Skedulo Pulse, including operators, types, and lists. Ideal for building optimized queries and enhancing your solutions
post-thumb

Welcome to the second part in this series on using GraphQL with the Skedulo Pulse Platform! If you’ve not had a chance to read part one, you can check it out here and then come back. Don’t worry, I’ll wait.

Read it now? Great!

In this part, we will be covering filters. Filters are a powerful and necessary part of building well optimized queries and making your applications as performant as possible. Skedulo uses Elastic Query Language, also known as EQL, for filtering our queries. These filters are similar to the ‘WHERE’ clause in a SQL query. In part one, we covered simple filtering, but here is a quick refresher. To start with, we have to specify the ‘filter:’ parameter in our query. This will tell it to return only the records that match the filter criteria, rather than all of them for a given table:

query {
  jobs(filter: "JobStatus == 'Queued'") {
    edges {
      node {
        UID,
        Name,
        Description,
        JobStatus
      }
    }
  }
}

As you would expect, running this query will produce a result similar to the below, returning all Jobs with a JobStatus of ‘Queued’:

{
  "data": {
    "jobs": {
      "edges": [
        {
          "node": {
            "UID": "001483a7-fe78-4cce-b77e-76d84464b8e1",
            "Name": "JOB-0035",
            "Description": "updated from GraphiQL3",
            "JobStatus": "Queued"
         }
        },
        // ...more records
      ]
    }
  }
}

With that out of the way, let’s continue.

Operators and Types

You’ve already seen a commonly used Operator in the query above (equals), as well as a common Type (String). However, there are several more that Skedulo supports within query filters.

But first, a quick discussion of Types (or, Literals). As the name suggests, this is the type of data that is held in a field. For example, text (“String”) or a number (an “Integer” or “Float”).

When we use a filter to compare a field value to another value (or values, more on that later), we need to make comparisons of the same Type. In our above example, we are comparing a String (the value in the “JobStatus” field) to another String (“Queued”).

In the same way, if we wanted to filter for all jobs of a certain “Start” (a Date/Time, called an “Instant”), we’d need to compare it to another “Instant”.

For example:

query {
  jobs(filter: "Start == 2022-01-14T00:00:00.000Z" ) { 
    //…query 
  }
}

Skedulo supports the following types: String, Boolean, Integer, Floating Point (Decimal), Date/Time (Instant), Local Time, Local Date, Duration, and Null. We won’t be covering every single one of them in this post, so the main point to take away is that when doing a comparison within a filter, make sure you are comparing the same type.

For more examples of the Types available check out the developer documentation here.

If you don’t know what type a field is, check out my intro to GraphQL post, where we touch on introspection queries. I will be covering this in more detail in a future post, so keep a lookout for it!

So with Types out of the way, let’s take a look at Operators!

An operator is the ‘bit’ between the field (e.g “JobStatus” or “Start”) and the value (“Queued” or “2022-01-14T00:00:00.000Z”) that you wish to compare. In our first example, this is the “equals” operator, which is represented by == .

Let’s talk about operators in more detail.

The first is != or, Not Equal, which is literally the opposite of ==. These operators can be used when comparing any type

The next set are the comparison operators, these are < (less than), > (greater than), <=(less than or equal to), and >=(greater than or equal to). These operators are generally used for comparing numeric types, for example: Instant, Integer, Float, Local Time, and Local Date.

Next up is LIKE and NOTLIKE These operators compare whether a partial match exists, normally within a String type. We use the % as a wildcard.

So if we needed to find all of the Jobs with a description that begins with “Skedulo”, we could use the LIKE operator to do so.

query {
  jobs(filter: "Description LIKE 'Skedulo%'" ) { 
    //…query 
  }
}

Notice the % symbol after Skedulo. This means that any value can come after Skedulo and still be returned. For example, using the above query, a job with a description of “Skedulo are scheduling the modern workforce” would be returned.

Alternatively, if we wanted to find all jobs where the description didn’t contain the word “Skedulo”, we could use NOTLIKE. Note the % symbol before and after “Skedulo”. This means that if “Skedulo” is anywhere in the description, the job will not be returned.

query {
  jobs(filter: "Description NOTLIKE '%Skedulo%'" ) { 
    //…query 
  }
}

The next two operators are somewhat special, as they are designed for use within Picklist fields.

Picklist fields are essentially a list of String types, so we have INCLUDES and EXCLUDES to check if the provided String value is or isn’t within the list. They behave in much the same way as == or !=

We also have IN and NOTIN that are conceptually similar, but designed for use when you are providing a list of values for the comparison. We will cover this in more detail in the next section.

The final operators are AND and OR. These are designed to allow you to combine multiple filter criteria. For example:

query {
  jobs(filter: "JobStatus LIKE 'Q%' AND Start < 2022-01-14T00:00:00.000Z") {
    //…query
  }
}

This query would return all jobs with a JobStatus beginning with “Q” AND that started after January 1st, 2022.

query {
  jobs(filter: "JobStatus LIKE 'Q%' OR Start < 2022-01-14T00:00:00.000Z") {
    //…query
  }
}

Whereas this query would return all jobs with a JobStatus beginning with “Q” OR jobs that started after January 1st, 2022.

So you may have noticed that we are filtering here on two fields (JobStatus and Start) so now is probably a good time to talk about Lists.

Lists

Armed with the knowledge of operators, types, and multiple fields you might now be thinking, “What if I want to get all jobs that have either ‘Pending Dispatch’ or ‘Queued’ status?” Your first thought might be to do a query like this:

query {
  jobs(filter: "JobStatus == 'Queued' OR JobStatus == 'Pending Dispatch'") {
    //…query
  }
}

This would actually work just fine, and return the records you are expecting. But what if you also want to get “Dispatched”, or any number of other Status values? Your query would start to get quite long and hard to manage. Luckily, we have another option: Lists.

Remember earlier when we mentioned the IN and NOTIN operators? Well, now is their time to shine!

Using a list and the IN operator, our query becomes:

query {
  jobs(filter: "JobStatus IN ['Queued', 'Pending Dispatch']") {
    //…query
  }
}

Just as easily, we could get all other Jobs by changing the query to use the NOTIN operator. For example:

query {
  jobs(filter: "JobStatus NOTIN ['Queued', 'Pending Dispatch']") {
    //…query
  }
}

You can also use lists for other data types. For example, this query would return all jobs for the given dates:

query {
  jobs(filter: "Start IN [2022-06-16T09:35:00.000Z, 2022-07-21T09:45:00.000Z] ") {
    //…query
  }
}

Of course, you can also use the NOTIN operator to get Jobs that are not on those dates.

Now, let’s combine everything we’ve covered so far and talk about Multiple Fields.

Multiple Fields

As we’ve learnt so far, if we want to filter on multiple different fields, we can use the AND operator. We have also covered using Lists to filter on multiple values within the same field.

Now, let’s dig more into using these together, as well combining them with the OR operator.

We can, and often need to, use both the AND and OR operators when we are constructing our filters. When doing so we need to enclose each statement in brackets.

For example:

query {
  jobs(filter: "(Name == 'Skedulo' OR Description LIKE '%sked%') AND (Locked == true)") {
    //…query
  }
}

This query will return all Jobs that are either named “Skedulo” or have “sked” in the description and are also locked. You can see that the two conditions - (Name == 'Skedulo' OR Description LIKE '%sked%') AND(Locked == true) - are enclosed in brackets and are separated by an AND operator.

This of course means that both of the conditions must be met in order for a record to be returned.

We can also use the OR operator to combine conditions like in this example:

query {
  jobs(filter: "(JobStatus == 'Queued') OR (Locked == false)") {
    //…query
  }
}

This would return all Jobs that are in the “Queued” status, OR those that are not “Locked”.

As we covered in the Lists section, if we want to return Jobs in both the “Queued” and “Pending Dispatch” status that also aren’t locked, we should use a list for the JobStatus, and the AND operator for the Locked field.

query {
  jobs(filter: "(JobStatus IN ['Queued','Pending Dispatch']) AND (Locked == false)") {
    //…query
  }
}

We could even combine multiple conditions to get all Jobs that are either “Queued”, “Pending Dispatch”, or named “Skedulo”, as well as being unlocked by a query. For example:

query {
  jobs(filter: "(JobStatus IN ['Queued','Pending Dispatch'] OR Name == 'Skedulo' ) AND (Locked == false)") {
    //…query
  }
}

Now that we can handle multiple fields, let’s kick it up a notch with Filtering Multiple Objects.

Filtering Multiple Objects

The last thing we will cover in this post is filtering queries that traverse multiple objects. As we know, one of the powerful things about GraphQL is the ability to query for, and return, only the data we need in a single call.

Often, that will mean we need to get records from multiple objects. For example, what if we needed to get all of the Jobs and Job Allocations where the Job is Queued or Pending Dispatch, and the Job Allocation records that have a certain Resource assigned for a given month?

Before we get to handling all of that, let’s talk quickly about Relationships.

For example, if we want to get all the Job Allocations assigned to the Resource “Michael Wheeler” but we don’t have his Resource Id. Unfortunately there is no field on Job Allocation that contains the assigned Resource’s name.

Of course, there is a field that looks up to a Resource, and the Resource has a name field, so in this instance, we need to look at the “Name” field on the Resource Object and we do that by using dot notation. For example:

query {
  jobAllocations(filter: "Resource.Name == 'Michael Wheeler'") {
    edges {
      node {
        Name
        Start
        Resource {
          Name
        }
      }
    }
  }
}

So, we are introducing two new things here. The first one is Resource.Name, which is the dot notation mentioned above. We are referencing the Name field on the Resource Object. If you’ve written any SQL before, this likely is quite familiar to you.

The second one is returning details from the Resource object, but including it in the same way we would include another field. This would return something like:

{
  "data": {
    "jobAllocations": {
      "edges": [{
        "node": {
          "Name": "JA-0001",
          "Start": "2022-06-16T09:35:00.000Z",
          "Resource": {
            "Name": "Michael Wheeler"
          }
        }
      }]
    }
  }
}

The cool thing here is that we can add filters at any level, so, armed with this knowledge, we can achieve what we set out to do at the start of this section.

We will start with the query and then explain what is happening.

query {
  jobs(filter: "(JobStatus IN ['Queued','Pending Dispatch']) AND (Start >= 2022-06-01T00:00:00.000Z AND Start <= 2022-06-30T11:59:59.999Z)") {
    edges {
      node {
        Name
        JobStatus
        Start
        JobAllocations(filter: "Resource.Name == 'Michael Wheeler'") {
          Name
          Resource {
            Name
          }
        }
      }
    }
  }
}

The first filter should look fairly familiar. We are looking for Jobs that are “Queued” or “Pending Dispatch” with a list. We also have an AND condition to check that the Start is after 01/06/2022 AND before 30/06/2022.

After we have selected some fields (Name, JobStatus, Start), we select the relationship to JobAllocations. We can simply filter this the same way as we do in the parent query (Jobs). Within that query we can also select the fields from JobAllocations and Resource we wish to return. The resulting output would be something like:

{
  "data": {
    "jobs": {
      "edges": [{
        "node": {
          "Name": "JOB-0001",
          "JobStatus": "Pending Dispatch",
          "Start": "2022-06-16T09:35:00.000Z",
          "JobAllocations": [{
            "Name": "JA-0001",
            "Resource": {
              "Name": "Michael Wheeler"
            }
          }]
        }
      },
      {
        "node": {
          "Name": "JOB-0035",
          "JobStatus": "Queued",
          "Start": "2022-06-02T09:35:00.000Z",
          "JobAllocations": []
        }
      }]
    }
  }
}

We can see that both Job records match the first filter, and the first record also has a JobAllocation record matching the second filter.

The second record, “JOB-0035” doesn’t have any JobAllocations that match the filter so an empty list is returned.

As you can see, we used two filters and returned data from three different objects. Within our filters, we used the following Operators: “equals”, “less than or equal to”, “greater than or equal to”, “AND”, and “IN”. We also used a list and both the String and Instant data types.

Conclusion

Now you should have all the tools you need to master your GraphQL query filters,and get only the records you need! Stay tuned for part three in our GraphQL series, where we will experiment with record changes, aka data mutation. As always, we’d love to hear about what you’re building on the Skedulo Pulse Platform so please reach out to us on Twitter @SkeduloDevs or LinkedIn!

References

comments powered by Disqus