scala skunk Multiple parameter Query: Passing List as Parameter to prepared statement

Question

I would like to query a db table using the following sql

  val ALL_BY_ID: Query[List[String] *: Int *: EmptyTuple, Store] =
   sql"""SELECT id, name, description, account, enterdate
      ,  company, modelid
       FROM   store
       WHERE id  IN $varchar  AND  modelid = $int4 
       """.query(storeDecoder)

here is the case class

 final case class Store(
  id: String,name: String,description: String,account: String,enterdate: Instant,company: String,modelid: Int)

and there is the storeDecoder

        val storeDecoder: Decoder[Store] = storeCodec.map {
case (id, name, description, account, enterdate, company, modelid) =>
  Store(id,name,description,account,toInstant(enterdate),company,modelid)}

This doesnt compile. Here the error

[error] 192 |    sql"""
[error] 193 |           SELECT id, name, description, account, enterdate, 
 [error] 195 |           WHERE id  IN $varchar  AND  modelid = $int4 
 [error] 196 |           """.query(storeDecoder)
 [error]     |    ^
 [error]     |Found:    skunk.Query[(String, Int), tradex.domain.model.store.Store]
 [error]     |Required: skunk.Query[(List[String], Int), tradex.domain.model.store.Store]

What's wrong with this?

Answer 1

You can't achieve that with ordinary val query. However you can achieve with fragment:

def ALL_BY_ID(lst: List[String], number: Int): AppliedFragment = {
  val query: Fragment[Void] =
    sql"""
      SELECT id, name, description, account, enterdate, company, modelid
      FROM store
    """
  query(Void) |+|
    AppliedFragment.apply[lst.type](sql" WHERE id IN(${varchar.list(lst)})", lst) |+|
    sql""" AND modelid = $int4""".apply(number)
}

And then call it like this:

val af = ALL_BY_ID(List("string", "string"), 1)
af.fragment.query(storeDecoder).queryList(af.argument)