7

How can we overcome the 22 limit when calling procedures with Slick?

We currently have: 

val q3 = sql"""call getStatements(${accountNumber})""".as[Transaction]

The problem is that we have to return more than 22 columns and Transaction case class cannot have more than 22 columns since when we do JSONFormat we get an error:

[error] E:\IdeaProjects\admin\Transaction.scala:59: No unapply or unapplySeq function found
[error]   implicit val jsonFormat = Json.format[Transaction]

Any suggestions?

user836845
  • 85
  • 4

1 Answers1

16

Alright - so if you can actually modify your Transaction case class than there is a better solution than HList (which to be honest may be a little cumbersome to operate with later on).

So here is the thing: let's imagine you have User table with following attributes:

  • id
  • name
  • surname
  • faculty
  • finalGrade
  • street
  • number
  • city
  • postCode

Above columns may not make sense but let's use them as example. The most straightforward way to deal with above is to create a case class:

case class User(
   id: Long,
   name: String,
   ...  // rest of the attributes here
   postCode: String)

which would be mapped from table on the application side.

Now what you can also do is to do this:

case class Address(street: String, number: String, city: String, postCode: String)

case class UniversityInfo(faculty: String, finalGrade: Double)

case class User(id: Long, name: String, surname: String, uniInfo: UniversityInfo, address: Address)

This composition will help you to avoid problem with too many columns (which is basically problem with too many attributes in your case class/tuple). Apart from that - I would argue that it is always (very often?) beneficial to do this if you have many columns - if for nothing else than simply for readability purposes.

How to do the mapping

class User(tag: Tag) extends Table(tag, "User") {

  // cricoss info
  def id = column[Long]("id")
  def name = column[String]("name")

  // ... all the other fields
  def postCode = column[String]("postCode")

  def * = (id, name, surname, uniInfoProjection, addressProjection) <>((User.apply _).tupled, User.unapply)

  def uniInfoProjection = (faculty, finalGrade) <>((UniversityInfo.apply _).tupled, UniversityInfo.unapply)

  def addressProjection = (street, number, city, city) <>((Address.apply _).tupled, Address.unapply)
}

The same can be done with custom SQL mapping. 

implicit val getUserResult = GetResult(r => 
    User(r.nextLong, r.nextString, r.nextString, 
         UniversityInfo(r.nextString, r.nextDouble),
         Adress(r.nextString, r.nextString, r.nextString, r.nextString))
)

So to put things simply - try to segregate your fields into multiple nested case classes and your problem should go away (with added benefit of improved readability). If you do that approaching tuple/case class limit should virtually never be a problem (and you shouldn't even need to use HList).

Paul Dolega
  • 2,446
  • 14
  • 23
  • Excellent that's exactly what we use for our other models. But can I do that when the result is from a procedure as well? Can I get the result of a procedure into a class that extends Table class? – user836845 Feb 23 '17 at 12:29
  • Sure, as long as `Slick` is concerned - it is just a query result. – Paul Dolega Feb 23 '17 at 12:30
  • Can I get the result of a procedure into a class that extends Table class? If yes that's awesome. – user836845 Feb 23 '17 at 12:31
  • That's confusing question - `Table` class is for specifying mapping, it is not for representing actual db tuples. You normally have a pair: - class extending `Table` (mapping definition) and a `case class` for representing db tuples (it doesn't need to be case class - but most often it is) – Paul Dolega Feb 23 '17 at 12:34
  • Yes we do that automatically for all our tables using the Generator. We modified that to our needs and it works really really good. But for the results from the procedure I just used a simple `case class` and it worked really well. – user836845 Feb 23 '17 at 12:37