11

I face a problem I would like to simplify : (quite sure, I'm doing it wrong in fact).

Wanted

I would like to count the number of users having an id = 1. In SQL language let's say it is something like this : 

SELECT COUNT(*) FROM users WHERE id = 1

Code

I'm using Slick in it's "lifted" form, so here is my piece of code counting the users :

Query(Users.where( _.id === 1).length).first

Actually what happens here is that Slick alias ScalaQuery, is actually creating a subquery with the filter cause and then counting the results of the sub-request. 

SELECT COUNT(*) FROM (SELECT * FROM users WHERE id = 1))

Seems like pretty big overhead for such a query.

i.am.michiel
  • 10,281
  • 7
  • 50
  • 86
  • Which DB are you using? Does this really lead to a different query execution plan than `SELECT COUNT(*) FROM users WHERE id = 1)`? SQL is based on relational algebra for a reason... you can automatically optimize stuff like this easily. Doesn't mean it's always done though. So IS IT a pretty big overhead or does it just seem like one but is not? – cvogt Sep 28 '13 at 08:58
  • @cvogt It makes a difference in MySQL 5.6 & Slick 2.1.0. `TableQuery[MyTable].filter(_.id === 6L).length` returns two nested selects (ie worse than this post), and results in a worse query plan. Is there a better way to tell Slick `SELECT COUNT(*) FROM my_table WHERE id = 6` ? – Muel Aug 28 '14 at 21:12
  • Here is a hack to override the SQL code Slick uses: https://gist.github.com/cvogt/d9049c63fc395654c4b4 – cvogt Aug 28 '14 at 21:15
  • @cvogt Thanks for the code! :) I was hoping for a hack-free technique - alas! – Muel Aug 28 '14 at 21:29
  • No hack free technique available at the moment. – cvogt Aug 28 '14 at 22:50

1 Answers1

9

Not sure if this has changed from ScalaQuery to Slick, but try:

val q = for{ 
  id <- Parameters[Int]
  t <- tableObject if t.id is id
} yield t.id.count

val cnt = q(someID).firstOption getOrElse 0
virtualeyes
  • 11,147
  • 6
  • 56
  • 91
  • Aaaand, there goes my one-liner! Any way, thanks, this indeed seems to work great. The generated SQL query is simpler and syntax is exactly the same for Slick. – i.am.michiel Oct 12 '12 at 20:40
  • right, I never got in the habit of using Query(...), as unlike for{...}, afaik, Query(...) is not composable (not sure if cached prepared statement is generated under the hood either). At any rate the vast majority of hand written sql can be replicated in SQ/Slick, so happy days ;-) and with type providers on the road map, bye, bye case class companion object mapping boilerplate... – virtualeyes Oct 13 '12 at 06:03
  • This might work to check if the result is 0 or 1 but it doesn't seem to work to get the count of items that match a criteria. The result will be a list that I can't figure out what represent. Example: I run it and I expect a count of 5 elements (returned as 1 row with 1 value equal to 5). Instead I get 5 rows each containing one value equal to 3. – Cristian Vrabie Oct 15 '13 at 18:55
  • @CristianVrabie firstOption should return an Option, not a List. If you're on Slick, I have no idea, still (happily) on ScalaQuery here. – virtualeyes Oct 16 '13 at 05:03
  • @virtualeyes My mistake there. Your exact example does work even on Slick but it gives a deprecated warning on the `.count` method saying to replace it with `.length`. When you do it behaves as I explained. Must be a naming collision with some other function as I've seen many people complaining that the new method does not work as expected. – Cristian Vrabie Oct 16 '13 at 09:12