[slick-1.0.1-RC1] Best way to map a one-to-many collection

[Pablo Fernandez]

Hey,

I'm having a lot of trouble mapping what I think is a pretty common case. Reducing the problem scope, what I have is:

  case class Container(id: Option[Long], name: String, items: Seq[Item])

  case class Item(name: String)

With tables that look like:

containers

========

id -> number (autoinc)

name -> varchar

items

========

id -> number (autoinc)

name -> varchar

container_id -> number (fk on containers.id)

What's the best way to retrieve all Containers with their "items" collection populated?

Thanks so much.

[Pablo Fernandez]

Note that I'm using the "case class mappings", like:

  object ContainerTable extends Table[Container]("containers")

[Martin Grotzke]

Hi all,

picking up this rather old thread.

I have basically the same question, with slick 1.0.1. I have a Product

with (one-to-many) ProductAttributes, the case classes and table mappings

look like this:

case class Product(id: Option[Long], ...)

object Products extends Table[Product]("products") {

  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

  ...

}

case class ProductAttribute(id: Option[Long] = None, productId: Long, name: String, value: String)

object ProductAttributes extends Table[ProductAttribute]("product_attributes") {

  def id = column[Long]("id", O.PrimaryKey, O.AutoInc)

  def productId = column[Long]("product_id")

  def name = column[String]("name")

  def value = column[String]("value")

  def product = foreignKey("product_fk", productId, Products)(_.id)

  ...

}

To load a product with its attributes I came up with this query:

  def findById(id: Long)(implicit s:Session): Option[(Product, List[ProductAttribute])] = {

    def pa = for {

      id <- Parameters[Long]

      (p, a) <- Products leftJoin ProductAttributes on (_.id === _.productId)

      if p.id === id

    } yield (p, a)

    val emptyMap = Map.empty[Product, List[ProductAttribute]].withDefaultValue(Nil)

    val map = pa.foldLeft(id, emptyMap){ (res, entry) => 

      res.updated(entry._1, entry._2 :: res(entry._1))

    }

    map.find{ case (p, as) => p.id.get == id }

  }

Are there better ways to do this?

An issue of this solution is, that products that don't have any attributes

are not found, instead an error is thrown ("SlickException: Read NULL value

for column product_attributes.product_id").

To come around this the

  yield (p, a)

could be changed to

  yield (p, a.id.?, a.name.?, a.value.?)

The disadvantage is that the ProductAttribute has to be constructed manually then,

which is ok for a ProductAttribute because it has only 3 or 4 properties, but for classes

with much more properties it's getting more of a hurdle.

Is it planned to support s.th. like "yield (p, a.?)", or are there other solutions for this?

What's the common way to handle one-to-many associations with left/outer joins?

Thanx && cheers,

Martin

[Christopher Vogt]

Small remark to start with: You use Parameters[...] within your def. This means the query is pre-compiled, then executed, then thrown away, which makes the pre-compilation useless. Put it in a global place as in the following examples.

Regarding your question, eventually we will support something like this in Slick:

Products.leftJoinSeq(ProductAttributes).on(...).run : Seq[(Product,Seq[ProductAttribute])]

This would probably be the easiest API, but it will be a while until we get around to do that. Until then, I would go for the simplest approach, which currently involves two queries, one for the product and one per product for the attributes. If you only look up a single product this is fine:

object queries {
  def productById(id: Column[Long]) = Query(Products).filter(_.id === id)
  def attributesByProductId(id: Column[Long]) = Query(ProductAttributes).filter(_.productId === id)
}

object compiled {
  val productById = Parameters[Long].flatMap(queries.productById)
  val attributesByProductId = Parameters[Long].flatMap(queries.attributesByProductId)
}

object dao {
  def productWithAttributesById(id: Long)(implicit s: Session): Option[(Product, List[ProductAttribute])] =
    compiled.productById(id).list.headOption.map((_, compiled.attributesByProductId(id).list))
}

Alternatively (or if you want to lookup multiple products at once), you can use your outer join approach. Using groupBy and map is much clearer than fold in this case I think, so this is what I do in the following code. Regarding the NULL exception you get, outer joins in Slick currently require explicitly changing outer joined columns to option types using .? as they can become NULL even if normally they cannot. We will automate this in a future version of Slick. In my play-slick example app I integrated some functionality that allows you to at least call .? on complete entities instead of only individual columns, which reduces a lot of boiler plate here. See the .? calls in https://github.com/cvogt/play-slick/blob/scaladays2013/samples/computer-database/app/controllers/Application.scala#L197 , the definition of ? in https://github.com/cvogt/play-slick/blob/scaladays2013/samples/computer-database/app/models/schema.scala#L48 and the definition of mapOption in https://github.com/cvogt/play-slick/blob/scaladays2013/samples/computer-database/app/util/projections.fm

object queries {
  def productsWithAttributes = Query(Products)
    .leftJoin(Query(ProductAttributes))
    .on(_.id === _.productId)
    .map {
      // type attribute columns as Option as they may be null after outer join
      // we will likely add support in Slick for this to happen automatically
      case (p, a) => (p, (a.id.?, a.productId.?, a.name.?, a.value.?))
    }

  def productWithAttributesById(id: Column[Long]) = productsWithAttributes.filter(_._1.id === id)
}

object compiled {
  val productWithAttributesById = Parameters[Long].flatMap(queries.productWithAttributesById)
}

object dao {
  // makes the grouping re-usable for multiple products
  private def groupProductsWithAttributes(productsWithAttributes: List[(Product, (Option[Long], Option[Long], Option[String], Option[String]))]) =
    for (
      (p, group) <- productsWithAttributes.groupBy(_._1)
    ) yield {
      val attributes = group.map(_._2)
      (p, attributes.filterNot(_._1.isEmpty).map(a => ProductAttribute(a._1.get, a._2.get, a._3.get, a._4.get)))
    }

  def productWithAttributesById(id: Long)(implicit s: Session): Option[(Product, List[ProductAttribute])] =
    groupProductsWithAttributes(compiled.productWithAttributesById(id).list).toMap.filterKeys(_.id == id).headOption
}

A third approach would be full outer joining product and attributes on false. This puts them into a single query, which you can separate again later. However, full outer joins are currently broken in Slick https://www.assembla.com/spaces/typesafe-slick/tickets/267 .