Supabase Kotlin join query

Question

I have a small Kotlin app which uses a Supabase database as backend. I have the problem that I have 2 relations in the tables I want to retrieve and I can't find a solution for my query.

I have a table that contains items, this is already displayed in the code given below. however, this table has a relation to a table that contains categories. This categories table has an ID, a name and a parent ID. this parent id refers to another category that serves as the main category. i would like to retrieve the sub and the main category with the item. is there a way to do this in a simple query?

i have included an example sql query that covers the problem here

SELECT 
    a.name,
    c1.name AS sub_category, 
    c2.name AS main_category,
FROM "Item_Table" a
LEFT JOIN categories c1 ON a.category_id = c1.id
LEFT JOIN categories c2 ON c1.parent_id = c2.id;

Mein Kotlin code in der app sieht aktuell so aus:

@Composable
fun ItemsList() {
    var itemsList by remember { mutableStateOf<List<Item>>(listOf()) }
    LaunchedEffect(Unit) {
        withContext(Dispatchers.IO) {
            itemsList = supabase.from("Item_Table")
                .select().decodeList<Item>()
        }
    }
    LazyColumn {
        items(
            itemsList,
            key = { it.id },
        ) { item ->
            Text(
                item.name,
                modifier = Modifier.padding(8.dp),
            )
        }
    }
}

aafanasev · Accepted Answer · 2025-04-04 05:59:32Z

1

Assuming you already have foreign keys between tables and models look like:

@Serializable
data class Category(
  val name: String,
  @SerialName("categories")
  val parent: Category? = null,
)

@Serializable
data class Item(
  val name: String,
  @SerialName("categories")
  val category: Category?,
)

The query may look like this:

supabase.from("Item_Table")
  .select(Columns.raw("name, categories(name, categories:parent_id(name))"))
  .decodeList<Item>()

This gives you:

Item(name=Item3, category=Category(name=Subcat1, parent=Category(name=Cat1, parent=null)))

answered Apr 4 at 5:59

aafanasev

1,1216 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Supabase Kotlin join query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related