sql - How correctly built an object graph based on multi level join in Slick? -

i have model structure following:

group -> many parties -> many participants 

in on of api calls need single groups parties , it's participants attached.

this whole structure built on 4 tables:

• group
• party
• party_participant
• participant

naturally, sql it's pretty straight forward join combines of them. , trying slick. mu method dao class looks this:

def findonebykeyandaccountidwithpartiesandparticipants(key: uuid, accountid: int): future[option[journeygroup]] = {     val joins = journeygroups.groups join                 parties.parties on (_.id === _.journeygroupid) joinleft                 partiesparticipants.relations on (_._2.id === _.partyid) joinleft                 participants.participants on (_._2.map(_.participantid) === _.id)      val query = joins.filter(_._1._1._1.accountid === accountid).filter(_._1._1._1.key === key)      val q = {       (((journeygroup, party), partyparticipant), participant) <- query     } yield (journeygroup, party, participant)      val result = db.run(q.result)      result ????   } 

the problem here, result type of future[seq[(journeygroup, party, participant)]]

however, need future[option[journeygroup]]

note: case classes of journeygroup , party have sequences there children defined:

case class party(id: option[int] = none,                  partytype: parties.type.value,                  journeygroupid: int,                  accountid: int,                  participants: seq[participant] = seq.empty[participant]) 

and

case class journeygroup(id: option[int] = none,                         key: uuid,                         name: string,                         data: option[jsvalue],                         accountid: int,                         parties: seq[party] = seq.empty[party]) 

so both can hold descendants.

what correct way convert result need? or in wrong direction?

also, statement correct: participants.participants on (_._2.map(_.participantid) === _.id) ?

i ended doing this:

journeygroupdao.findonebykeyandaccountidwithpartiesandparticipants(key, account.id.get) map { data =>        val groupedbyjourneygroup = data.groupby(_._1)        groupedbyjourneygroup.map { case (group, rows) =>          val parties = rows.map(_._2).distinct map { party =>           val participants = rows.filter(r => r._2.id == party.id).flatmap(_._3)            party.copy(participants = participants)          }            group.copy(parties = parties)        }.headoption      } 

where dao method's signature is:

def findonebykeyandaccountidwithpartiesandparticipants(key: uuid, accountid: int): future[seq[(journeygroup, party, option[participant])]]