SQLDelight and data classes with one to many relations in Kotlin

Question

I am trying to create a small app using Kotlin Multiplatform where i can save book titles and their authors but i'm struggling with the data classes and how to map them together so that i get the author with all of their books and the publish date.

CREATE TABLE book(
id INTEGER NOT NULL,
name TEXT NOT NULL,
publishDate INTEGER NOT NULL,
authorId INTEGER NOT NULL
)

CREATE TABLE author(
id INTEGER NOT NULL,
name TEXT NOT NULL
)

Here are my data classes:

@Serializable
data class bookEntity(
  id: Int,
  name: String,
  authorId: Int
)

@Serializable
data class authorEntity(
  id: Int,
  authorName: String
  books: List<bookEntity>
)

and my Query:

selectAuthors:
SELECT * FROM author
JOIN book ON book.authorId = author.id
WHERE book.authorId = author.id

i tried the following mapping but it didn't work:

private fun mapAuthor(
  id: Int,
  authorName: String,
  bookId: String,
  name: String,
  publishDate: Long
): Author(
  return Author (
    id = id,
    authorName = authorName,
    book = List<BookEntity>(
      id = bookId,
      name = name,
      publishDate = publishDate
    )
  )
)

How can i work with lists like this? Every help is appreciated!

Answer 1

I don't have more information about sqldelight. But in this case you can get both list separately and then merge them together like. Get all authers then get all books and then book list to group by auther id and then add that grouped booklist to auther object