SELECT DISTINCT in Scala slick
Solution 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.
Solution 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
Solution 3
I don't think this implemented yet. See https://github.com/slick/slick/issues/96
Solution 4
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)
noplay
Updated on June 16, 2022Comments
-
noplay almost 2 years
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?
-
noplay over 10 yearsHey, this one worked! I am not exactly sure why, I haven't done more of a deep dive, but I needed a quick fix.
-
Alex Abdugafarov almost 10 yearsFor some reason, this gives me a
scala.TupleN
instead ofXxxRow
entity, withClassCastException
thrown. -
Nick over 6 yearsFWIW, there seems to be a bug with distinctOn at the moment.
-
kosiara - Bartosz Kosarzycki about 6 yearsIt 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 getsslick key not found: s2
runtime exception. DoingdistinctOn(a => (a.id, a.name))
won't work