Any string concatenation method in slick groupBy?

Question

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                      |
-----------------------------------------

There is an issue where support for backend-specific aggregation functions is discussed: https://github.com/slick/slick/issues/923 If you happen to be using postgres, there is an extension to slick that adds support for these functions: https://github.com/tminglei/slick-pg — ecoe, Aug 28 '18 at 19:08

score 1 · Accepted Answer · edited May 23 '17 at 12:24

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

IIRC — If the rows are sorted when they come back from `db.run`, Scala's groupBy() should maintain the ordering within the groupings. — DK_, Oct 27 '16 at 21:02

score 0 · Answer 2 · answered May 06 '21 at 18:29

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
)

Any string concatenation method in slick groupBy?

2 Answers2