Scala slick join table with grouped query

Question

I have two tables:

Shop

class ShopTable(tag: Tag) extends GenericTableShop, UUID {

 def * = (id.?, name, address) <> (Shop.tupled, Shop.unapply _)

 def name = column[String]("name")

 def address = column[String]("address")

}

val shopTable = TableQuery[ShopDAO.ShopTable]

Order

class OrderTable(tag: Tag) extends GenericTableOrder, UUID {

 def * = (id.?, shopId, amount) <> (Order.tupled, Order.unapply _)

 def shopId = column[UUID]("shop_id")

 def amount = column[Double]("amount")

}

val orderTable = TableQuery[OrderDAO.OrderTable]

I can get statictic (orders count, orders amount sum) for shops:

def getStatisticForShops(shopIds: List[UUID]): Future[Seq(UUID, Int, Double)] = {
  searchStatisticsByShopIds(shopIds).map(orderStatistics => 
    shopIds.map(shopId => {
      val o = orderStatistics.find(_._1 == shopId)
      (
        shopId,
        o.map(_._2).getOrElse(0),
        o.map(_._3).getOrElse(0.0)
      )
    })
  )
}

 def searchStatisticsByShopIds(shopIds: List[UUID]): Future[Seq(UUID, Int, Double)] = 
  db.run(searchStatisticsByShopIdsCompiled(shopIds).result)

 private val searchStatisticsByShopIdsCompiled = Compiled((shopIds: Rep[List[(UUID)]]) =>
  orderTable.filter(_.shopId === shopIds.any)
    .groupBy(_.shopId)
    .map { case (shopId, row) =>
      (shopId, row.length, row.map(_.amount).sum.get)
    }
 )

By I need sorting and filtering shopTable by orders count.

How I can join grouped orderTable to shopTable with zero values for missing shops?

I want have such a request:

| id(shopId) | name | address | ordersCount | ordersAmount |
|     id1    | name | address |       4     |    200.0     |
|     id2    | name | address |       0     |     0.0      |
|     id3    | name | address |       2     |    300.0     |

I use scala 2.12.6, slick 2.12:3.0.1, play-slick 3.0.1, slick-pg 0.16.3

P.S. I may have found a solution

val shopsOrdersQuery: Query[(Rep[UUID], Rep[Int], Rep[Double]), (UUID, Int, Double), Seq] = searchShopsOrdersCompiled.extract

// Query shops with orders for sorting and filtering
val allShopsOrdersQueryQuery = shopTable.joinLeft(shopsOrdersQuery).on(_.id === _._1)
  .map(s => (s._1, s._2.map(_._2).getOrElse(0), s._2.map(_._3).getOrElse(0.0)))

private val searchShopsOrdersCompiled = Compiled(
  orderTable.groupBy(_.shopId)
    .map { case (shopId, row) =>
      (shopId, row.length, row.map(_.amount).sum.get)
    }
)

Viktor Tempelfeld Viktor Tempelfeld · Accepted Answer · 2021-04-14T15:34:55

Yes, this solution is work fine

val shopsOrdersQuery: Query[(Rep[UUID], Rep[Int], Rep[Double]), (UUID, Int, Double), Seq] = searchShopsOrdersCompiled.extract

// Query shops with orders for sorting and filtering
val allShopsOrdersQueryQuery = shopTable.joinLeft(shopsOrdersQuery).on(_.id === _._1)
  .map(s => (s._1, s._2.map(_._2).getOrElse(0), s._2.map(_._3).getOrElse(0.0)))

private val searchShopsOrdersCompiled = Compiled(
  orderTable.groupBy(_.shopId)
    .map { case (shopId, row) =>
      (shopId, row.length, row.map(_.amount).sum.get)
    }
)

Scala slick join table with grouped query

1 Answers