1

I'm trying to sort my query by one field and the count of the joined query. That's my SQL:

SELECT * FROM stmt s 
LEFT JOIN rctn r ON s.id = r.stmt_id 
GROUP BY s.id 
ORDER BY (s.time + INTERVAL 1*COUNT(r.stmt_id) DAY) DESC

I already have

stmts.where(_.orgid === orgId)
    .innerJoin(rctns).on(_.id === _.stmtId)
    .groupBy(_._1.id)

, but I have no idea how to do the sortBy.

Any ideas?

pichsenmeister
  • 2,132
  • 4
  • 18
  • 34

1 Answers1

4

Very much like in Scala collections:

stmts.filter(_.orgid === orgId)
  .innerJoin(rctns).on(_.id === _.stmtId)
  .groupBy(_._1.id)
  .sortBy{ case (s_id,group) =>
    group.map(_._1.time).max + interval(
      group.map(_._2.stmtId).length,
      "DAY"
    )
  }

The .max is a hack because we currently do not support .head, which you would have to use in Scala collections. Be aware, that interval does not come with Slick and you will have to define it yourself using the SimpleExpression construct, see http://slick.typesafe.com/doc/2.0.2/userdefined.html#scala-database-functions.

Notice that in the past we had more bugs with group by in Slick than with other operations. SQL pulls a lot of type-tricks (identifiers changing type between collections and scalar values, etc.), which are tricky to get right in a mapping to Scala semantics. The things we know about are fixed now. If you experience a case where invalid SQL is generated, please report a bug that allows us to reproduce the problem.

cvogt
  • 11,260
  • 30
  • 46
  • Thx for the answers. Just another question regarding this: I wrote the interval function `val interval = SimpleFunction.binary[Column[Int], String, Timestamp]("INTERVAL")` and now I'm getting the error `could not find implicit value for parameter tm: scala.slick.ast.BaseTypedType[java.sql.Timestamp] with scala.slick.ast.NumericTypedType group.map(_._1.time).max + interval(`. I searched for the error, but the docs to slick are very rare. what I'm doing wrong here? – pichsenmeister Jun 26 '14 at 11:31
  • did you import .simple._ from your driver at the place wher it happens? – cvogt Jul 02 '14 at 23:14
  • yes. `import scala.slick.driver.MySQLDriver.simple._` – pichsenmeister Jul 03 '14 at 17:16
  • Please report a bug including a code sample that allows us to replicate the problem. e.g. a github project – cvogt Jul 03 '14 at 19:43