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?

score 23 · Accepted Answer · answered Aug 17 '13 at 08:55

23

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.

answered Aug 17 '13 at 08:55

Martin Kolinek

2,010
14
16

1

Hey, this one worked! I am not exactly sure why, I haven't done more of a deep dive, but I needed a quick fix. – noplay Aug 20 '13 at 17:12
For some reason, this gives me a `scala.TupleN` instead of `XxxRow` entity, with `ClassCastException` thrown. – Alex Abdugafarov Jun 10 '14 at 15:35
Keep in mind that this will mess your result if you had some sorting in `query`. – Branislav Lazic Apr 27 '23 at 11:51

Valerii Rusakov · Answer 2 · 2020-11-12T10:21:37.260

22

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

edited Nov 12 '20 at 10:21

answered Oct 30 '15 at 17:45

Valerii Rusakov

1,091
2
11
22

4

FWIW, there seems to be [a bug with distinctOn](https://github.com/slick/slick/issues/1712) at the moment. – Nick Nov 14 '17 at 13:36

score 5 · Answer 3 · answered Aug 15 '13 at 18:34

5

I don't think this implemented yet. See https://github.com/slick/slick/issues/96

answered Aug 15 '13 at 18:34

Eugene Ryzhikov

17,131
3
38
60

score 2 · Answer 4 · answered Jun 03 '16 at 08:51

2

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)

answered Jun 03 '16 at 08:51

binshi

1,248
2
17
33

1

It needs to be it the **specific order** you described - i.e. `map(a => (a.id, a.name)).disctinctOn(a => (a._1, a._2))` or one gets `slick key not found: s2` runtime exception. Doing `distinctOn(a => (a.id, a.name))` won't work – kosiara - Bartosz Kosarzycki Mar 10 '18 at 22:55

SELECT DISTINCT in Scala slick

4 Answers4

Linked