How to COUNT(*) in Slick 2.0?

Question

According to the Slick 2.0 documentation, to get the count of rows in a table:

val q1 = coffees.length
// compiles to SQL (simplified):
//   select count(1) from "COFFEES"

However, it turns out that coffees.length is of type Column[Int].

How does one execute the query and get the value?

score 26 · Accepted Answer · answered Feb 03 '14 at 02:31

26

I just had this same problem upgrading to slick 2.0. I forget where the exact method lives, but the generic .run seems to work for me, i.e.

coffees.length.run

answered Feb 03 '14 at 02:31

waffle paradox

For some reason this `run` method is not available to me (could I be missing a trait on my DB driver?)... I was, however, able to get it to run like this: `scala.slick.lifted.Compiled(coffees.length).run`. If anyone knows what I'm missing to prevent the shorter version from working, let me know! – Chris W. Feb 26 '14 at 23:59
1

Amazing!, thank you! @ChrisW : did you import your Driver profile simple? – Krzysztof Kowalski Apr 06 '14 at 10:17
@KrzysztofKowalski: I could have sworn I *did* have all the symbols from my driver's `simple` module imported, but in any case, for some reason the shorter syntax (`table.length.run`) *is* working now. – Chris W. Apr 07 '14 at 14:49
This works, but the performance can be really bad. See my comment to @tuxSlayer below. – hezamu Sep 19 '14 at 06:55

score 5 · Answer 2 · answered May 16 '14 at 17:43

5

StaticQuery.queryNA[Int]("select count(*) from \"" + TableName + "\"").first

Quotes are needed if your table name is not upper case.

answered May 16 '14 at 17:43

tuxSlayer

Note that this query can be an order of magnitude faster than the `.length.run` alternative. My test DB has about 180k rows, and on my computer the first approach takes about 350ms and the StaticQuery approach takes around 30ms, which is about the same I get from mysql console. – hezamu Sep 19 '14 at 06:54
Yeah I query plan for ```coffees.length.run``` kinda query is horrible. So many nested queries: ```select x2.x3 from (select count(1) as x3 from (select x4.`updated_at` as x5, x4.`description` as x6, x4.`status` as x7) from `sales` x4) x19) x2``` – Richeek Aug 12 '15 at 01:53

vitalii · Answer 3 · 2014-02-03T08:01:04.253

1

~~Try coffees.length.first should execute and return Int~~

Sorry, indeed, in the slick 1.0 there was first method to do this, in Slick 2.0 they get rid of it in favor of more generic run.

The function to execute query is

coffees.length.run

edited Feb 03 '14 at 08:01

answered Feb 02 '14 at 21:15

vitalii

I had tried that earlier, but Column[Int] doesn't have a first method. – kes Feb 02 '14 at 22:15

3 Answers3