How can I count number of rows returned by a join in Slick?

Question

When I try to do Query(query.length).first on a query, that represents a join of 2 tables which have several columns with the same names, I am getting malformed sql. Consider the example:

// in Main.scala
import scala.slick.driver.MySQLDriver.simple._
object Main extends App {

  object Houses extends Table[Long]("Houses") {
    def id = column[Long]("id")
    def * = id
  }
  object Rooms extends Table[(Long, Long)]("Rooms") {
    def id = column[Long]("id")
    def houseId = column[Long]("houseId")
    def * = id ~ houseId
  }

  val query = for {
    h <- Houses
    r <- Rooms
    if h.id === r.houseId
  } yield (h, r)
  println("QUERY: " + Query(query.length).selectStatement)
}

// in build.sbt
scalaVersion := "2.10.2"

libraryDependencies += "com.typesafe.slick" %% "slick" % "1.0.1"

This example generates the following SQL:

select x2.x3 from
  (select count(1) as x3 from 
    (select x4.`id`, x5.`id`, x5.`houseId` 
     from `Houses` x4, `Rooms` x5 where x4.`id` = x5.`houseId`) x6) x2

Which is clearly wrong and is rejected by MySQL because id column is duplicated in select x4.id, x5.id part.

I could try to do the following:

query.list.size

but that will extract all the rows from the query and send them over the wire, which is going to hinder performance greatly.

What am I doing wrong? Is there some way to fix it?

Answer 1

That's an interesting issue. Usually with SQL, you alias the other column which would cause a name collision but I'm not sure how that works with Slick (or if even possible). But you can work around this I believe by only selecting a single column if you just want to count:

val query = for {
  h <- Houses
  r <- Rooms
  if h.id === r.houseId
} yield h.id.count

Now the count call on id is deprecated, but this one produced a clean sql statement which looks like this:

select count(x2.`id`) from `Houses` x2, `Rooms` x3 where x2.`id` = x3.`houseId`

Anything that I tried using .length produced a bunch of sql that was not correct.

EDIT

In response to your comment, it you wanted to leave the query the way it was (and let's forget that the query itself is broken due to field collision/ambiguity in the join) and then be able to also derive a count query from it, that would look like this:

def main(args: Array[String]) {
  val query = for {
    h <- Houses
    r <- Rooms
    if h.id === r.houseId
  } yield (h,r)

  val lengthQuery = query.map(_._1.id.count)
}

The point here is that you should be able to take any query and map it to a count query by selecting a single column (instead of the full objects) and then getting that count for that column. In this case, because the result is a Tuple2, I have to go in an additional level to get to the id column, but I think you get the picture.