`selectByIds` and `deleteByIds` for composite key types

[oyvindberg]

Finally found an implementation of selectByIds(Array[CompositeId]) which made it possible to thread things through JDBC. The solution was to split the array into arrays of the components:

for a composite ID type

case class SalesterritoryhistoryId(businessentityid: BusinessentityId, startdate: TypoLocalDateTime, territoryid: SalesterritoryId)

This now works:

  override def selectByIds(compositeIds: Array[SalesterritoryhistoryId]): ZStream[ZConnection, Throwable, SalesterritoryhistoryRow] = {
    val businessentityid = compositeIds.map(_.businessentityid)
    val startdate = compositeIds.map(_.startdate)
    val territoryid = compositeIds.map(_.territoryid)
    sql"""select "businessentityid", "territoryid", "startdate"::text, "enddate"::text, "rowguid", "modifieddate"::text
          from sales.salesterritoryhistory
          where ("businessentityid", "startdate", "territoryid")
          in (select unnest(${businessentityid}), unnest(${startdate}), unnest(${territoryid}))
       """.query(using SalesterritoryhistoryRow.jdbcDecoder).selectStream()

  }

given this it was also easy to implement deleteByIds in the same manner:

  override def deleteByIds(compositeIds: Array[SalesterritoryhistoryId])(implicit c: Connection): Int = {
    val businessentityid = compositeIds.map(_.businessentityid)
    val startdate = compositeIds.map(_.startdate)
    val territoryid = compositeIds.map(_.territoryid)
    SQL"""delete
          from sales.salesterritoryhistory
          where ("businessentityid", "startdate", "territoryid")
          in (select unnest(${businessentityid}), unnest(${startdate}), unnest(${territoryid}))
       """.executeUpdate()

  }