Scala Slick groupBy without aggregation

Question

I'm trying to do a basic query yielding 3 columns from one table, grouped by cityName (of type string), however I can't quite work out the correct way to handle the mapping. The documentation suggests that one must map the result returned from groupBy regardless of whether it requires aggregation. In this case it's grouping by a string value, therefore when writing this in SQL it would be as simple as:

SELECT cityCode, cityName, countryName FROM airport GROUP BY cityCode

What I have so far is the following query, which returns Future[Seq[(String, String, String)]]:

val q = for {
   a <- slickAirports
} yield (a.cityCode, a.cityName, a.cityName)
db.run(q.result)

My attempt at grouping by cityCode following the example here: http://slick.typesafe.com/doc/3.0.3/sql-to-slick.html#group-by

val q2 = q.groupBy(a => a._1).map{ 
   case (cityCode, group) => (cityCode, group.map(_._1))
}
db.run(q2.result)

This result in the following error:

type mismatch;
 found   : AirportDAOImpl.this.driver.StreamingDriverAction[Seq[(String, Option[String])],(String, Option[String]),slick.dbio.Effect.Read]
    (which expands to)  slick.profile.FixedSqlStreamingAction[Seq[(String, Option[String])],(String, Option[String]),slick.dbio.Effect.Read]
 required: slick.dbio.DBIOAction[Seq[(String, String, String)],slick.dbio.NoStream,Nothing]

The only thing I can see I'm doing differently is not including the .avg on the group.map(_._1), however since this is a string column i'm trying to group on surely it doesn't require this. Any insights into the proper way to go about simple groupBy's of string columns would be greatly appreciated.

Did you try whether your SQL statement would work? Because afaik columns not included in the GROUP BY statement need to use a aggregation function like AVG, MAX etc.. So my first guess would be that this SQL statement is not even valid, since it would need to return some sort of map from cityCode to a list of cityName and countryName (same goes for the slick statement btw). Maybe you could write down what kind of result you expect. I think that would help in answering your question. — thwiegan
IIRC MySQL supports this weird style of GROUP BY, but it's not universally supported. I think you want to use DISTINCT instead. — jkinkead

Daniel Daniel · Accepted Answer · 2021-10-14T13:20:28

The SQL query you present:

SELECT cityCode, cityName, countryName FROM airport GROUP BY cityCode

is not valid. In order to get cityName and countryName, you must also group by these columns as such:

SELECT cityCode, cityName, countryName FROM airport GROUP BY cityCode, cityName, countryName

The Slick query thus becomes:

val query = slickAirports
  .groupBy(airport => (airport.cityCode, airport.cityName, airport.countryName))
  .map(_._1)
  .result

This will result in a sequence of tuples containing the grouped cityCode, cityName, and countryName respectively.

Scala Slick groupBy without aggregation

1 Answers