How can I create a select query that uses subqueries?

[giannism13]

I need to implement the following query
select * from "Fields" where id in ( select field_id from "Patient_profile" where id = 1 );
How can I do something like this?

[jan-tennert]

Not sure how your tables relate to each other. Take a look at https://supabase.com/docs/guides/api/joins-and-nesting.
If the syntax for JS is:

const { data, error } = await supabase.from('countries').select(`
  id, 
  name, 
  cities ( id, name )
`).eq('cities.id', 1)

Then it would be like this for Kotlin:

val countries = Columns.raw("""
    id, name, cities(id, name)
""".trimIndent())
val country = client.postgrest["countries"].select(columns) {
    eq("cities.id", 1)
}

[giannism13]

Correct me if I'm wrong but your proposal essentially is a join, which is different from what I'm trying to do. I did a little digging and it seems that Supabase does not support subqueries. Instead I had to use a function and rpc.

[jan-tennert]

Correct me if I'm wrong but your proposal essentially is a join, which is different from what I'm trying to do. I did a little digging and it seems that Supabase does not support subqueries. Instead I had to use a function and rpc.

Not an expert on this field, but great to know that you solved your problem!