CODEWITHSUNDEEP
scaladoobie
Oleg
Oleg

Reputation: 941

provide Get instance for Seq

For instance, I have some entities with some params, and two database tables, representating this entities:

 entity                 param
╔════╦═════════╗       ╔═══════════╦════════╗
║ id ║ name    ║       ║ entity_id ║ value  ║
╠════╬═════════╣       ╠═══════════╬════════╣
║ 1  ║ "One"   ║       ║ 1         ║ "aaa"  ║
║ 2  ║ "Two"   ║       ║ 1         ║ "bbb"  ║
║ 3  ║ "Three" ║       ║ 1         ║ "ccc"  ║
╚════╩═════════╝       ╚═══════════╩════════╝

And a scala model:

case class Entity(id: Long, name: String, params: Seq[String])

And I want to retreive this data via Doobie, but I can't to do it directly to the Entity instance, cause params is a Seq of Strings, not just String:

val sql = sql"select e.id, e.name, p.value from entity e left join param p on e.id = p.entity_id"

sql.query[Entity].to[Seq]   //Error Cannot find or construct a Read instance for type: Entity

Is where any trick to provide Get instance for Seq?

If not, what is the way, Doobie offers to retrieve such data:

  1. Write primitives instead of the Entity type:
    sql.query[(Long, String, String)].to[Seq] and compose this Seq of tuples to the Entity instance.
    Potentially not convenient, cause tables may have a lot of columns, what leads to copypaste this long tuple to the every new query.
  2. Compose this primitives to anothes case classes:
    case class EntityRow(id: Long, name: String)
    case class ParamRow(value: String)
    sql.query[(EntityRow, ParamRow)].to[Seq] and compose to the Entity instance like in 1..
  3. Like 2., but using HNil:
    val entity = Long :: String :: HNil
    val param = String :: HNil
    sql.query[entity ++ param].to[Seq] and compose to the Entity instance like in 1..
    I do not know any advantages or disadvantages of this way, as shapeless is a new thing to me.
  4. Retrieve data with two separate queries:
    val entities = sql"select id, name from entity".query[EntityRow].to[Seq]
    val params = sql"select value from param".query[ParamRow].to[Seq]
    Probably not such perfomant as via single query.
  5. Any another way?

Thanks.

Upvotes: 4

Views: 568

Answers (1)

Rajan Chauhan
Rajan Chauhan

Reputation: 1375

Your case class Entity expects params to be grouped together with id and name column as a list of strings,
whereas the sql query select e.id, e.name, p.value from entity e left join param p on e.id = p.entity_id will return you a rows containing id, name and value 

joined entity
╔════╦═════════╦════════╗
║ id ║ name    ║ value  ║
╠════╬═════════╬════════╣
║ 1  ║ "One"   ║ "aaa"  ║
║ 1  ║ "One"   ║ "bbb"  ║
║ 1  ║ "One"   ║ "ccc"  ║
║ 2  ║ "Two"   ║        ║
║ 3  ║ "Three" ║        ║
╚════╩═════════╩════════╝

This is not exactly what you want. To achieve this we need to rewrite the sql query like below 

val entities = sql"select e.id, e.name, GROUP_CONCAT(p.value SEPARATOR ',') as params from entity e left join param p on e.id = p.entity_id group by e.id, e.name".query[Entity]

Now you can map easily your sql query output to your case class.

Upvotes: 4

Related Questions