Kotlin Supabase查询：如何同时获取商品及子、主分类？

预期SQL示例（参考） #

首先明确关联逻辑对应的原生SQL，示例如下：

SELECT 
    i.*,
    sub_cat.* AS sub_category,
    main_cat.* AS main_category
FROM Item_Table i
-- 关联商品对应的子分类
INNER JOIN categories sub_cat ON i.category_id = sub_cat.id
-- 通过子分类的parent_id关联所属主分类
INNER JOIN categories main_cat ON sub_cat.parent_id = main_cat.id

如果存在子分类本身是顶级分类（无主分类）的场景，将第二个INNER JOIN改为LEFT JOIN即可保留这类商品数据。

解决方案1：使用Supabase Kotlin SDK查询构建器 #

Supabase Kotlin SDK支持多表JOIN与别名映射，无需编写原生SQL即可实现关联查询。

步骤1：定义对应的数据类

创建包含商品、子分类、主分类的DTO类，注意字段与查询结果的映射：

import io.github.jan.supabase.annotations.Column
import java.util.UUID

// 分类实体类
data class Category(
    val id: UUID,
    val name: String,
    val parent_id: UUID?
)

// 携带双分类信息的商品DTO
data class ItemWithCategories(
    val id: UUID,
    val name: String,
    // 补充你的商品表其他字段（如price、stock等）
    @Column("sub_cat") // 对应JOIN后的子分类别名
    val subCategory: Category,
    @Column("main_cat") // 对应JOIN后的主分类别名
    val mainCategory: Category? // 允许主分类为空时设为可空类型
)

步骤2：构建关联查询

通过两次join操作关联分类表，并指定别名区分子分类与主分类：

import io.github.jan.supabase.postgrest.Postgrest
import io.github.jan.supabase.postgrest.query.JoinType

// 假设supabase是你初始化完成的SupabaseClient实例
val supabasePostgrest: Postgrest = supabase.postgrest

val itemsWithCategories = supabasePostgrest.from("Item_Table")
    .select("""
        *,
        sub_cat(*), -- 查询子分类所有字段
        main_cat(*) -- 查询主分类所有字段
    """)
    // 第一次JOIN：关联商品对应的子分类
    .join(
        otherTable = "categories",
        joinType = JoinType.INNER,
        on = "Item_Table.category_id = categories.id"
    )
    .alias("sub_cat")
    // 第二次JOIN：通过子分类的parent_id关联主分类
    .join(
        otherTable = "categories",
        joinType = JoinType.INNER, // 需要保留无主分类的商品则改为LEFT
        on = "sub_cat.parent_id = categories.id"
    )
    .alias("main_cat")
    // 解码为自定义DTO
    .decodeList<ItemWithCategories>()

解决方案2：执行原生SQL查询 #

如果更习惯编写原生SQL，直接使用raw方法执行即可：

val itemsWithCategories = supabasePostgrest.raw("""
    SELECT 
        i.*,
        sub_cat.* AS sub_category,
        main_cat.* AS main_category
    FROM Item_Table i
    INNER JOIN categories sub_cat ON i.category_id = sub_cat.id
    LEFT JOIN categories main_cat ON sub_cat.parent_id = main_cat.id
""")
.decodeList<ItemWithCategories>()

注意事项 #

1. RLS权限检查：确保Item_Table和categories表的行级安全策略允许关联查询，比如categories表的策略要允许访问关联商品对应的分类数据。
2. 字段映射：如果SQL中使用了别名，必须在DTO类中用@Column注解指定对应别名，否则解码会失败。
3. 空值处理：若存在顶级分类（无主分类），需将mainCategory设为可空类型，并搭配LEFT JOIN避免过滤掉这类商品。

内容的提问来源于stack exchange，提问作者Pantastix