How to do a multi-column subquery in slick 3.3

Question

I want to do delete rows from an n-to-n table with a subquery and match on the two id columns.

There are three tables: books, authors and a relationship book_author.

Basically I want to do:

DELETE FROM book_authors ba
WHERE (ba.book_id, ba.author_id) IN (
  SELECT (b.id, a.id)
  FROM books b, authors
  WHERE b.name = 'American Gods' AND a.name = 'Neil'
)

I can't figure out how to do that in slick. this is what I came up with:

  val query = for {
    b <- books if b.name === "American Gods"
    a <- authors if a.name === "Neil Gaiman"
  } yield (b.id, a.id)

  bookauthors.filter(row => (row.bookId, row.authorId) in query)

According to the documentation this works with a single column. I assume I have to help with the types there, but don't know how.

[info] Compiling 1 Scala source to /c/Users/saski/projects/essential-slick/book-authors/target/scala-2.12/classes ... 
[error] /c/Users/saski/projects/essential-slick/book-authors/src/main/scala/main.scala:105:56: value in is not a member of (slick.lifted.Rep[Long], slick.lifted.Rep[Long]) 
[error]   bookauthors.filter(row => (row.bookId, row.authorId) in query).result
[error]                                                        ^
[error] /c/Users/saski/projects/essential-slick/book-authors/src/main/scala/main.scala:105:21: ambiguous implicit values:
[error]  both value BooleanOptionColumnCanBeQueryCondition in object CanBeQueryCondition of type => slick.lifted.CanBeQueryCondition[slick.lifted.Rep[Option[Boolean]]] 
[error]  and value BooleanCanBeQueryCondition in object CanBeQueryCondition of type => slick.lifted.CanBeQueryCondition[Boolean]
[error]  match expected type slick.lifted.CanBeQueryCondition[Nothing]
[error]   bookauthors.filter(row => (row.bookId, row.authorId) in query).result
[error]                     ^
[error] two errors found 
[error] (Compile / compileIncremental) Compilation failed 
[error] Total time: 2 s, completed Jun 13, 2019 9:49:35 AM

Whole code example for completion

// Import the Slick interface for H2:
import slick.jdbc.H2Profile.api._

import scala.concurrent.Await
import scala.concurrent.duration._

object Example extends App {

  final case class Book(
                         name: String,
                         id: Long = 0L)

  final case class Author(
                           name: String,
                           id: Long = 0L)

  final case class BookAuthor(
                               bookId: Long,
                               authorId: Long)

  // Helper method for creating test data:
  def freshBooks = Seq(
    Book("Good Omens", 0),
    Book("American Goods", 1),
    Book("Looking for Alaska", 2),
  )

  // Helper method for creating test data:
  def freshAuthors = Seq(
    Author("Neil Gaiman", 0),
    Author("John Green", 1)
  )

  // Helper method for creating test data:
  def freshBooksAuthors = Seq(
    BookAuthor(0, 0),
    BookAuthor(1, 0),
    BookAuthor(2, 1),
  )

  // Schema for the "message" table:
  final class BookTable(tag: Tag)
    extends Table[Book](tag, "books") {

    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

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

    def * = (name, id).mapTo[Book]
  }

  // Schema for the "message" table:
  final class AuthorTable(tag: Tag)
    extends Table[Author](tag, "authors") {

    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

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

    def * = (name, id).mapTo[Author]
  }

  final class BookAuthorTable(tag: Tag)
    extends Table[BookAuthor](tag, "book_authors") {

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

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

    def * = (bookId, authorId).mapTo[BookAuthor]
  }

  // Base query for querying the messages table:
  lazy val books = TableQuery[BookTable]
  lazy val authors = TableQuery[AuthorTable]
  lazy val bookauthors = TableQuery[BookAuthorTable]

  // Create an in-memory H2 database;
  val db = Database.forConfig("book-authors")

  // Helper method for running a query in this example file:
  def exec[T](program: DBIO[T]): T = Await.result(db.run(program), 2 seconds)

  // Create the "messages" table:
  println("Creating database table")
  exec(DBIO.seq(
    (books.schema ++ authors.schema ++ bookauthors.schema).create,
    books ++= freshBooks,
    authors ++= freshAuthors,
    bookauthors ++= freshBooksAuthors
  ))


  // Run the test query and print the results:
  println("\nSelecting all messages:")
  Await.result(db.run(books.result), 2 seconds) foreach println

  val query = for {
    b <- books
    a <- authors if a.name === "Neil Gaiman"
  } yield (b.id, a.id)

  bookauthors.filter(row => (row.bookId, row.authorId) in query).result

}

Ossip Ossip · Accepted Answer · 2019-06-19T16:39:56

Seems the 'in' syntax only works for scalar types, e.g. one id, not tuples. (it tries to directly translate to sql 'in' I assume)

This should do the trick? (can't test it myself right now)

  query.flatMap{ case(b,a) => bookauthors.filter(row => row.bookId===b && row.authorId===a) }.result

As this doesn't seem to work for deleting, I guess you can just do something like this..

 val query = for {
    b <- books
    a <- authors if a.name === "Neil Gaiman"
    ba <- bookauthors if ba.bookId === b.id && ba.authorId === a.id
 } yield ba

 query.result
or
 query.delete

How to do a multi-column subquery in slick 3.3

1 Answers