CODEWITHSUNDEEP
scalaplayframeworkslick
Fahad Siddiqui
Fahad Siddiqui

Reputation: 1849

Any string concatenation method in slick groupBy?

I am trying to apply .groupBy in a Slick query

var q = (for {
      user <- Users
      userSettings <- UserSettings if user.id === userSettings.userId
    } yield (user, userSettings)).groupBy {
      case (users, userSettings) =>
        (user.id, userSettings.controls)
    }.map {
      case (x, y) => (x._1, y.map(_._2.controls).???)
    }

If the controls column was an Integer or Long, I could apply sum, avg and other aggregate functions. But in this case controls is a string. How to group concatenate these strings so that records look like

-----------------------------------------
|User ID    |User Controls              |
-----------------------------------------
|1          |left, right, up, down      |
|2          |left, right                |
-----------------------------------------

without applying groupBy records look like this

-----------------------------------------
|User ID    |User Controls              |
-----------------------------------------
|1          |left                       |
|1          |right                      |
|1          |up                         |
|1          |down                       |
|2          |left                       |
|2          |right                      |
-----------------------------------------

Upvotes: 3

Views: 1837

Answers (2)

Kawser Habib
Kawser Habib

Reputation: 1422

Unfortunately, noticed no support for this. In this case, we can use raw queries with proper validation and sanitization.

Suppose, I have a table named logs (id, message, date). I want to group by date and contact each group message. So my query will be:

def histogram(data: ReqData): Future[Vector[DbHistogramMapper]] = {

    var messageWild = s"%${data.phrase}%"
    /**Need to write raw query because there is no support for GROUP_CONCAT in slick*/
    val query = sql"""SELECT "date" AS date, GROUP_CONCAT("message") AS message FROM "logs" WHERE "date" >= ${data.datetimeFrom} AND "date" <= ${data.datetimeUntil} AND "message" LIKE  ${messageWild} GROUP BY "date" """.as[DbHistogramMapper]
    db.run(query)
  }

Note: DbHistogramMapper

case class DbHistogramMapper(
     date: Timestamp,
     message: String
)

Upvotes: 0

DK_
DK_

Reputation: 2658

Slick takes your Scala code and converts it to SQL, so anything you do in Slick must be supported by the underlying SQL. If you search for similar questions related to concatenating strings in SQL, you find some results on SO. Unfortunately, it is non trivial to do this kind of thing in SQL. (Note: It is possible in some SQL dialects.) There may be some Slick driver that exposes SQL-dialect specific functions, or something that executes after the SQL query, but you can simply take your query, and run the groupBy/map after executing it against the DB, such as by doing:

val q = for {
  user <- Users
  userSettings <- UserSettings if user.id === userSettings.userId
} yield (user.id, userSettings.controls)
val db = Database.forConfig("h2mem1")
try {
  for {
    res <- db.run(q.result)
  } yield res
    .groupBy(_._1)
    .map({ case (id, t) => id -> t.map(_._2).mkString(", ") })
} finally db.close

Upvotes: 1

Related Questions