Tutorial: Query Filtering and Ordering in ComposeDB
ComposeDB's query filtering and ordering features offer powerful ways to manipulate and retrieve your data
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
enumtype: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 newstatusvalue, and we’ll make thestatusmandatory 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. @createIndexdirective 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:
- Implicit
andfor range filtering - Filtering by options in a provided list
- Combining multiple filters
- 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!