CODEWITHSUNDEEP
scalaslick
Vishal
Vishal

Reputation: 1492

Grouping in Slick

I am trying to get a group by result in from the slick table .

Sql : Select * from Jobs GROUP BY category ;

Class 

case class JobEntity(id:Option[Long],category:String,properties:String)

My slick function

def getJobsByCategory() :(String,Future[Seq[JobsEntity]]) = 
db.run(jobs.groupBy(_.category).map{ case(category,res) => 
(category,res)}.result)

Error:

No matching Shape found.
[ERROR] Slick does not know how to map the given types.
[ERROR] Possible causes: T in Table[T] does not match your * projection,
[ERROR]  you use an unsupported type in a Query (e.g. scala List),
[ERROR]  or you forgot to import a driver api into scope.
[ERROR]   Required level: slick.lifted.FlatShapeLevel
[ERROR]      Source type: (slick.lifted.Rep[String], slick.sql.FixedSqlStreamingAction[Seq[org.exadatum.xstream.persistence.models.SparkConfigEntity],org.exadatum.xstream.persistence.models.SparkConfigEntity,slick.dbio.Effect.Read])
[ERROR]    Unpacked type: T
[ERROR]      Packed type: G
[ERROR]

There is probably some issue with the return type but am not sure what as the IDE generates error as 

Expression of type Future[Seq[Nothing]] doesn't conform to expected type (String,Future[Seq[JobsEntity]])

Upvotes: 0

Views: 1876

Answers (1)

thwiegan
thwiegan

Reputation: 2173

Sql : Select * from Jobs GROUP BY category ;

This query would only work (even in SQL) if your table only consists of the category field.

With a group by statement every field in the select statement which is not in the group by statement (in your case everything (*) aside from category), needs to be aggregated in some way, since standard SQL only supports flat result tables.

Same stands for Slick. In the map call following the groupBy call, you'll have to define aggregation functions for everything aside your category. Otherwise Slick does not know how to map the result (as stated in the exception)

case class JobEntity(id:Option[Long],category:String,properties:String)

db.run(jobs.groupBy(_.category).map{ case(category,res) =>(category,res)}.result)

Does not work as it is.

Something like: 

db.run(jobs.groupBy(_.category).map{ case(category,res) =>(category,res.map(_.1).sum)}.result)

Would work, since it results in a flat shape: Your category and the sum of the IDs with that category. I know this does not make sense for you as is, but hopefully illustrates the problem.

If really just want to group your jobs by the category, I would do it in Scala after fetching them from the database:

val groupedJobs: Future[Seq[String, JobEntity]] = db.run(jobs).map {
   case jobs => jobs.groupBy(_.category)
}

If you tell me what you want to achieve exactly, I can propose another solution for you.

Upvotes: 1

Related Questions