Kotlin: Using filters

Filters allow you to only return rows that match certain conditions.

Filters can be used on select(), update(), and delete() queries.

You can use two different types for applying filters:

eq("country_id", 1)

And using a class property:

City::countryId eq 1

As you can see on the property syntax: the name of the countryId gets converted to country_id.

By default, this is done by converting camel case to snake case, but you can customize this by changing the propertyConversionMethod in the Postgrest Config

If a database function returns a table response, you can also apply filters.

Examples

Applying a filter block

supabase.from("cities").select(columns = Columns.list("name", "country_id")) \{
    filter \{
        City::name eq "The Shire"
        //or
        eq("name", "The Shire")
    \}
\}

Multiple filters on one column

supabase.from("cities").select(columns = Columns.list("name, country_id")) \{
    filter \{
        and \{ //when both are true
           City::population gt 40000
           City::population lt 700000
        \}
        or \{ //when either one of the filters are true
           City::name eq "London"
           City::name eq "Berlin"
        \}
    \}
\}

Filter by values within a JSON column

supabase.from("users").select \{
    filter \{
        eq("address->postcode", 90210)
    \}
\}

Filter Foreign Tables

val columns = Columns.raw("""
    name,
    cities!inner (
        name
    )
""".trimIndent())
supabase.from("countries").select(
      columns = columns
) \{
    filter \{
        eq("cities.name", "Bali")
    \}
\}