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),
            )
        }
    }
}