There is already a topic about array aggregation found here:
Support of PostgreSQL specific array_agg function in scala frameworks?
However, since it's not currently supported, I'm finding it hard to do a few things with slick. Heres an simple example.
Say for example, that I run a business hosting parties. Each party there are guests, hosts:
Party(id: Option[Int], name: String)
Guest(id: Option[Int], name: String)
Host(id: Option[Int], name: String)
PartyGuests(partyId, guestId: Int)
PartyHosts(partyId, hostId: Int)
And I want a table of all the parties, displaying all the guests and hosts like this:
PartyId |Guests |Hosts ------------|-----------------------|------ 1 |Adam, Julie, Jack |Tom, Jamie, John 2 |Joe, Jim, Jake |Tom, Tony, James 3 |Amy, Sally |Jenny
My solutions is this:
// The map is: partyId -> (guestNamesList, hostNamesList)
val partyTable: Map[Int, (List[String], List[String])] = (for {
party <- db.run(parties.result.list)
guest <- db.run(partyGuests
.filter(_.partyId === party.id)
.flatMap(_.guest)
.result.list)
host <- db.run(partyHosts
.filter(_.partyId === party.id)
.flatMap(_.host)
.result.list)
} yield (party, guest, host))
.groupBy { case (party, guest, host) =>
party.id
}
.mapValues { values =>
val myGuests = values.map(_._2).distinct.map(_.name)
val myHosts = values.map(_._3).distinct.map(_.name)
(myGuests, myHosts)
}
However this solution is very space consuming on large data sets. Does anyone know of a better solution?
