Syntax for filtering documents

[kodumbeats]

Client Filters for Documents

We're in the planning phases of refactoring our database, and we need your help. We aim to create the best developer experience while we build Appwrite, and we want document filtering to reach that same ease-of-use as the rest of our stack.

We've talked at length about the syntax of these filters - while we've come up with a few options, your 👀 may see things we haven't.

What's your experience?

1. What's your favorite filtering approach? We've looked at many other implementations, including:

• Firestore
• MongoDB
• PouchDB
• IBM Cloudant

2. What's your biggest headache when filtering documents?

3. Which of our approaches looks the best?

4. What have we missed?

Our Approaches

To better demonstrate our filtering approaches, assume we have the following data structure:

type Movie = {
    title: string;
    year: number;
    published: boolean;
    director: Director;
    actors: Actor[];
}

type Director = {
    name: string;
}

type Actor = {
    name: string;
}

With this schema in mind, we've used three approaches to fetch data in different scenarios, from simple to more advanced:

Simple

Movie with title 'Iron Man'

    db.listDocuments($movieCollection, new Filter()
        .equal('name', 'Iron Man')
    )
    
 
 
    db.listDocuments($movieCollection, { 
      "title: { "equals": "Iron Man" } 
    })
      
  
  
    val filter = new Filter()
          .equal('name', 'Iron Man')
    db.listDocuments($movieCollection, filter)*

• Only so many ways to write simple filters. 🙂

Intermediate

Movies published between 1999 and 2001

    db.listDocuments($movieCollection, new Filter()
        .equal('published', true)
        .greaterEquals('year',1999).lesser('year',2001)
    )



    db.listDocuments($movieCollection, {
        "year": {
            "greaterEquals": 1999,
            "lesser": 2001
        }, 
        "published": {
            "equals": true
        }
    })



    val filter = new Filter()
        .equal('published', true)
        .between('year',  include(1999), exclude(2001)) // year >= 1999 && year <2001
    db.listDocuments($movieCollection, filter)

Advanced

Movies that actors "Brad Pitt" and "Angelina Jolie" performed together, published after 2000, not Directed by "Michael Bay"

    db.listDocuments($movieCollection, new Filter()
        .containsAll("actors.name", ["Brad Pitt",  "Angelina Jolie"])
        .equal("published",true).greater("year",2000)
        .notEqual("director.name","Michael Bay")
    )



    db.listDocuments($movieCollection, {
        "actors":  {
            "name":{
                "containsAll": ["Brad Pitt", "Angelina Jolie"]
            }
        },
        "published": {
            "equals": true
        },
        "directors": {
            "equalsNot": "Michael Bay"
        },
        "year": {
            "greater": 2000
        }
    });



    val filter = new Filter().equal('actors.name', and('Brad Pitt', 'Angelina Jolie'))
        .greaterThan('published', include('2000')) 
        .notEqual('director.name', 'Michael Bay')
    db.listDocuments($movieCollection, filter)

[eldadfux]

Again and as discussed earlier this week, for me the first approach is by far the most intuitive, desired, and the one that matches our simplicity first approach.

• I think the syntax is clean and short
• I think its more intuitive to have the query driven syntax rather than attribute driven syntax - because we set filters and not necessarily think of a query as something grouped by attributes, even if that's the end result its not how we would think of it in most cases.
• The include/exclude method is not very clear to me and also is part of the global scope which is a very bad practice
• The JSON based syntax is really problematic (or at least add unwanted complexity) in the context of typed script languages, and is not friendly. This is also going to be a problem to match our desire to have consistent syntax across SDK as much as possible.
• The JSON object syntax also looks much longer

[TorstenDittmann]

Btw, we should also consider this Filter to be usable with something that is not a Collection.

We should have consistent filters across Storage, Users, co.

[christyjacob4]

@eldadfux I would not be too concerned about global functions because they can be imported and scoped specifically to the file in which it is being used . Consider this

// Typescript / JS
import { includes } from 'appwrite/filters'
import { includes as Something } from 'appwrite/filters'

// Java or Kotlin
import appwrite.filters.includes;

[eldadfux]

@christyjacob4 agree, but if we can avoid adding multiple statements for a simple filter I would go that way 😉

[christyjacob4]

@eldadfux Are you talking about reducing the number of imports?

[eldadfux]

Yes, but definitely not my main concern as stated above.