How to express Postgres arrays with Scala Slick

Question

I have a table in Postgres 9.5 with this structure:

my_table (id Integer, images_ranks image_rank[]);

where image_rank is:

CREATE TYPE image_rank AS (image_url text, thumbnail_rank integer);

I am struggling to express something like that in Slick (3.1):

case class MyTable (id: Int, imagesRanks: Seq[Option[ImageRank]])

implicit val propertyMyTableResult = GetResult(r => MyTable(r.<<, r.<<)
implicit val propertyImageRankResult = GetResult[Option[ImageRank]] { r => ImageRank(r.<<, r.<<)) }

What is the correct way to do it?

UPDATE

This wrap (following the answer from n1r3) is blocking me at the moment:

implicit val ImageRankWrapper: JdbcType[List[ImageRank]] = new SimpleArrayJdbcType[ImageRank]("image_rank[]").to(_.toList)

UPDATE 2

Still not sure what is wrong. I have added the code as you suggested, but it still complains with:

could not find implicit value for parameter rconv: slick.jdbc.GetResult[database.rows.MyTable].

So I have added:

implicit val myTableResult = GetResult[MyTable](r => MyTable(r.<<, r.<<))

which returns:

diverging implicit expansion for type slick.jdbc.GetResult[T1]
starting with object GetStringOption in object GetResult
         r.<<, r.<<))

This is my configuration:

import com.github.tminglei.slickpg._
import database.rows.ImageRank

trait CustomPostgresProfile extends ExPostgresProfile
  with PgArraySupport
  with PgPlayJsonSupport
  with PgPostGISSupport
  with PgEnumSupport
  with PgDate2Support {

  // Use PostgreSQL JSONB support
  def pgjson = "jsonb"

  override val api = MyAPI
  object MyAPI extends API
    with ArrayImplicits
    with JsonImplicits
    with PostGISImplicits
    with DateTimeImplicits {
    implicit val imageRankListTypeMapper =
      new AdvancedArrayJdbcType[ImageRank]("image_rank",
        str => utils.SimpleArrayUtils.fromString[ImageRank](s => {
          val ImageRankRegex = "ImageRank\\((.*),(\\d+)\\)".r
          s match {
            case ImageRankRegex(imageUrl, thumbnailRank) =>
              ImageRank(imageUrl, thumbnailRank.toInt)
            case _ =>
              println(s"$s is not ImageRank")
              ImageRank("", 0)
          }
        })(str).orNull,
        imageRanks => utils.SimpleArrayUtils.mkString[ImageRank](_.toString)(imageRanks)
      ).to(_.toList)
  }
}

object CustomPostgresProfile extends CustomPostgresProfile


abstract class DatabaseProfile(val provider: DatabaseConfigProvider) {
  val config = provider.get[CustomPostgresProfile]
  val profile = config.profile
  val db = config.db
}

trait PropertyDataDatabase {
    self: DatabaseProfile =>
    .....

I'm not sure Seq[Option[ImageRank]] is correct, don't you want Option[Seq[ImageRank]] or simply Seq[ImageRank]? — n1r3
– n1r3, Commented Oct 19, 2017 at 8:33
Using a regex kills mostly of the "safety" of typed languages. So I changed it to at least using "Option". Also, the values are not always there. I have tweaked a bit the code in the 'Mapper' by Dmitri. — Randomize
– Randomize, Commented Oct 19, 2017 at 8:38
Right. But one can assume the database is a trusted system, no? And just throw a good old RuntimeException if it's not? — n1r3
– n1r3, Commented Oct 19, 2017 at 9:14
I have removed the "option" and reverted to the Dmytro code, but I am still getting the same errors. — Randomize
– Randomize, Commented Oct 19, 2017 at 9:18

n1r3 · Accepted Answer · 2017-10-18 10:36:37Z

3

The best would be for you to use this excellent library: https://github.com/tminglei/slick-pg

If you would rather implement it yourself, have a look at the source code (https://github.com/tminglei/slick-pg/tree/master/core/src/main/scala/com/github/tminglei/slickpg/array) and get what you want from there.

answered Oct 18, 2017 at 10:36

n1r3

9,1233 gold badges20 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Randomize Over a year ago

Thank you. In the link you put, there is an example of MarketFinancialProduct as list element. How do I replace it with my ImageRank?

Dmytro Mitin Over a year ago

@Randomize See my answer.

Dmytro Mitin · Accepted Answer · 2017-10-18 19:18:57Z

What is ImageRank? Write its definition. I presume it's a case class

case class ImageRank(imageUrl: String, thumbnailRank: Int)

I guess that besides slick-pg mapper for specific array

implicit val imageRankListTypeMapper =
  new AdvancedArrayJdbcType[ImageRank]("image_rank",
    str => utils.SimpleArrayUtils.fromString[ImageRank](s => {
      val ImageRankRegex = "ImageRank\\((.*),(\\d+)\\)".r
      s match {
        case ImageRankRegex(imageUrl, thumbnailRank) =>
          ImageRank(imageUrl, thumbnailRank.toInt)
        case _ =>
          println(s"$s is not ImageRank")
          ImageRank("", 0)
      }
    })(str).orNull,
    imageRanks => utils.SimpleArrayUtils.mkString[ImageRank](_.toString)(imageRanks)
  ).to(_.toList)

you should define slick mapper for ImageRank like

case class LiftedImageRank(imageUrl: Rep[String], thumbnailRank: Rep[Int])
implicit object ImageRankShape extends CaseClassShape(LiftedImageRank.tupled, ImageRank.tupled)

Documentation is here.

SimpleArrayJdbcType has String argument meaning SQL base type so I guess "image_rank[]" won't work.

Also SimpleArrayJdbcType works with arrays whose base type T is standard and for which ElemWitness[T] is defined in slick-pg. Since there is no ElemWitness[ImageRank] you should use AdvancedArrayJdbcType.

Collectives™ on Stack Overflow

How to express Postgres arrays with Scala Slick

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related