Tutorial: Query Filtering and Ordering in ComposeDB

Introduction

In this tutorial, you'll learn how to make the most out of the query filtering and ordering capabilities in ComposeDB. By the end, you will know how to:

• Set up type validation directives and create indices for specific fields
• Use value and logical conditions for querying
• Sort the returned results by specific fields

Prerequisites

• Basic understanding of GraphQL
• An active ComposeDB environment (make sure to install the latest versions of Ceramic CLI and ComposeDB CLI)

The Model

Before we can start querying, we need to have data models defined in ComposeDB, along with some data to query, filter, and sort.

You can use a simple social app from the great Getting Started With ComposeDB on Ceramic tutorial, create a model from scratch, or work on your existing data.

For simplicity, I’ll create a new model, adapt it to support filtering, add some data to it, and demonstrate all the filtering and sorting capabilities.

Defining a Posts model

A simple Posts model from the “Getting Started” guide looks like this:

type Posts @createModel(accountRelation: LIST, description: "A simple Post") {
  body: String! @string(minLength: 1, maxLength: 100)
  edited: DateTime
  created: DateTime!
  profileId: StreamID! @documentReference(model:"BasicProfile")
  profile: BasicProfile! @relationDocument(property: "profileId")
}

It’s good enough to capture and store data, but let’s make it more robust, add a few fields and get it ready for filtering and sorting.

Create indices and add fields

Query filtering in ComposeDB allows you to create queries that return a subset of data that matches specified criteria. This functionality helps you narrow down the search by specifying the search parameter.

To filter queries, you will need to create an index for the corresponding field. An index is a data structure that allows the database to find documents matching the filter criteria more quickly.

In our Posts type, for example, we have created indexes for status, tag, created, rating, and edited fields using the @createIndex directive as shown below:

@createIndex(fields: [{ path: "status" }])
@createIndex(fields: [{ path: "tag" }])
@createIndex(fields: [{ path: "created" }])
@createIndex(fields: [{ path: "rating" }])
@createIndex(fields: [{ path: "edited" }])

Our model now looks like this:

enum PublicationStatus {
    DRAFT
    PUBLISHED
    ARCHIVED
}
type Posts
    @createModel(accountRelation: LIST, description: "A simple Post")
    @createIndex(fields: [{ path: "status" }])
    @createIndex(fields: [{ path: "tag" }])
    @createIndex(fields: [{ path: "created" }])
    @createIndex(fields: [{ path: "rating" }])
    @createIndex(fields: [{ path: "edited" }]) {
    body: String! @string(minLength: 1, maxLength: 100)
    tag: String! @string(minLength: 1, maxLength: 100)
    rating: Float @float(min: 0, max: 10)
    status: PublicationStatus!
    edited: DateTime
    created: DateTime!
}

You’ll notice a few additions here:

• A new enum type: PublicationStatus. We know that the publication status can have only a few possible options. We’re enumerating them all here and then we’ll use that as a type that we can apply to variables. We’ll use it for the new status value, and we’ll make the status mandatory by adding an exclamation point to the type
• New fields: tag, rating, status, edited. A good social app should have more than just short entries. With these fields, we’re adding an option to tag and rate posts, have different publication statuses, and an option to see which entries were edited and when.
• @createIndex directive on five fields: status, tag, created, rating, edited. These add all the necessary indices. We can now filter and sort on all of these.

Add data

We have a model now but we don’t have any data yet. We can’t do much filtering if we don’t have anything to filter. Let’s add some posts.

mutation CreatePosts{
  createPosts(input: {
    content: {
      body: "ComposeDB Launch",
      tag: "announcement",
      rating: 9.9,
      status: PUBLISHED,
      created: "2023-02-28T12:00:00Z"
      edited: "2023-03-01T12:00:00Z"
    }
  }){
    document{
      body
      tag
      rating
      status
      created
    }
  }
}

This mutation adds our first post, a ComposeDB launch post, with an announcement tag, a 9.9 rating and a non-zero edited field.

Let’s add 20 more posts so that we have enough to play with. You can copy them from this gist and use GraphiQL to add them to your Ceramic node.

Query Filtering

Now that we have a way to filter and have the data to filter on, let’s do some filtering.

You can filter by value or with logical conditions.

Filtering by value is based on a single field value and we can do a lot with it:

• check if it’s null - isNull: Boolean
• check if it’s equal or not equal to a value - equalTo: (value), notEqualTo: (value)
• check if it is or isn’t in a list of values: in: […values], notIn: […values]
• check if it’s greater or smaller than a certain value: lessThan: (value), lessThanOrEqualTo: (value), greaterThan: (value), greaterThanOrEqualTo: (value)

Logical conditions combine multiple conditions using and, or, or not.

Filtering Examples: Filtering by Value

Let’s see all the ways we can filter our posts.

Filter by publication status

A query:

query FilterByStatus($input: PostsFiltersInput!) {
  postsIndex(filters: $input, first: 50) {
    edges {
      node {
        body
        created
        rating
        tag
        status
      }
    }
  }
}

With the input to show only posts with DRAFT status:

{
  "input": {
    "where": {
      "status": {
        "equalTo": "DRAFT"
      }
    }
  }
}

If you’re using GraphiQL to run your queries, you need to write the query in the query section (1) and the input data in the variables section below (2).

You can then run the query with the filter by pressing a play button and you get the results on the right (3).

As you can see, only the posts with DRAFT status are showing up. The filter works!

Let’s do more filtering.

Filter by rating

Show poorly-rated posts, with rating less than 7.5

{
  "input": {
    "where": {
      "rating": {
        "lessThan": 7.5
      }
    }
  }
}

Filter by date

Posts from earlier than Jan 10, 2023.

{
  "input": {
    "where": {
      "created": {
        "lessThan": "2023-01-10"
      }
    }
  }
}

Filter by edited status

Show me the posts that have been edited (their edited field is not null).

{
  "input":{
    "where": {
      "edited":{
        "isNull": false
      }
    }
  }
}

Filter by tags

Only the posts tagged with Decentralization, Future, or both.

{
  "input":{
    "where": {
      "tag":{
        "in": ["Decentralization", "Future"]
      }
    }
  }
}

Filtering by Logical Conditions

Logical conditions combine multiple conditions. These could be either for a single field, or for multiple fields. The syntax is the same.

Filter by rating range

This is filtering on the same field but it needs combining two where filters with an and operator, indicating that both conditions must be true. If you know GraphQL, this syntax should be familiar.

{
   "input":{
      "and":[
         {
            "where":{
               "rating":{
                  "greaterThanOrEqualTo":2.2
               }
            }
         },
         {
            "where":{
               "rating":{
                  "lessThan":8.8
               }
            }
         }
      ]
   }
}

Filter by date range

Show me only the posts from January of 2023 (note the greaterThanOrEqualTo operator).

{
   "input":{
      "and":[
         {
            "where":{
               "created":{
                	"greaterThanOrEqualTo":"2023-01-01"
               }
            }
         },
         {
            "where":{
               "created":{
                	"lessThan":"2023-01-31"
               }
            }
         }
      ]
   }
}

An alternative way of filtering by range

The previous two examples of filtering by range used an explicit and: you started with and at the top and supplied two where queries.

This can also be achieved with an implicit and, where you supply two conditions and the and is implied.

The two examples above can be rewritten like this:

{
   "input":{
      "where":{
         "rating":{
            "greaterThanOrEqualTo":2.2,
            "lessThan": 8.8
        }
      }
   }
}

{
   "input":{
      "where":{
         "created":{
            "greaterThanOrEqualTo":"2023-01-01",
            "lessThan":"2023-12-31"
        }
      }
   }
}

In both cases, the results are the same with both an implicit and an explicit and.

Filter by multiple fields

I want to clean up some posts. Show me the posts with DRAFT or ARCHIVED status, created before March 1, 2023.

{
   "input":{
      "and":[
         {
            "where":{
               "status":{
                	"in": ["DRAFT", "ARCHIVED"]
               }
            }
         },
         {
            "where":{
               "created":{
                	"lessThan":"2022-03-01"
               }
            }
         }
      ]
   }
}

Query Ordering

Now that we have focused on the posts we wanted, it would be nice to be able to sort the results, too.

For that, we need to define an additional variable: $sortinginput, include it in the query, and then add it next to the $input variable.

{
   "input":{
      "and":[
         {
            "where":{
               "created":{
                	"greaterThanOrEqualTo":"2023-01-01"
               }
            }
         },
         {
            "where":{
               "created":{
                	"lessThan":"2023-12-31"
               }
            }
         }
      ]
  },
  "sortinput":{
      "created":"DESC"
   }
}

Now the posts are filtered, only showing the ones from 2023, and they’re also all sorted by created date, in a descending order, with the newest ones at the top.

Putting it all together

Here's a sample query that uses multiple filtering options and a sorting option on top, to show you what's possible:

A lot is going on here:

1. Implicit and for range filtering
2. Filtering by options in a provided list
3. Combining multiple filters
4. Sorting the output

And that’s it. You’re now filtering and sorting at will! Congratulations 🎉

Conclusion

ComposeDB's query filtering and ordering features offer powerful ways to manipulate and retrieve your data.

With the steps in this tutorial, you can now efficiently filter and order your data based on specific criteria, enabling you to build more robust applications.

Dive deeper into the documentation for more advanced filtering and ordering techniques.

If you have any questions about filtering and ordering, jump into our community forum. See you there!