Support filters, e.g. repos(license:'mit') or repos(stargazers__gt:10)

[simonw]

I can use essentially the same syntax as the table view's querystring arguments, e.g. https://github-to-sqlite.dogsheep.net/github/repos?stargazers_count__gt=10&license=mit

[simonw]

I think I'm going to support these with GraphQL arguments: https://graphql.org/learn/queries/#arguments

I'm a bit worried about supporting every Datasette querystring option using arguments. If a table has 10 columns and each columns supports 10 operations (lt, gt, lte etc) does that mean that the GraphQL field for it will need to support 100 arguments? If so, will all 100 be encoded into the introspection schema that GraphiQL loads on startup? Could end up getting pretty big.

[simonw]

If I don't support them all as explicit arguments I'll need to figure out an alternative way of representing them.

An ugly but functional way could be to encode them as strings. So you'd call something like this:

{
  repos(filters:"license=mit&stargazers__gt=10") {
    full_name
  }
}

This isn't exactly beautiful but it does mean I can support ALL of the filters that Datasette provides without needing to define hundreds of separate GraphQL arguments per table.

I think GraphQL arguments can take a list of strings - so maybe this would be an option:

{
  repos(filters:["license=mit", "stargazers__gt=10"]) {
    full_name
  }
}

[simonw]

Maybe even a list of [column, operator, value] lists?

{
  repos(filters:[["license", "=", "mit"], ["stargazers", ">", "10"]]) {
    full_name
  }
}

[simonw]

This might work:

    repos = Field(Repo, args={"filters": graphene.List(graphene.List(graphene.String))})

[simonw]

Or maybe define my own type called Filter which bundles column, operator, value.

[simonw]

I'm tempted to implement this by converting the graphQL call into an internal ASGI request through Datasette with the correct querystring parameters, so I don't have to re-implement the existing filter logic (and keep it in sync with future changes to Datasette's own table filters).

[simonw]

Comparing this:

{
  repos(filters: [["license", "=", "mit"]]) {
    full_name
    owner {
      html_url
    }
  }
}

With this:

{
  repos(filters:["license=mit"]) {
    full_name
    owner {
      html_url
    }
  }
}

I think I prefer the latter.

[simonw]

I'm going to start with a list of strings, like that second example. I may reconsider this later.

[simonw]

Or maybe even specify filters: as taking any of the following:

• A string, e.g. "license=mit"
• A list of strings, e.g.["license=mit", "stargazers>10"]
• A list of three-tuples, e.g. [["license", "=", "mit"], ["stargazers", ">", "10"]]

First step: get it working with a list of strings.

[simonw]

I could use Datasette's datasette.filters module for this - it's not a documented part of the API so there are no guarantees it won't change, but there's also a chance I'll make it extensible via plugin hooks which would cause it to become stable: https://github.com/simonw/datasette/blob/7ca8c0521ac1ea48a3cd8d0fe9275d1316e54b43/datasette/filters.py

[simonw]

In [18]: f = Filters([["license", "mit"], ["score__gt", "10"]])                                                                     

In [19]: list(f.selections())                                                                                                       
Out[19]: [('license', 'exact', 'mit'), ('score', 'gt', '10')]

In [20]: f.build_where_clauses('mytable')                                                                                           
Out[20]: (['"license" = :p0', '"score" > :p1'], {'p0': 'mit', 'p1': 10})

[simonw]

        from_sql = "from {table_name} {where}".format(
            table_name=escape_sqlite(table),
            where=("where {} ".format(" and ".join(where_clauses)))
            if where_clauses
            else "",
        )