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!

Scheller

Answer 1

The ON clause of the JOIN is the condition which links both tables. You don't need to repeat the condition in the WHERE clause. Use WHERE to further narrow down the query, for instance if you're searching for a specific author name.

SELECT * FROM author
JOIN book ON book.authorId = author.id
WHERE author.name LIKE 'John%';

If you want to query all authors, just remove the WHERE completely.

Also, you don't need to create data classes and do the mapping yourself. SQLDelight already creates data classes for your queries.

Gertrude

Please explain why this was down voted 😢 – Gertrude

Answer 2

You can create the query like Sven suggested and then map the joined table records into your authorEntity object using a mapper on the query.

This is how you use the mapper. You just need to adjust this to the joined columns you mentioned in your question to fill the authorEntity.

override fun get(id: Long): Flow<Query<Friend>>? {
   return try {
      return queries.getFriendById(
          id = id,
          mapper = { friendId, username, firstname, lastname, phone, picture, accepted ->
          Friend(
              Profile(friendId, username, firstname, lastname, phone, picture),
              accepted
          )
      }).asFlow()
  } catch (e: NullPointerException) {
      null
  }
}

Acton