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?
Yes, the maximum is constrained by the maximum number of columns in a SQL table (1024), but it is not 1024; it will always be less than that.
Column Count Limits MySQL has hard limit of 4096 columns per table, but the effective maximum may be less for a given table.
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:
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
).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With