scala slick left join multiple tables fetch result as a sequence

Question

I'm using scala version 2.12.6 and slick version 3.2.3

I would like to join multiple tables from a postgres database with

1:1 relationship between description and product
1:0/1 relation between product and price and
1:n relationship between price and currency

the desired output:

(description, product, price, Seq[Option[currencies])

what I have so far:

 val query = (for {
      (((description, product), price), currencies) <- ((descriptions.filter(_.language === lang.locale.toLanguageTag()) join products on (_.productId === _.id))  joinLeft prices on (_._2.id === _.priceId)) joinLeft currencies on (_._2.map(_.id) === _.priceId)
    } yield (description, product, price, currencies))

but this code results in

(description, product, price, [Option[currencies])

with duplicate rows

Leo C · Accepted Answer · 2018-08-06T15:14:08.657

By SQL-compliant standard, your join/left-join query is rightfully yielding (description, product, Option[price], Option[currencies]).

To generate Seq[Option[currencies]] instead of Option[currencies], I'm not sure it's doable with Slick. In theory, a groupBy should do, like:

val groupQuery = joinQuery.
  groupBy{ case (dsc ,prd, prc, cur) => (dsc ,prd, prc) }.
  map{
    case ((dsc ,prd, prc), group) => (dsc ,prd, prc, group.map(_._2).someAggFunction)
  }

Unfortunately, Slick doesn't support groupBy aggregation such as PostgreSQL's array_agg or Spark's collect_list. Even if you're by any chance using slick-pg on a PostgreSQL database, its arrayAgg method doesn't work as advertised, last I checked.

One workaround as shown in this SO link is to use Scala's groupBy on the client side, but it's obviously unscalable.

scala slick left join multiple tables fetch result as a sequence

1 Answers1