Entity List within another entity

[nmoberg]

I have an entity Groups and then I have another entity Users that belong to those groups. There are three different tables in the database that are associated with these entities. I have:
groups_table
users_table
groups_users_relationship

So basically I have group 1. This groups has user A, B, and C associated with that group. So when I join all three tables, I get three separate records, which is to be expected in the SQL. But now comes the part I am struggling with. I want to deserialize them into one single group object that has a list of users.

data class Groups(
    val id: Int,
    val name: String,
    var members: List<Users>?
)

Here is the query that I am using:

  val groupResults = database
            .from(GroupsTable)
            .leftJoin(GroupsUsersRelationshipTable, on = GroupsUsersRelationshipTable.groupId eq GroupsTable.id)
            .leftJoin(UsersTable, on = UsersTable.UserId eq GroupsUsersRelationshipTable.UserId)
            .select()

Current Result would look something like this:

Group_id          Group_Name              User_id             User_name
1                  Test Group                 A                 Test User 1
1                  Test Group                 B                 Test User 2
1                  Test Group                 C                 Test User 3

Right now I have it returning:

List{
     Groups(
        val id = 1
        val name = "Test Group"
        var members: List(User(A, "Test User 1))
    ),
     Groups(
        val id = 1
        val name = "Test Group"
        var members: List(User(B, "Test User 2))
    ),
     Groups(
        val id = 1
        val name = "Test Group"
        var members: List(User(C, "Test User 3))
    )
}

I want to return

List {
      Groups(
          val id = 1
          val name = "Test Group"
          var members: List(User(A, "Test User 1), User(B, "Test User 2"), User(C, "Test User 3"))
      )
}

Is there an easy way to do this? I have gone through the documentation, and it does not appear like it is explained, but it is possible that I missed it.

I know there is a hacky way that I could do it within kotlin, but I would rather use the framework if it has it built in.

TIA

[vincentlauvlwj]

val groups = database.groups.filter { ... }.toList()

val groupIds = groups.map { it.id }
val allUsers = database
    .from(Users)
    .innerJoin(GroupUserRelationships, on = ...)
    .where(GroupUserRelationships.groupId.inList(groupIds))
    .map { row -> Users.createEntity(row) }

return groups.map { group -> 
    return Group(
        id = group.id,
        name = group.name,
        members = allUsers.filter { it.groupId == group.id }
    )
}