There is some table:
case class Thing(name: String, color: Option[String], height: Option[String])
class ThingSchema(t: Tag) extends Table[Thing](t, "things") {
def name = column[String]("name")
def color = column[Option[String]]("color")
def height = column[Option[String]]("height")
def * = (name, color, height) <> (Thing.tupled, Thing.unapply)
}
val things = TableQuery[ThingSchema]
For example, there are the following data in the things
table:
| name | color | height |
+---------+-----------+--------+
| n1 | green | <null> |
| n1 | green | <null> |
| n1 | <null> | normal |
| n1 | <null> | normal |
| n1 | red | <null> |
| n2 | red | <null> |
I need to get the following result from the above data:
| name | color | height | size |
+---------+-----------+--------+------+
| n1 | green | <null> | 2 |
| n1 | <null> | normal | 2 |
| n1 | red | <null> | 1 |
| n2 | red | <null> | 1 |
To solve this task I use the following grouping queries:
SELECT name, color, null, count(*) AS size
FROM things
GROUP BY name, color
UNION ALL
SELECT name, null, height, count(*) AS size
FROM things
GROUP BY name, height
I've tried to create this query with the Slick:
val query1 =
things.groupBy(t => (t.name, t.color))
.map { case (name, color) => (name,color,None, g._2.size)} //Error#1
val query2 =
things.groupBy(t => (t.name, t.height))
.map { case (name, height) => (name,None,height,g._2.size)} //Error#1
val query = query1 ++ query2
But the above code isn't compiled, because the Slick can't define type for ConstColumn
for the None
values (see //Error#1
comment in the above code).
This would work for the NOT-null values (such as numbers
, strings
), but doesn't work for Nullable values which are represented as Option[String]=None
for example.
How to use ConstColumn
for None
values for this case?