provide Get instance for Seq

Question

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:

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.
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..
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.
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.
Any another way?

Thanks.

If you pay attention, your query is returning a tuple `(Long, String, String)` which means that `sql.query[(Long, String, String)].to[Seq]` should work, now, you can do some processing group your result by id in order to get the `Seq[Entity]` that you need, if you like to do this with doobie, first you'll need to update you query to return an array on of values instead but analyze whether you really get benefits from that. — AlexITC, Nov 14 '19 at 21:10

score 4 · Accepted Answer · answered Nov 16 '19 at 11:33

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.

provide Get instance for Seq

1 Answers1