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?

Jaime

Answer 1

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

Rafe

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! – Eusebiaeusebio

Amazing!, thank you! @ChrisW : did you import your Driver profile simple? – Susumu

@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. – Eusebiaeusebio

This works, but the performance can be really bad. See my comment to @tuxSlayer below. – Emotive

Answer 2

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

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

Macbeth

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. – Emotive

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 – Monometallism

Answer 3

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

Lantha

I had tried that earlier, but Column[Int] doesn't have a first method. – Jaime