Scala Slick: Issues with groupBy and missing shapes

Question

I'm trying to use Slick to query a many-to-many relationship, but I'm running into a variety of errors, the most prominent being "Don't know how to unpack (User, Skill) to T and pack to G".

The structure of the tables is similar to the following:

case class User(val name: String, val picture: Option[URL], val id: Option[UUID])
object Users extends Table[User]("users") {
  def name = column[String]("name")
  def picture = column[Option[URL]]("picture")
  def id = column[UUID]("id")
  def * = name ~ picture ~ id.? <> (User, User.unapply _)
}

case class Skill(val name: String, val id: Option[UUID])
object Skills extends Table[Skill]("skill") {
  def name = column[String]("name")
  def id = column[UUID]("id")
  def * = name ~ id.? <> (Skill, Skill.unapply _)
}

case class UserSkill(val userId: UUID, val skillId: UUID, val id: Option[UUID])
object UserSkills extends Table[UserSkill]("user_skill") {
  def userId = column[UUID]("userId")
  def skillId = column[UUID]("skillId")
  def id = column[UUID]("id")
  def * = userId ~ skillId ~ id.? <> (UserSkill, UserSkill.unapply _)
  def user = foreignKey("userFK", userId, Users)(_.id)
  def skill = foreignKey("skillFK", skillId, Skills)(_.id)
}

Ultimately, what I want to achieve is something of the form

SELECT u.*, group_concat(s.name) FROM user_skill us, users u, skills s WHERE us.skillId = s.id && us.userId = u.id GROUP BY u.id

but before I spend the time trying to get group_concat to work as well, I have been trying to produce the simpler query (which I believe is still valid...)

SELECT u.* FROM user_skill us, users u, skills s WHERE us.skillId = s.id && us.userId = u.id GROUP BY u.id

I've tried a variety of scala code to produce this query, but an example of what causes the shape error above is

(for {
  us <- UserSkills
  user <- us.user
  skill <- us.skill
} yield (user, skill)).groupBy(_._1.id).map { case(_, xs) => xs.first }

Similarly, the following produces a packing error regarding "User" instead of "(User, Skill)"

(for {
  us <- UserSkills
  user <- us.user
  skill <- us.skill
} yield (user, skill)).groupBy(_._1.id).map { case(_, xs) => xs.map(_._1).first }

If anyone has any suggestions, I would be very grateful: I've spent most of today and yesterday scouring google/google groups as well as the slick source, but I haven't a solution yet.

(Also, I'm using postgre so group_concat would actually be string_agg)

EDIT

So it seems like when groupBy is used, the mapped projection gets applied because something like

(for {
  us <- UserSkills
  u   <- us.user
  s   <- us.skill
} yield (u,s)).map(_._1)

works fine because _._1 gives the type Users, which has a Shape since Users is a table. However, when we call xs.first (as we do when we call groupBy), we actually get back a mapped projection type (User, Skill), or if we apply map(_._1) first, we get the type User, which is not Users! As far as I can tell, there is no shape with User as the mixed type because the only shapes defined are for Shape[Column[T], T, Column[T]] and for a table T <: TableNode, Shape[T, NothingContainer#TableNothing, T] as defined in slick.lifted.Shape. Furthermore, if I do something like

(for {
  us <- UserSkills
  u   <- us.user
  s   <- us.skill
} yield (u,s))
  .groupBy(_._1.id)
  .map { case (_, xs) => xs.map(_._1.id).first }

I get a strange error of the form "NoSuchElementException: key not found: @1515100893", where the numeric key value changes each time. This is not the query I want, but it is a strange issue none the less.

I ended up writing a direct query in a situation when I had to join multiple tables. By the way, a better performant variant (provided u.id, s.id, us.userId and us.skillId are keys) might be: "SELECT u.*, group_concat(s.name) FROM user_skill us JOIN skills s ON us.skillId = s.id JOIN users u ON us.userId = u.id GROUP BY u.id" — Ashalynd
But anyway, when you group, you get a Map(id -> List((u,s)) and that's not exactly how it is described in your { case } expression. — Ashalynd

Alex Alex · Accepted Answer · 2013-10-13T05:26:17

I've run up against similar situations as well. While I love working with Scala and Slick, I do believe there are times when it is easier to denormalize an object in the database itself and link the Slick Table to a view.

For example, I have an application that has a Tree object that is normalized into several database tables. Since I'm comfortable with SQL, I think it is a cleaner solution than writing a plain Scala Slick query. The Scala code:

case class DbGFolder(id: String,
                     eTag: String,
                     url: String,
                     iconUrl: String,
                     title: String,
                     owner: String,
                     parents: Option[String],
                     children: Option[String],
                     scions: Option[String],
                     created: LocalDateTime,
                     modified: LocalDateTime)
object DbGFolders extends Table[DbGFolder]("gfolder_view") {
  def id = column[String]("id")
  def eTag = column[String]("e_tag")
  def url = column[String]("url")
  def iconUrl = column[String]("icon_url")
  def title = column[String]("title")
  def owner = column[String]("file_owner")
  def parents = column[String]("parent_str")
  def children = column[String]("child_str")
  def scions = column[String]("scion_str")
  def created = column[LocalDateTime]("created")
  def modified = column[LocalDateTime]("modified")
  def * = id ~ eTag ~ url ~ iconUrl ~ title ~ owner ~ parents.? ~
          children.? ~ scions.? ~ created ~ modified <> (DbGFolder, DbGFolder.unapply _)

  def findAll(implicit s: Session): List[GFolder] = {
    Query(DbGFolders).list().map {v =>
      GFolder(id = v.id,
              eTag = v.eTag,
              url = v.url,
              iconUrl = v.iconUrl,
              title = v.title,
              owner = v.owner,
              parents = v.parents.map { parentStr =>
                parentStr.split(",").toSet }.getOrElse(Set()),
              children = v.children.map{ childStr =>
                childStr.split(",").toSet }.getOrElse(Set()),
              scions = v.scions.map { scionStr =>
                scionStr.split(",").toSet }.getOrElse(Set()),
              created = v.created,
              modified = v.modified)
    }
  }
}

And the underlying (postgres) view:

CREATE VIEW scion_view AS
    WITH RECURSIVE scions(id, scion) AS (
      SELECT c.id, c.child
      FROM children AS c
      UNION ALL
      SELECT s.id, c.child
      FROM children AS c, scions AS s
      WHERE c.id = s.scion)
    SELECT * FROM scions ORDER BY id, scion;    

CREATE VIEW gfolder_view AS
  SELECT
    f.id, f.e_tag, f.url, f.icon_url, f.title, m.name, f.file_owner,
    p.parent_str, c.child_str, s.scion_str, f.created, f.modified
  FROM
    gfiles AS f
      JOIN mimes AS m ON (f.mime_type = m.name)
      LEFT JOIN (SELECT DISTINCT id, string_agg(parent, ',' ORDER BY parent) AS parent_str
                 FROM parents GROUP BY id) AS p ON (f.id = p.id)
      LEFT JOIN (SELECT DISTINCT id, string_agg(child, ',' ORDER BY child) AS child_str
                 FROM children GROUP BY id) AS c ON (f.id = c.id)
      LEFT JOIN (SELECT DISTINCT id, string_agg(scion, ',' ORDER BY scion) AS scion_str
                 FROM scion_view GROUP BY id) AS s ON (f.id = s.id)
  WHERE
    m.category = 'folder';

Scala Slick: Issues with groupBy and missing shapes

2 Answers