Many to many relationship in Slick

[Amir]

Hi,

I have the following tables (author_book, book, and author), where a book can have several authors and an author can have several books (many to many relations).

I need help to write a function to return a list of all books including their authors, i.e. a function with the following header:

def getBooks: Future[Seq[(Book)] = db.run {...}

This function is supposed to returns a list of all books that each book has a list of it's authors. Ideally, to access a book's authors, I use this format "book1.authors"

Any help would be appreciated

create table "author_book" (

...
  "author_id" bigint(20) NOT NULL,
  "book_id" bigint(20) NOT NULL,

  FOREIGN KEY ("author_id") REFERENCES author(id),
  FOREIGN KEY ("book_id") REFERENCES book(id)

)

create table "book" (
  "id" bigint generated by default as identity(start with 1) not null primary key,
  "name" varchar not null,
...
)
create table "author" (
  "id" bigint generated by default as identity(start with 1) not null primary key,
  "name" varchar not null
...
);

private class BookTable(tag: Tag) extends Table[Book](tag, "book") {

  /** The ID column, which is the primary key, and auto incremented */
  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

  def name = column[String]("name")

  def publishDate = column[Date]("publish_date")

  def memberId = column[Option[Long]]("member_id")

  def member = foreignKey("member_fk",memberId,members)(_.id)



private class AuthorBookTable (tag: Tag) extends Table[AuthorBook](tag, "author_book") {

 /** The ID column, which is the primary key, and auto incremented */
 def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

 def authorId = column[Long]("author_id")

 def bookId = column[Long]("book_id")



private class AuthorTable (tag: Tag) extends Table[Author](tag, "author") {

 /** The ID column, which is the primary key, and auto incremented */
 def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

 def name = column[String]("name")