sql - Translate nested join and groupby query to Slick 3.0 -

i'm implementing todo list. user can have multiple lists , list can have multiple users. want able retrieve lists user, each of these lists contain list of users it's shared (including owner). not succeeding implementing 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) // primary key column   def uuid = column[string]("uuid")   def name = column[string]("name")   // every table needs * projection same type 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, data of other users user shared 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) // 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 additional class hold database list object + users, goal map query result somehow instances of class.

case class dblistwithsharedusers(list: dblist, sharedusers: seq[dbshareduser]) 

this sql query of it, gets first lists user (inner query) join of lists list_user user in order rest of data , users each list, filters inner query. doesn't contain group part

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

i tested , works. i'm trying implement in slick i'm getting compiler error. don't know if structure in part correct, haven't been able come 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   }    // lists user (corresponds inner query in above sql)   val querytogetlistsforuser = listtousers.filter(_.useruuid===user.uuid)    // map uuids   val querytogetlistsuuidsforuser: query[rep[string], string, seq] = querytogetlistsforuser.map { ltu => ltu.listuuid }    // create query mirrors sql above (problems):   val querytogetlistswithsharedusers = (for {     listsuuids <- querytogetlistsuuidsforuser     ((listtousers, lists), users) <- listsusersjoin     if lists.uuid.inset(listsuuids) // error because inset requires traversable , passing listtousers   } yield (lists))    // group - doesn't compile because above doesn't compile:   querytogetlistswithsharedusers.groupby {case (list, user) =>     list.uuid   } ... } 

how can fix this?

thanks in advance

edit:

i put emergency solution (at least compiles), execute query using raw sql , grouping programmatically, looks 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 = sql"""select (list.id, list.uuid, list.name, user.id, user.uuid, user.email, user.first_name, user.last_name, user.provider) list inner join list_user on list.uuid=list_user.list_uuid inner join user on user.uuid=list_user.user_uuid list.uuid in (   select (list_uuid) list_user 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 horrible, how working normal way?

edit 2:

i'm experimenting monadic joins stuck here. example lists given user:

val listsusersjoin = {     list <- lists     listtouser <- listtousers      user_ <- users if user_.uuid === user.uuid  } yield (list.uuid, list.name, user.uuid, user.firstname ...) 

but not enough because need users lists, need 2 queries. need first lists user , find users lists, like:

val querytogetlistsforuser = listtousers.filter(_.useruuid===user.uuid)  val listsusersjoin = {     list <- lists     listtouser <- listtousers      user_ <- users /* if list.uuid in querytogetlistsforuser result  */ } yield (list.uuid, list.name, user.uuid, user.firstname ... ) 

but don't know how pass join. i'm not sure if groupby, @ least @ database level correct, far see used aggregate results single value, count or avg. need them in collection.

edit 3:

i don't know yet if right monadic join may path solution. compiles:

val listsusersjoin = {   listtouser <- listtousers if listtouser.useruuid === user.uuid   // lists user   list <- lists if list.uuid === listtouser.listuuid  // join list   listtouser2 <- listtousers if list.uuid === listtouser.listuuid // users lists     user_ <- users if user_.uuid === listtouser2.useruuid // join user  } yield (list.uuid, list.name, user.uuid, user.email, user.firstname, user.lastname) 

ah, @ that, came solution. still have test if works @ least compiler stopped shouting @ it. i’ll edit later if necessary.

val listsusersjoin = {   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 grouped result should never 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) }