How to put data from various tables into separate lists via one request

Question

For instance, I have some entity. Each entity has some attributes. DB looks something about:

 entity                entity_attribute
╔════╦════════╗       ╔════╦════════════╦═══════════╗
║ id ║ name   ║       ║ id ║ entity_id  ║ attribute ║
╠════╬════════╣       ╠════╬════════════╬═══════════╣
║ 1  ║  One   ║       ║ 1  ║ 1          ║ "aaa"     ║ 
║ 2  ║  Two   ║       ║ 2  ║ 1          ║ "bbb"     ║
║ 3  ║  Three ║       ║ 3  ║ 1          ║ "ccc"     ║
╚════╩════════╝       ║ 4  ║ 1          ║ "ddd"     ║
                      ║ 5  ║ 2          ║ "aa"      ║
                      ║ 6  ║ 2          ║ "bb"      ║
                      ╚════╩════════════╩═══════════╝

My models looks like this:

case class Entity(id: Long, name: String)

case class Entityattribute(id: Long, entityId: Long, attribute: String)

And I am trying to get entitites with attributes(Important: No join) via doobie:

(for {
      entitites <- sql"select id, name from entity"query[Entity].to[List]
      attributes <- (sql"select id, entity_id, attribute from entity_attribute where " ++ 
                    Fragments.in(
                      fr"entity_id", 
                      NonEmptyList.fromListUnsafe(entities.map(_.id)))   //Unsafe, just example
                    ).query[EntityAttribute].to[List]
      } yield entitites ++ attributes).transact(xa)

The result is one List[Product]:

List(
  Entity(1, One),
  Entity(2, Two),
  Entity(3, Three),
  EntityAttribute(1,1,"aaa"),
  EntityAttribute(2,1,"bbb"),
  EntityAttribute(3,1,"ccc"),
  EntityAttribute(4,1,"ddd"),
  EntityAttribute(5,2,"aa"),
  EntityAttribute(6,2,"bb")
)

How to modify doobie request to get result into two separate List[Entity] and List[EntityAttribute]?

Answer 1

Lists in Scala are homogeneous, that means compiler will try to find upper bound for types of all objects in the list.

For your case, upper bound for Entity and EntityAttribute is Product.

What you can do to preserve original types is just returning a tuple containing two lists: List[Entity] and List[EntityAttribute].

} yield (entitites, attributes)).transact(xa)

Then you could retrieve lists by just pattern matching on a tuple:

result.map {
   case (entities, attributes) => /* do something */
}