4

Having the following query template to select all:

val q = for {
  a <- Parameters[Int]
  b <- Parameters[Int]
  t <- T if t.a == a && t.b == b
  _ <- Query.orderBy(t.c, t.d)
} yield t

I need to modify it to select the very first (with minimum c and d minimum for this c) or the very last (with maximum c and d maximum for this c) record of those matching the where condition. I'd usually strongly prefer no other (than the last/first) records to be selected as there are hundreds thousands of them...

Ivan
  • 63,011
  • 101
  • 250
  • 382

2 Answers2

3

There is method firstOption defined on the Invoker trait, and by some magic it is available on the Query class. So maybe you can try it like this:

val q = for {
  a <- Parameters[Int]
  b <- Parameters[Int]
  t <- T if t.a == a && t.b == b
  _ <- Query.orderBy(t.c, t.d)
} yield t
q.firstOption
Rogach
  • 26,050
  • 21
  • 93
  • 172
3

There's a potential danger here in how the OP's query is currently constructed. Run as is, getting the first or last result of a 100K result set is not terribly efficient (unlikely, yes, but the point is, the query places no limit on number of number of rows returned)

With straight SQL you would never do such a thing; instead you would tack on a LIMIT 1

In ScalaQuery, LIMIT = take(n), so add take(1) to get a single record returned from the query itself

val q = (for {
  a <- Parameters[Int]
  b <- Parameters[Int]
  t <- T if t.a == a && t.b == b
  _ <- Query.orderBy(t.c, t.d)
} yield t) take(1)
q.firstOption
virtualeyes
  • 11,147
  • 6
  • 56
  • 91
  • Eh... ScalaQuery could *definitely* use some good round-up of all those fancy methods. (I failed to find one) – Rogach Apr 16 '12 at 14:38
  • SQ documentation leaves a lot to be desired, I agree. Hopefully with the morph into SLICK coming early summer, there will be better docs (since SLICK is under TypeSafe umbrella, I'm expecting just that) – virtualeyes Apr 16 '12 at 16:29
  • This doesn't compile, says "value take is not a member of org.scalaquery.ql.basic.BasicQueryTemplate[...]". Curiously, if replace the space before `take(1)` with a dot, my IDE (Idea) stops painting `take` in red, but the compiler still doesn't do... – Ivan Apr 17 '12 at 20:27
  • @Ivan try "q.take(1).firstOption" I built against ScalaQuery 0.10 master, maybe there's a difference between that and 0.9x branch. Should not matter though, do you have the (for {...}) wrapped in parens? – virtualeyes Apr 17 '12 at 21:29
  • "q.take(1).firstOption" produces `'}' expected but eof found.` error. I am building against 0.10.0-M1. Yes, I have the parens. Maybe the problem is caused by the fact I try to encapsulate my queries in the table model object (using `this` instead of `T` in the example) (but this works just for simple selects), I'll try to move them out... Another significant difference between reality and the example is that I use `Projection(a, b) <- Parameters[Int, Int]` for the parameters (I've came to this after writing the question as I could see no way to actually pass the parameters values otherwise). – Ivan Apr 17 '12 at 21:39
  • Curious thing is that I can't use `a ~ b` instead of `Projection(a, b)`. In both cases my IDE highlights it (`~` or `Projection` as an error) but the compiler is ok with `Projection`. – Ivan Apr 17 '12 at 21:49
  • well, start with the simplest approach and then work in the extras. Not sure about "this", I have db tables mapped to case class companion objects, and use the object in place of your T in the example. It's truly "flying by the seat of your pants" right now in SQ, we're on our own until Stefan Zeiger comes up for air with much anticipated SLICK release (he's giving a talk at Scala Days London tomorrow, stuck in France ;-( ...) – virtualeyes Apr 17 '12 at 21:59
  • @Ivan you should look at this thread if you have not already done so; the multiple Parameters[Int] bit may be causing some issues "http://szeiger.de/blog/2009/08/06/efficient-parameterized-queries-in-scala-query/" – virtualeyes Apr 17 '12 at 22:17
  • That's the post where I've taken the Projection way from. – Ivan Apr 17 '12 at 23:35
  • By the way, I've just tried your solution (adding `take`) with a simple parameter-less query - seems ok. But unfortunately that's of little use as in real life I need 6 parameters, 4 of them representing a composite primary key. Thank you anyway, I would hardly find the direction to dig without your assistance. – Ivan Apr 17 '12 at 23:48