Update Doku for nested select

[pfennig42]

Hey, since I just spend multiple hours on this problem, I thought it could be worth taking it as example.

When making a nested request, e.g. select=user!inner (name), id&user.name=eq.foo, there could be two errors. First that it is not parsable. The reason would be the space after the inner.

The second problem is, that it is not possible to use a nested string like "user.name", because the dot is a reserved char. As consequence the library will change it to ""user.name"" and the request fails with the statement: 42703: column Foo.user.name does not exist

A simple solution for this would be to use backticks instead of using a double quotation mark.

I think, that updating the docs to make this understandable could lower much pain

[Ra0k]

Hey @pfennig42 , did you manage to filter results via joined tables? I have the same issue, and it drives me crazy. Would you mind to show me your solution? :)

[pfennig42]

Hi @Ra0k,

I just grabbed this snippet. Does this help you?

supabasecall := supabase.DB.From(table).Select("item,customer!inner(id, short_name, full_name)").Filter(`customer.id`, "eq", "0")

I filled the variables by heart, so I am only 80% sure it works. I would appreaciate the feedback!

[Ra0k]

Hi @pfennig42,

Thank you for the help and the quick response! Unfortunately, for me, the outcome is the same:

if err := supaClient.DB.From("Workspace").Select("id, name, WorkspaceUser!inner(user_id, role)").Filter(`WorkspaceUser.role`, "eq", "admin").Execute(&results); err != nil {
    fmt.Println(err)
    http.Error(w, "internal server error", http.StatusInternalServerError)
    return
}

Output: 42703: column Workspace.WorkspaceUser.role does not exist

Ps: I only have camel case table names because I was not sure if _ causes problems or not.

[pfennig42]

The only thing I could think about is removing the spaces inside the querys?

Otherwise I would trace the error until Postgres. There should be the translation error

[Ra0k]

I just wonder why it works for you. Do you use the latest version of supabase-go?

In my version (latest). Everything is sanitized.

func SanitizeParam(param string) string {
    if strings.ContainsAny(param, reservedChars) {
        return fmt.Sprintf("\"%s\"", param)
    }
    return param
}

func (b *FilterRequestBuilder) Filter(column, operator, criteria string) *FilterRequestBuilder {
    if b.negateNext {
        b.negateNext = false
        operator = "not." + operator
    }
    b.params.Add(SanitizeParam(column), operator+"."+criteria)
    return b
}

As far as I understand, it should always sanitize the input so it should always break the join filtering.

[Ra0k]

Okay, I figured out what happened. Since you opened this issue, this dependency was changed and probably has caused to break this feature. The same happened to https://github.com/nedpals/supabase-go/issues/28

[pfennig42]

Alright. Thanks for letting me know!

[Fritte795]

@Ra0k There is currently a known problem with escaped characters in filter functions. Compare with #28.
A temporary solution is to use the generic Filter() function. This function behaves differently and worked at least for me.

The problem lies indeed not in this repository but here. Currently two main strategies are considered after talking to @nedpals:

1. Directly implement the postgrest-go functionality in this repository to speed up development. We would not need releases in two repositories to take effect. a. We would need to fix the behavior of sanitizing to mimic the official postgrest-js repository .
2. Use another postgrest-go library that is in a more advanced state and is better maintained.

[whoiscarlo]

@Fritte795 forgive me the naivety of my question but is it possible to just take the nedpals/postgres-go repo and importing it into this repo and creating a PR?

[Fritte795]

It would be possible, see 1.
We decided to continue development in our own repository as it gives us the opportunity to iterate faster and manage the repository ourselves. You can find the repositories here: Allgeier Secion.

[stav]

supabasecall := supabase.DB.From(table).Select("item,customer!inner(id, short_name, full_name)").Filter(`customer.id`, "eq", "0")

The filter is for the outer table:

Filter(`table.id`, "eq", "0")

So would this be right?

Filter(`id`, "eq", "0")

As long as you setup the foreign key relationships in the database it should work.