SELECT DISTINCT in Scala slick

31
votes

I am using Slick 1, and I have to be able to apply a filter in a query to lookup all entities that match a condition in a related table.

This example using the Slick documentation shows what I am trying to do (this is a contrived example that is close to my situation).

Here, I want all coffees that are provided by suppliers on the west coast. I want the Coffee only, I am only interested in navigating to Suppliers to apply the filter:

val westCoast = Seq("CA", "OR", "WA")
val implicitInnerJoin = for {
  c <- Coffees
  s <- Suppliers if c.supID === s.id && s.state inSet westCoast
} yield c

This works ok, but it will duplicate Coffees if there is more than one match in the Suppliers table.

The obvious workaround is in normal SQL to do a SELECT DISTINCT; however, I cannot find a way to do that here.

You could in theory do a:

query.list.distinct

After the results are already returned; however, I have also implemented PAGING support, so you wouldn't want to process the results once the already come back from the database. Here is the paging support:

query.drop(offset).take(limit).list

So, in a nutshell, I need a way to specify SELECT DISTINCT in my query that goes out.

Anyone have any ideas?

4
scalaslick

4 Answers

23
votes

As a work around you can try to use groupBy:

query.groupBy(x=>x).map(_._1)

It should have the same semantics as distinct, but I'm not sure about performance.

21
votes

With slick 3.1.0 you can use distinct and distinctOn functions (Slick 3.1.0 release notes). For example:

val westCoast = Seq("CA", "OR", "WA")
val implicitInnerJoin = for {
  c <- Coffees
  s <- Suppliers if c.supID === s.id && s.state inSet westCoast
} yield c

db.run(implicitInnerJoin.distinctOn(_.name).result)

Update Finally bug related to distinctOn was resolved in slick 3.3.3

2
votes

For distinct on multiple columns coffee.name and coffee.price:

val westCoast = Seq("CA", "OR", "WA")
val implicitInnerJoin = for {
  c <- Coffees
  s <- Suppliers if c.supID === s.id && s.state inSet westCoast
} yield c

db.run(implicitInnerJoin.map(f => (f.name, f.price, f.state)).distinctOn(p => (p._1, p._2)).result)