`LIKE`/`ILIKE`/`NOT LIKE` operator support for filter types

[Flambe]

Summary

Add support for like/ilike/not like/not ilike filtering to get a collection of records that matches the given filter string.

E.g. filter: { label: { like: "Egg%" } }

Rationale

Filtering on string partials is a common use case for us.

[kav]

Not my MR but any reason #194 didn't end up getting merged?

[Flambe]

Not my MR but any reason #194 didn't end up getting merged?

@kav It was my MR & it was still missing things like tests and I hadn't gotten around to sorting those out yet. I'm guessing that they don't want stagnent draft MRs left in open so they closed it.

[kav]

Need a hand taking it the rest of the way? I too would love LIKE and ILIKE support

[Flambe]

Need a hand taking it the rest of the way? I too would love LIKE and ILIKE support

Yeah that's absolutely cool with me! My changes have been the bare minimum to get it working so far and only cover a simple like and ilike filter. Anything you can do to help will be greatly appreciated.

[kav]

Looking at the NOT case is there a consensus on patterns? Here are the three I've encountered. Also happy to defer NOT to another day as LIKE/ILIKE is all I need for my use case.

Dgraph style

filter: { not: { label: { like: "Egg%" } } }

https://dgraph.io/docs/graphql/queries/and-or-not/

PostGraphile Connection Filter style

filter: { label: { notLike: "Egg%" } }

https://github.com/graphile-contrib/postgraphile-plugin-connection-filter

PostgREST style

filter: { label: { not: { like: "Egg%" } } }

https://postgrest.org/en/stable/api.html?highlight=ilike#logical-operators

There seems to be pretty strong guidance in the GraphQL community that there are dragons here but I'll leave it up to someone with a deeper understanding of the architecture to comment on that. Adding here as it might drive decisions on the above.

🚨BEWARE🚨: adding powerful generic filtering capabilities to your GraphQL API is strongly discouraged, not just by Benjie (the maintainer of PostGraphile) but also by Lee Byron (one of the inventors of GraphQL) and various other experts in the GraphQL ecosystem. It is strongly advised that you add only very specific filters using one of the techniques above (and that you make their inputs as simple as possible) rather than using a generic filtering plugin like this. Not heeding this advice may lead to very significant performance issues down the line that are very hard for you to dig your way out of. https://www.graphile.org/postgraphile/filtering/

[Flambe]

My original plan was to go the PostGraphile way since it's similar to the existing options like neq, but the PostgREST style is definitely closer to how it works on the SQL query side. But it's probably something the maintainer should decide on depending on how they want to progress? (Same with the warning about powerful generic filtering)

[kav]

Paging @olirice for comment on the above when you've got a second. Happy to defer the not case if you don't have one

[olirice]

thanks for the ping!

I'm leaning away from exposing string pattern filters

String matching is very flexible and notoriously difficult to index for. With the exception of functional indexes exactly matching your query, and some prefix filters, each query would be guaranteed to check every record in the table. That makes results sluggish at moderate scale and can be cpu intensive on low end hardware (like supabase free-tier)

like/ilike filters are also SQL specific standards that aren't common in GraphQL or web exposed APIs generally. I think with the recent addition of in filtering support (should hit the platform within 2 weeks) we might already be at the sweet spot for semi-flexible/performant string matching

[kav]

Given the above how are you imagining handling search use cases there? trigrams and IN? I've had great success with LIKE/ ILIKE and start of string matches. I do of course have to shape my index for my search cases but that's basically always true. I suppose folks could write poorly optimized GraphQL queries by LIKE-ing on fields I don't have indexed

[olirice]

how are you imagining handling search use cases there?

The the short term, search would be limited to filtering on a list of strings. Longer term we may

• add something like a startsWith: operator for text types
• allow like/ilike and make it opt-in behind a comment directive like totalCount

[kav]

That makes sense. I can look at the two longer term items as I build on Steve's existing work

[Flambe]

If anyone is looking to use like/ilike filtering with the new rust version of the library, I've updated my fork to include them and some basic steps on how to compile it yourself: https://github.com/Flambe/pg_graphql/

[kav]

@olirice any chance on getting the current rust version from @Flambe's fork in? I know I owe any tests you may need if so. Also slightly unrelated any chance you have not in/nin in the pipeline?

[Flambe]

If there is still a consideration for an opt-in version then that'd be perfect! (startswith wouldn't be enough to cover our usecase) I would have attempted it myself already but I need to get round to learning some more rust first.

[olirice]

@kav not in support sounds reasonable to me. I opened a new issue #311 to track it separately so we can keep this one narrowly focused on String-like types

---

The delay on this is that we haven't had to take a hard position on defaulting to flexibility vs scalability as the stance for the project. To be consistent with supabase+postgrest I think flexibility with options to "harden" the API is how we should play it

I've opened #310 with the beginnings of String filtering support. There are todos for like and like. That PR will also include a comment directive set at the schema/table/column level for generically disabling filter ops as needed

Will aim to complete and merge it next week so its in the next release

[Flambe]

I'd love parity with Supabase JS, but yeah I fully understand the concerns you've mentioned before.

Splitting it into opt-in/opt-out parts sounds really useful for me at least!

[kav]

🎉