How to count results with a filter using Slick?

Question

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.

Answer 1

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