CODEWITHSUNDEEP
scalaslick
fatlog
fatlog

Reputation: 1192

Slick.io - Handling Joined Tables

I have a simple query in Slick (2.1) which joins two tables in a one-to-many relationship. Defined roughly as follows...

class Users( tag: Tag ) extends Table[ User ]( tag, "users" )
    // each field here...
    def * = ( id, name ).shaped <> ( User.tupled, User.unapply)

class Items( tag: Tag ) extends Table[ Item ]( tag, "items" )
    // each field here...

    // foreign key to Users table
    def userId = column[ Int ]( "user_id")
    def user_fk = foreignKey( "users_fk", userId, Users )( _.id )
    def * = ( id, userId.?, description ).shaped <> ( Item.tupled, Item.unapply)

A single User can have multiple Items. The User case class I want to marshal to looks like...

case class User(id: Option[Int] = None, name:String, items:Option[List[Item]] = None)

I then query the database with an implicit join like this...

for{
    u <- Users
    i <- Items 
      if i.userId === u.id
  } yield(u, i)

This "runs" fine. However, the query obviously duplicates the "Users" record for each "Item" that belongs to the user giving...

List(
(User(Some(1),"User1Name"),Item(Some(1),Some(1),"Item Description 1")), 
(User(Some(1),"User1Name"),Item(Some(2),Some(1),"Item Description 2")))

Is there an elegant way of pulling the "many" part into the User case class? Whether it's Slick or Scala. What I would ideally like to end up with is...

User(Some(1),"User1Name",
    List(Item(Some(1),Some(1),"Item Description 1"),
         Item(Some(2),Some(1),"Item Description 2")))

Thanks!

Upvotes: 0

Views: 65

Answers (1)

Jeffrey Chung
Jeffrey Chung

Reputation: 19517

One way to do it in Scala:

val results = List((User(Some(1), "User1Name"), Item(Some(1), Some(1), "Item Description 1")),
                   (User(Some(1), "User1Name"), Item(Some(2), Some(1), "Item Description 2")))

val grouped = results.groupBy(_._1)
                     .map { case (user, item: List[(User, Item)]) =>
                       user.copy(items = Option(item.map(_._2))) }

This handles multiple distinct Users (grouped is an Iterable[User]).

Upvotes: 1

Related Questions