SQL一对多关联查询问题：如何获取含关联用户列表的城市对象

你当前的问题根源在于内连接（inner join）会将每个关联的User与City生成一行结果，直接map会得到多个重复的City对象，而.singleOrNull()因为结果行数大于1，所以返回null。以下是两种可行的解决方法：

方法一：分步查询（推荐，直观易维护） #

先查询目标City的基础信息，再单独查询该City下的所有User，最后将User列表组装到City对象中：

suspend fun getOne(id: String): City? {
    return dbQuery {
        // 1. 查询单个City的基础数据
        val cityRow = CityTable
            .selectAll()
            .where { CityTable.id.eq(id) }
            .singleOrNull() ?: return@dbQuery null
        
        // 2. 查询该City关联的所有User（假设User表有cityId外键关联City.id）
        val users = UserTable
            .selectAll()
            .where { UserTable.cityId.eq(id) }
            .map {
                User(
                    it[UserTable.id],
                    it[UserTable.name],
                    // 填充User的其他字段
                )
            }
        
        // 3. 组装包含User列表的City对象
        City(
            cityRow[CityTable.id],
            cityRow[CityTable.name],
            users = users, // 假设City数据类定义了users参数
            // 填充City的其他字段
        )
    }
}

方法二：分组聚合查询（单次SQL完成） #

通过分组聚合将同一City的User合并到列表中，结合数据库的聚合能力实现（不同数据库语法略有差异），以Exposed为例：

suspend fun getOne(id: String): City? {
    return dbQuery {
        // 先处理有User关联的情况
        val cityWithUsers = (CityTable innerJoin UserTable)
            .selectAll()
            .where { CityTable.id.eq(id) }
            .groupBy(CityTable.id)
            .map { groupRow ->
                val users = UserTable
                    .selectAll()
                    .where { UserTable.cityId.eq(groupRow[CityTable.id]) }
                    .map { User(it[UserTable.id], it[UserTable.name]) }
                
                City(
                    groupRow[CityTable.id],
                    groupRow[CityTable.name],
                    users = users
                )
            }
            .singleOrNull()
        
        // 处理无关联User的City，返回空User列表
        cityWithUsers ?: CityTable
            .selectAll()
            .where { CityTable.id.eq(id) }
            .map { City(it[CityTable.id], it[CityTable.name], users = emptyList()) }
            .singleOrNull()
    }
}

关键注意事项 #

• 确保User表存在指向City的外键（如cityId），且关联条件正确
• 分步查询虽为两次SQL，但可读性强，性能在常规场景下足够；若需单次SQL，可使用数据库的聚合函数（如PostgreSQL的array_agg）直接聚合User字段
• 需处理City无关联User的边界情况，避免返回null或缺失User列表

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