CODEWITHSUNDEEP
scalagroup-byslickslick-3.0
Ross Anthony
Ross Anthony

Reputation: 531

Scala Slick groupBy without aggregation

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.

Upvotes: 1

Views: 1256

Answers (1)

Daniel
Daniel

Reputation: 9464

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.

Upvotes: 1

Related Questions