Hey! Is it possible to filter a select by a joined...

# supabase-kt

[Nacho Ruiz Martin]

Hey!
Is it possible to filter a select by a joined property?

Example:

Table Event (id, name, stadium)
Table Stadium (id, name)`
//Pseudo SQL
SELECT * FROM Event e
  INNER JOIN Stadium s
    WHERE e.stadium = s.id
  WHERE s.name ILIKE "query" <--- How to do this?

supabase.from("event").select() {
    filter {
       Event::stadium::name like "%la%" <- Not working
       //or
       like("stadium.name", "%la%")  <- Working?
    }
}

[Jan]

It seems like AnyType::property is a different type than AnyType::property::nestedProperty and there are no methods defined for that.
AnyType::property is of type KProperty1<AnyType, T> and AnyType::property::nestedProperty only KProperty0<T>
So we would have to define similar methods like this

[Nacho Ruiz Martin]

Will take a look and open a PR 😉

[Jan]

I'm not sure however if you can get the "upper" property name, in your case stadium

[Jan]

But for your question, these joins are supported by postgrest: https://supabase.com/docs/guides/database/joins-and-nesting

[Colton Idle]

dumb question. but is postgrest different from postgres or is that just a typo?

[Nacho Ruiz Martin]

Oh, It's not a typo, it's PostgREST. To build a rest API on top of Postgres.

[Nacho Ruiz Martin]

Thanks, Jan, I'll take a look!

[Colton Idle]

oh. TIL. is postgrest a postgres thing or a supabase thing?

[Nacho Ruiz Martin]

A Postgres thing!

[Nacho Ruiz Martin]

For info: https://postgrest.org/en/v12/

[Nacho Ruiz Martin]

Hey @Jan,
So, if I understood the docs correctly, this should be the way, right?

return supabase
            .from(tableName)
            .select(
                Columns.raw(
                    """
                    id,
                    time,
                    league(*),
                    home_team!inner(*),
                    visitor_team!inner(*)
                    """.trimIndent().replace("\n", "")
                )
            ) {
                val pattern = "%$query%"
                filter {
                    and {
                        Event::time gte todayAtMidnight
                        or {
                            ilike("home_team.name", pattern)
                            ilike("visitor_team.name", pattern)
                        }
                    }
                }
            }
            .decodeList()

But the following error is thrown:

BadRequestRestException: "failed to parse logic tree ((time.gte.2024-04-11T00:00:00Z,or(home_team.name.ilike.%Gij%,visitor_team.name.ilike.%Gij%)))" (line 1, column 48) (unexpected "a" expecting "not" or operator (eq, gt, ...))

[Jan]

So you don't need that and in your filter, because all filters must match by default. But this is not the error, the error is there because there is a . in your logical expression filter (ilike("home_team.name") ). The dot is used for separating filters when in a logical expression, hence the error. Normally you would just specify the referencedTable parameter (which doesn't exist for or and and I just realized, opened a PR), so you can filter foreign columns in a logical expression like this:

or(referencedTable = "home_team") {
    ilike("name", pattern) //The name from home_team will be filtered on
}

however you have two different tables, I'm not sure if that would work even with the referencedTable parameter.

[Jan]

Note: If you are not in a logical expression, you can use the dot

[Jan]

Maybe a database function could help there, as I haven't worked with such complex queries in PostgREST

[Nacho Ruiz Martin]

such complex queries

Is it a complex query, though? Maybe I’m doing something odd since I’m not an expert in SQL by any means, but I just want to query a table and filter by a joined table’s column.
Anyway, the database function sounds like a good idea 🙂.
Thanks for that!

[Nacho Ruiz Martin]

@Jan Maybe this is something missing in kt SDK? The JS one seems able to do it (not tested on my side).
https://supabase.com/docs/reference/javascript/select?example=filtering-through-referenced-tables

[Nacho Ruiz Martin]

Ah, nah, you’re right. The problem here is that is the same column that is joined twice and that the filter is inside an or, so the postgrest API doesn’t allow this.
A database function it is!