title: Slick 3 join query one to many relationship
body: <p>Imagine the following relation</p>
  
  <p>One book consists of many chapters, a chapter belongs to exactly one book. Classical one to many relation.</p>
  
  <p>I modeled it as this:</p>
  
  <pre><code>case class Book(id: Option[Long] = None, order: Long, val title: String)
  
  class Books(tag: Tag) extends Table[Book](tag, "books")
  {
    def id = column[Option[Long]]("id", O.PrimaryKey, O.AutoInc)
    def order = column[Long]("order")
    def title = column[String]("title")
  
    def * = (id, order, title) &lt;&gt; (Book.tupled, Book.unapply)
    def uniqueOrder = index("order", order, unique = true)
  
    def chapters: Query[Chapters, Chapter, Seq] = Chapters.all.filter(_.bookID === id)
  }
  
  object Books
  {
    lazy val all = TableQuery[Books]
    val findById = Compiled {id: Rep[Long] =&gt; all.filter(_.id === id)}
  
    def add(order: Long, title: String) = all += new Book(None, order, title)
    def delete(id: Long) = all.filter(_.id === id).delete
  
  //  def withChapters(q: Query[Books, Book, Seq]) = q.join(Chapters.all).on(_.id === _.bookID)
  
    val withChapters = for
    {
      (Books, Chapters) &lt;- all join Chapters.all on (_.id === _.bookID)
    } yield(Books, Chapters)
  }
  
  case class Chapter(id: Option[Long] = None, bookID: Long, order: Long, val title: String)
  
  class Chapters(tag: Tag) extends Table[Chapter](tag, "chapters")
  {
    def id = column[Option[Long]]("id", O.PrimaryKey, O.AutoInc)
    def bookID = column[Long]("book_id")
    def order = column[Long]("order")
    def title = column[String]("title")
  
    def * = (id, bookID, order, title) &lt;&gt; (Chapter.tupled, Chapter.unapply)
    def uniqueOrder = index("order", order, unique = true)
  
    def bookFK = foreignKey("book_fk", bookID, Books.all)(_.id.get, onUpdate = ForeignKeyAction.Cascade, onDelete = ForeignKeyAction.Restrict)
  }
  
  object Chapters
  {
    lazy val all = TableQuery[Chapters]
    val findById = Compiled {id: Rep[Long] =&gt; all.filter(_.id === id)}
  
    def add(bookId: Long, order: Long, title: String) = all += new Chapter(None, bookId, order, title)
    def delete(id: Long) = all.filter(_.id === id).delete
  }
  </code></pre>
  
  <p><strong>Now what I want to do:</strong></p>
  
  <p><em>I want to query all or a specific book (by id) <strong>with all their chapters</em></strong></p>
  
  <p>Translated to plain SQL, something like:</p>
  
  <p><code>SELECT * FROM books b JOIN chapters c ON books.id == c.book_id WHERE books.id = 10</code></p>
  
  <p>but in Slick I can't really get this whole thing to work.</p>
  
  <p>What I tried:</p>
  
  <pre><code>object Books
  {
      //...
      def withChapters(q: Query[Books, Book, Seq]) = q.join(Chapters.all).on(_.id === _.bookID)
  }
  </code></pre>
  
  <p>as well as:</p>
  
  <pre><code>object Books
  {
      //...
      val withChapters = for
      {
          (Books, Chapters) &lt;- all join Chapters.all on (_.id === _.bookID)
      } yield(Books, Chapters)
  }
  </code></pre>
  
  <p>but to no avail. (I use ScalaTest and I get an empty result (for <code>def withChapters(...)</code>) or another exception for the <code>val withChapters = for...</code>)</p>
  
  <p>How to go on about this? I tried to keep to the documentation, but I'm doing something wrong obviously.</p>
  
  <p><strong>Also:</strong> Is there an easy way to see the actual query as a String? I only found <code>query.selectStatement</code> and the like, but that's not available for my joined query. Would be great for debugging to see if the actual query was wrong.</p>
  
  <p><strong>edit:</strong> My test looks like this:</p>
  
  <pre><code>class BookWithChapters extends FlatSpec with Matchers with ScalaFutures with BeforeAndAfter
  {
    val db = Database.forConfig("db.test.h2")
  
    private val books = Books.all
    private val chapters = Chapters.all
  
    before { db.run(setup) }
    after {db.run(tearDown)}
  
    val setup = DBIO.seq(
      (books.schema).create,
      (chapters.schema).create
    )
  
    val tearDown = DBIO.seq(
      (books.schema).drop,
      (chapters.schema).drop
    )
  
    "Books" should "consist of chapters" in
    {
      db.run(
        DBIO.seq
        (
          Books.add(0, "Book #1"),
          Chapters.add(0, 0, "Chapter #1")
        )
      )
  
      //whenReady(db.run(Books.withChapters(books).result)) {
      whenReady(db.run(Books.withChapters(1).result)) {
      result =&gt; {
        //  result should have length 1
          print(result(0)._1)
        }
      }
    }
  }
  </code></pre>
  
  <p>like this I get an <code>IndexOutOfBoundsException</code>.</p>
  
  <p>I used this as my method:</p>
  
  <pre><code>object Books
  {
        def withChapters(id: Long) = Books.all.filter(_.id === id) join Chapters.all on (_.id === _.bookID)
  }
  </code></pre>
  
  <p>also:</p>
  
  <p><code>logback.xml</code> looks like this:</p>
  
  <pre><code>&lt;configuration&gt;
      &lt;logger name="slick.jdbc.JdbcBackend.statement" level="DEBUG/&gt;
  &lt;/configuration&gt;
  </code></pre>
  
  <p>Where can I see the logs? Or what else do I have to do to see them?</p>