SELECT DISTINCT in Scala slick

Question

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?

Answer 1

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.

Answer 2

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