SimpleExpression returning a tuple

[Nebehr Gudahtt]

I am trying to hack together a solution that would allow me to use DISTINCT ON clause in Postgres. As is well known, it does not work out of the box in Slick. I am trying to implement it using SimpleExpression but cannot find a way to make it accept tuple as return type.

case class Item(name: String, param: String, ord: Int)

class Items(tag: Tag) extends Table[Item](tag, "item") {
  def name = column[String]("name")
  def param = column[String]("param")
  def ord = column[Int]("ord")

  def * = (name, param, ord).mapTo[Item]
}

val items = TableQuery[Items]

val distinctOn = SimpleExpression.unary[String, (String, String)] { (col, qb) =>
  import slick.util.MacroSupport._
  import qb._
  b"distinct on ($col) name, param"
}

val query = items.sortBy(i => (i.name, i.ord.desc)).map(i => distinctOn(i.name))

It does work when return type is a single column, but if I try to return more than one column (or the whole row, using "*") I get "could not find implicit value for evidence parameter of type slick.ast.TypedType[...]". I do realize that SimpleExpression probably wasn't meant for such scenarios but is it even possible to achieve what I am trying to?

[Ivano Pagano]

Hello, I'm trying to figure out what's wrong with the distinct clause in postgres?

Why can't you 

items.distinctOn(_.name).map(_.name).sortBy(...) ?

I ask because I'm using this kind of code on our project and didn't realise something was amiss...

Iv. 

[Nebehr Gudahtt]

SELECT DISTINCT != SELECT DISTINCT ON. See https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT

I am not sure actually what the difference is between Slick's distinct and distinctOn but neither is what I am after.

As for plain DISTINCT, there is an outstanding bug https://github.com/slick/slick/issues/1340 which probably does not manifest itself in simple queries.

[Ivano Pagano]

Thanks for the clarification, will turn out useful.

Iv.