Slick subquery: No type for symbol s2 found for Ref s2

Question

I have a query in slick which has a subquery, looking like this:

(User join Address on (_.id === _.userId))
.map { case (user, address) =>
    (
        user.id,
        address.id,
        address.street,
        Detail.filter(_.userId === user.id).map(_.value).first
    )
}.list

which causes an exception:

[SlickException: No type for symbol s2 found for Ref s2]

What is the issue here? To be clear, this is a simplified version version of my production code for convenience, however the error is preciselly the same.

The stack: Slick 2.1 / Scala 2.11.4 / Play Framework 2.3.6 / Java 8

Table classes are generated by slick code generator (I'd like to keep it that way):

package db
// AUTO-GENERATED Slick data model
/** Stand-alone Slick data model for immediate use */
object Tables extends {
  val profile = scala.slick.driver.PostgresDriver
} with Tables

/** Slick data model trait for extension, choice of backend or usage in the cake pattern. (Make sure to initialize this late.) */
trait Tables {
  val profile: scala.slick.driver.JdbcProfile
  import profile.simple._
  import scala.slick.model.ForeignKeyAction
  // NOTE: GetResult mappers for plain SQL are only generated for tables where Slick knows how to map the types of all columns.
  import scala.slick.jdbc.{GetResult => GR}

  /** DDL for all tables. Call .create to execute. */
  lazy val ddl = Address.ddl ++ Detail.ddl ++ User.ddl

  /** Entity class storing rows of table Address
   *  @param id Database column id DBType(serial), AutoInc, PrimaryKey
   *  @param userId Database column user_id DBType(int4)
   *  @param street Database column street DBType(varchar), Length(2147483647,true) */
  case class AddressRow(id: Int, userId: Int, street: String)
  /** GetResult implicit for fetching AddressRow objects using plain SQL queries */
  implicit def GetResultAddressRow(implicit e0: GR[Int], e1: GR[String]): GR[AddressRow] = GR{
    prs => import prs._
    AddressRow.tupled((<<[Int], <<[Int], <<[String]))
  }
  /** Table description of table address. Objects of this class serve as prototypes for rows in queries. */
  class Address(_tableTag: Tag) extends Table[AddressRow](_tableTag, "address") {
    def * = (id, userId, street) <> (AddressRow.tupled, AddressRow.unapply)
    /** Maps whole row to an option. Useful for outer joins. */
    def ? = (id.?, userId.?, street.?).shaped.<>({r=>import r._; _1.map(_=> AddressRow.tupled((_1.get, _2.get, _3.get)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))

    /** Database column id DBType(serial), AutoInc, PrimaryKey */
    val id: Column[Int] = column[Int]("id", O.AutoInc, O.PrimaryKey)
    /** Database column user_id DBType(int4) */
    val userId: Column[Int] = column[Int]("user_id")
    /** Database column street DBType(varchar), Length(2147483647,true) */
    val street: Column[String] = column[String]("street", O.Length(2147483647,varying=true))
  }
  /** Collection-like TableQuery object for table Address */
  lazy val Address = new TableQuery(tag => new Address(tag))

  /** Entity class storing rows of table Detail
   *  @param id Database column id DBType(serial), AutoInc, PrimaryKey
   *  @param userId Database column user_id DBType(int4)
   *  @param value Database column value DBType(varchar), Length(2147483647,true) */
  case class DetailRow(id: Int, userId: Int, value: String)
  /** GetResult implicit for fetching DetailRow objects using plain SQL queries */
  implicit def GetResultDetailRow(implicit e0: GR[Int], e1: GR[String]): GR[DetailRow] = GR{
    prs => import prs._
    DetailRow.tupled((<<[Int], <<[Int], <<[String]))
  }
  /** Table description of table detail. Objects of this class serve as prototypes for rows in queries. */
  class Detail(_tableTag: Tag) extends Table[DetailRow](_tableTag, "detail") {
    def * = (id, userId, value) <> (DetailRow.tupled, DetailRow.unapply)
    /** Maps whole row to an option. Useful for outer joins. */
    def ? = (id.?, userId.?, value.?).shaped.<>({r=>import r._; _1.map(_=> DetailRow.tupled((_1.get, _2.get, _3.get)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))

    /** Database column id DBType(serial), AutoInc, PrimaryKey */
    val id: Column[Int] = column[Int]("id", O.AutoInc, O.PrimaryKey)
    /** Database column user_id DBType(int4) */
    val userId: Column[Int] = column[Int]("user_id")
    /** Database column value DBType(varchar), Length(2147483647,true) */
    val value: Column[String] = column[String]("value", O.Length(2147483647,varying=true))
  }
  /** Collection-like TableQuery object for table Detail */
  lazy val Detail = new TableQuery(tag => new Detail(tag))

  /** Entity class storing rows of table User
   *  @param id Database column id DBType(serial), AutoInc, PrimaryKey
   *  @param name Database column name DBType(varchar), Length(2147483647,true) */
  case class UserRow(id: Int, name: String)
  /** GetResult implicit for fetching UserRow objects using plain SQL queries */
  implicit def GetResultUserRow(implicit e0: GR[Int], e1: GR[String]): GR[UserRow] = GR{
    prs => import prs._
    UserRow.tupled((<<[Int], <<[String]))
  }
  /** Table description of table user. Objects of this class serve as prototypes for rows in queries. */
  class User(_tableTag: Tag) extends Table[UserRow](_tableTag, "user") {
    def * = (id, name) <> (UserRow.tupled, UserRow.unapply)
    /** Maps whole row to an option. Useful for outer joins. */
    def ? = (id.?, name.?).shaped.<>({r=>import r._; _1.map(_=> UserRow.tupled((_1.get, _2.get)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))

    /** Database column id DBType(serial), AutoInc, PrimaryKey */
    val id: Column[Int] = column[Int]("id", O.AutoInc, O.PrimaryKey)
    /** Database column name DBType(varchar), Length(2147483647,true) */
    val name: Column[String] = column[String]("name", O.Length(2147483647,varying=true))
  }
  /** Collection-like TableQuery object for table User */
  lazy val User = new TableQuery(tag => new User(tag))
}

UPDATE: I now realize that .first in the subquery produces String where really I need it to be Column[String] Something like

Detail.filter(_.userId === user.id).map(_.value).max

does work, but then the subquery looks like this:

SELECT MAX(value) FROM Detail WHERE userId = user.id

when I need it to be looking more like this:

SELECT value FROM Detail WHERE userId = user.id LIMIT 1

Answer 1

I think you want something like this:

val q = for { 
  ((u, a), d) <- User.innerJoin(Addres).on(_.id === _.userId)
    .innerJoin(Detail).on(_._1.id === _.userId)
  } yield (u, a, d.value)

val result = q.list