Map to tuple with None value of case class type

[vma...@openwt.com]

Hello,

I am working on a project using Slick and it's the first time that I have absolutely no idea of how to solve my problem. Imagine I have the two following table definitions:

class Users(tag: Tag) extends Table[User](tag, "USERS") {

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

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

  def * = (name, id.?) <> (User.tupled, User.unapply)

}

class Books(tag: Tag) extends Table[Book](tag, "BOOKS") {

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

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

  def price = column[Double]("PRICE")

  def userId = column[Int]("USER_ID")

  foreignKey("USER_ID_FK", userId, Tables.users)(_.id)

  def * = (name, price, userId, id.?) <> (Book.tupled, Book.unapply)

}

Now, I have a requirement which is that I need to joinLeft Users with Books and do something with the result but for some performance reasons, I do not always want to do it... Sometimes, I just want to return one tuple per user with None as "joined book". (my use case is a bit more complex but the important part is that I need the type of the query to be Query[User, Book] even if I don't load the books. Then depending on the condition, different processing will be applied)

val query = if (someCondition) {

  Tables.users.joinLeft(Tables.books).on({
    case (user, book) => user.id === book.userId

  })
} else {
  Tables.users.map(user =>
    (user, None: Option[Book])
  )
}

Quite simple, isn't it ? And here I am, with a compilation error saying that "No matching Shape found. Slick does not know how to map the given types."

Do you have an idea of what I am missing ?

Thank you for your time,

Valentin

[Richard Dallaway]

Hello Valentin

Hopefully someone will have other answers for you, but in the meantime...

When you say:

Tables.users.map(user =>

    (user, None: Option[Book])

  )

...you're selecting rows, and the way I look at it, I'd find it hard to know what the SQL would be for the None part of that.

I suppose it could be `select id, name, null, null, null, null from user`. I don't think Slick will try to do that for you.

There are two approaches I can see.

1. Convert to (User,Option[Book]) on the client, rather than in SQL.

For example, maybe you work in terms of DBIO and do the conversation as part of that. Something like:

  def addBlankBook(users: Seq[Users]): Seq[User,Option[Book]] = users.map(u => (u,None))

  def query: DBIO[Seq[(User,Option[Book])]] = Table.users.result.map(addBlankBook)

(Apologies for any typos in any of this. I've not tried to compile it)

2. Or you could do a custom mapped projection to convert the query into one that returns the type you want. That would be something like...

  def query = Table.users.map(u => (u.name, u.id) <> (withBlankBook, ignoreBook))

  // Map the fields of our query into the type we want:

  def withBlankBook(tuple: (Int,String,Double,Int)): (User,Option[Book]) = 

    ( User(tuple._1, tuple._2, tuple._3, tuple._4), None )

  // going the other way:

  def ignoreBook(pair: (User, Option[Book]) = {

    val u = pair._1

    Some( (u.id, u.name, etc) )

  }

I'd probably for the first option, as it's less code and less duplication of columns. The advantage of the second version is that you get to continue to work in terms of Query, rather than DBIO, if that's something you need.

Hope that's some use. If anyone has nicer ways, I'd love to hear about them.

Richard

--

---
You received this message because you are subscribed to the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scalaquery+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/069e1861-5186-45b4-8e19-f7b91a3cfb36%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[vma...@openwt.com]

Hi Richard,

Yes, you are completely right, my goal is to have the equivalent of a SQL query such as select id, name, null, null, null, null from user, it does work this way with simple types such as Int, String, etc. For instance, the following code compiles and generates the following SQL query (the case when is definitely not optimal but will do the job):

Tables.users.map(user =>

  (user, None: Option[String])

)

select x2."NAME", x2."ID", (case when (null is null) then null else null end) from "USERS" x2

Option 1 is unfortunately not an option since I want the "post processing" to be done inside the DB (imagine a Book table with millions of elements, and you want to retrieve the sum of the book prices per user for the 10 users who have spent the most, I definitely cannot afford to retrieve all those records, sum them and sort them in my backend).

Option 2 could be a solution although, as you said, it will introduce some code redundancy. I will give it a try in the next few days.

Thank you !

Valentin