How to write a One to Many Query with Scala Slick which returns something like this `(Model1, Option[Seq[Model2]])`

I know this question has been asked before but i cant figure it out. In my data model i have a news-model containing an arbitrary amount of images.

case class NewsDataModel(
                                newsId: Option[Long],
                                name: String,
                                description: String,
                                author: String,
                                creationDateTime: Option[OffsetDateTime]
                        )

case class Image(
                        id: Long,
                        url: String,
                        newsId: Long
                )

Now i want to query my db to get something like this (NewsDataModel, Option[Seq[Image]])

My query is currently implemented as followed:

val q = newsTable.joinLeft(imagesTable).on(_.newsId === _.newsId).result
db.run(q)

This evaluates to Future[Seq[(NewsDataModel, Option[Image])]]. I guess the right way to solve this would be to use the groupBy-function but i dont know how to implement it since this


val q = newsTable.joinLeft(imagesTable).on(_.newsId === _.newsId)
       .groupBy(_._1.newsId)
        .result

db.run(q)

evaluates to Future[Seq[(Option[Long], Query[(NewsTable, Rep[Option[ImagesTable]]), (NewsDataModel, Option[Image]), Seq])]]

Solution

Slick won't automatically create that data structure for you. (I find it helpful to think of Slick in terms of rows and tables and what you can do in portable SQL, and not in terms of "object-relational mappers" or similar).

What you'll want to do is convert the rows into the format you want in Scala, after the database layer. There are many ways you can do that.

Here's one way to do that.

Given this example data...

scala> case class NewsDataModel(newsId: Long)
class NewsDataModel

scala> case class Image(id: Long)
class Image

scala> val results = Seq(
     |  ( NewsDataModel(1L), Some(Image(1L))  ),
     |  ( NewsDataModel(1L), Some(Image(10L)) ),
     |  ( NewsDataModel(1L), None             ),
     |  ( NewsDataModel(2L), None             ),
     |  ( NewsDataModel(3L), Some(Image(3L))  ),
     | )
     |

val results: Seq[(NewsDataModel, Option[Image])] = List((NewsDataModel(1),Some(Image(1))), (NewsDataModel(1),Some(Image(10))), (NewsDataModel(1),None), (NewsDataModel(2),None), (NewsDataModel(3),Some(Image(3))))

We can group by the key:

scala> val groups = results.groupBy { case (key, values) => key }

val groups: scala.collection.immutable.Map[NewsDataModel,Seq[(NewsDataModel, Option[Image])]] = HashMap(NewsDataModel(3) -> List((NewsDataModel(3),Some(Image(3)))), NewsDataModel(1) -> List((NewsDataModel(1),Some(Image(1))), (NewsDataModel(1),Some(Image(10))), (NewsDataModel(1),None)), NewsDataModel(2) -> List((NewsDataModel(2),None)))

And convert that into something like the type you want:

scala> val flat = groups.map { case (key, seq) => key -> seq.flatMap(_._2) }

val flat: scala.collection.immutable.Map[NewsDataModel,Seq[Image]] = HashMap(NewsDataModel(3) -> List(Image(3)), NewsDataModel(1) -> List(Image(1), Image(10)), NewsDataModel(2) -> List())

That flat result is a map, but you can turn it into (for example) a List with the type signature (close to) the type you want:

scala> flat.toList

val res18: List[(NewsDataModel, Seq[Image])] = List((NewsDataModel(3),List(Image(3))), (NewsDataModel(1),List(Image(1), Image(10))), (NewsDataModel(2),List()))

You can find lots of ways to do that, but the point is you're doing it in Scala, not Slick (SQL). Note, in particular, the groupBy method I've used are the Scala one in the collection library, not the Slick ones (which would be a SQL GROUP BY clause). That is, I'm modifying the result of running the query, not modifying the query itself.

I'd suggest putting whateever conversion you want into a method and then applying it to the Slick action. For example:

def convert(input: Seq[(NewsDataModel, Option[Image])]): Seq[(NewsDataModel, Seq[Image])] =
   ??? // your implementation here

val action = newsTable.joinLeft(imagesTable).on(_.newsId === _.newsId).result
val convertedAction = action.map(convert)
db.run(convertedAction)