Translate nested join and groupby query to Slick 3.0

Question

I'm implementing a todo list. A user can have multiple lists and a list can have multiple users. I want to be able to retrieve all the lists for a user, where each of these lists contain a list of the users for which it's shared (including the owner). Not succeeding implementing this query.

The table definitions:

case class DBList(id: Int, uuid: String, name: String)
class Lists(tag: Tag) extends Table[DBList](tag, "list") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc) // This is the primary key column
  def uuid = column[String]("uuid")
  def name = column[String]("name")
  // Every table needs a * projection with the same type as the table's type parameter
  def * = (id, uuid, name) <> (DBList.tupled, DBList.unapply)
}
val lists = TableQuery[Lists]

case class DBUser(id: Int, uuid: String, email: String, password: String, firstName: String, lastName: String)
// Shared user projection, this is the data of other users which a user who shared an item can see
case class DBSharedUser(id: Int, uuid: String, email: String, firstName: String, lastName: String, provider: String)
class Users(tag: Tag) extends Table[DBUser](tag, "user") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc) // This is the primary key column
  def uuid = column[String]("uuid")    
  def email = column[String]("email")    
  def password = column[String]("password")    
  def firstName = column[String]("first_name")    
  def lastName = column[String]("last_name")    
  def * = (id, uuid, email, password, firstName, lastName) <> (DBUser.tupled, DBUser.unapply)
  def sharedUser = (id, uuid, email, firstName, lastName) <> (DBSharedUser.tupled, DBSharedUser.unapply)
}
val users = TableQuery[Users]


// relation n:n user-list
case class DBListToUser(listUuid: String, userUuid: String)
class ListToUsers(tag: Tag) extends Table[DBListToUser](tag, "list_user") {
  def listUuid = column[String]("list_uuid")
  def userUuid = column[String]("user_uuid")    
  def * = (listUuid, userUuid) <> (DBListToUser.tupled, DBListToUser.unapply)
  def pk = primaryKey("list_user_unique", (listUuid, userUuid)) 
}
val listToUsers = TableQuery[ListToUsers]

I created an additional class to hold the database list object + the users, my goal is to map the query result somehow to instances of this class.

case class DBListWithSharedUsers(list: DBList, sharedUsers: Seq[DBSharedUser])

This is the SQL query for most of it, it gets first all the lists for the user (inner query) then it does a join of lists with list_user with user in order to get the rest of the data and the users for each list, then it filters with the inner query. It doesn't contain the group by part

select * from list inner join list_user on list.uuid=list_user.list_uuid inner join user on user.uuid=list_user.user_uuid where list.uuid in (
  select (list_uuid) from list_user where user_uuid=<myUserUuuid>
);

I tested it and it works. I'm trying to implement it in Slick but I'm getting a compiler error. I also don't know if the structure in that part is correct, but haven't been able to come up with a better one.

def findLists(user: User) = {
  val listsUsersJoin = listToUsers join lists join users on {
    case ((listToUser, list), user) =>
      listToUser.listUuid === list.uuid &&
      listToUser.userUuid === user.uuid
  }

  // get all the lists for the user (corresponds to inner query in above SQL)
  val queryToGetListsForUser = listToUsers.filter(_.userUuid===user.uuid)

  // map to uuids
  val queryToGetListsUuidsForUser: Query[Rep[String], String, Seq] = queryToGetListsForUser.map { ltu => ltu.listUuid }

  // create query that mirrors SQL above (problems):
  val queryToGetListsWithSharedUsers = (for {
    listsUuids <- queryToGetListsUuidsForUser
    ((listToUsers, lists), users) <- listsUsersJoin
    if lists.uuid.inSet(listsUuids) // error because inSet requires a traversable and passing a ListToUsers
  } yield (lists))

  // group - doesn't compile because above doesn't compile:
  queryToGetListsWithSharedUsers.groupBy {case (list, user) =>
    list.uuid
  }
...
}

How can I fix this?

Thanks in advance

Edit:

I put together this emergency solution (at least it compiles), where I execute the query using raw SQL and then do the grouping programmatically, it looks like this:

case class ResultTmp(listId: Int, listUuid: String, listName: String, userId:Int, userUuid: String, userEmail: String, userFirstName: String, userLastName: String, provider: String)

implicit val getListResult = GetResult(r => ResultTmp(r.nextInt, r.nextString, r.nextString, r.nextInt, r.nextString, r.nextString, r.nextString, r.nextString, r.nextString))

val a = sql"""select (list.id, list.uuid, list.name, user.id, user.uuid, user.email, user.first_name, user.last_name, user.provider) from list inner join list_user on list.uuid=list_user.list_uuid inner join user on user.uuid=list_user.user_uuid where list.uuid in (
  select (list_uuid) from list_user where user_uuid=${user.uuid}
);""".as[ResultTmp]

val result: Future[Vector[ResultTmp]] = db.run(a)

val res: Future[Seq[DBListWithSharedUsers]] = result.map {resultsTmp =>
  val myMap: Map[String, Vector[ResultTmp]] = resultsTmp.groupBy { resultTmp => resultTmp.listUuid }

  val r: Iterable[DBListWithSharedUsers] = myMap.map {case (listUuid, resultsTmp) =>

    val first = resultsTmp(0)

    val list = DBList(first.listId, listUuid, first.listName)

    val users: Seq[DBSharedUser] = resultsTmp.map { resultTmp => 
      DBSharedUser(resultTmp.userId, resultTmp.userUuid, resultTmp.userEmail, resultTmp.userFirstName, resultTmp.userLastName, resultTmp.provider)
    }

    DBListWithSharedUsers(list, users)
  }

  r.toSeq
}

But that's just horrible, how do I get it working the normal way?

Edit 2:

I'm experimenting with monadic joins but also stuck here. For example something like this would get all the lists for a given user:

val listsUsersJoin = for {
    list <- lists
    listToUser <- listToUsers 
    user_ <- users if user_.uuid === user.uuid

} yield (list.uuid, list.name, user.uuid, user.firstName ...)

but this is not enough because I need the get also all the users for those lists, so I need 2 queries. So I need to get first the lists for the user and then find all the users for those lists, something like:

val queryToGetListsForUser = listToUsers.filter(_.userUuid===user.uuid)

val listsUsersJoin = for {
    list <- lists
    listToUser <- listToUsers 
    user_ <- users /* if list.uuid is in queryToGetListsForUser result  */
} yield (list.uuid, list.name, user.uuid, user.firstName ... )

But I don't know how to pass that to the join. I'm not even sure if groupBy, at least at database level is correct, so far I see this used only to aggregate the results to a single value, like count or avg. I need them in a collection.

Edit 3:

I don't know yet if this is right but the monadic join may be the path to the solution. This compiles:

val listsUsersJoin = for {
  listToUser <- listToUsers if listToUser.userUuid === user.uuid   // get the lists for the user
  list <- lists if list.uuid === listToUser.listUuid  // join with list
  listToUser2 <- listToUsers if list.uuid === listToUser.listUuid // get all the users for the lists  
  user_ <- users if user_.uuid === listToUser2.userUuid // join with user

} yield (list.uuid, list.name, user.uuid, user.email, user.firstName, user.lastName)

Answer 1

Ah, look at that, I came up with a solution. I still have to test if works but at least the compiler stopped shouting at it. I’ll edit this later if necessary.

val listsUsersJoin = for {
  listToUser <- listToUsers if listToUser.userUuid === user.uuid  
  list <- lists if list.uuid === listToUser.listUuid 
  listToUser2 <- listToUsers if list.uuid === listToUser.listUuid
  user_ <- users if user_.uuid === listToUser2.userUuid

} yield (list.id, list.uuid, list.name, user_.id, user_.uuid, user_.email, user_.firstName, user_.lastName, user_.provider)

val grouped = listsUsersJoin.groupBy(_._2)

val resultFuture = db.run(grouped.result).flatMap {groupedResults =>

  val futures: Seq[Future[DBListWithSharedUsers]] = groupedResults.map {groupedResult =>

    val listUuid = groupedResult._1
    val valueQuery = groupedResult._2

    db.run(valueQuery.result).map {valueResult =>

        val first = valueResult(0) // if there's a grouped result this should never be empty 

        val list = DBList(first._1, listUuid, first._3)         
        val users = valueResult.map {value =>
            DBSharedUser(value._4, value._5, value._6, value._7, value._8, value._9)
        }

        DBListWithSharedUsers(list, users)
    }
  }
  Future.sequence(futures)
}