writing script in scala to join two mysql tables and create one object (quill)

Question

I have two mysql tables: Owners & Pets

Owner case class:

Owner(id: Int, name: String, age: Int)

Pet case class:

Pet(id: Int, ownerId: Int, type: String, name: String)

I want to create out of those tables list of OwnerAndPets:

case class OwnerAndPets(ownerId: Int,
                        name: String,
                        age: String,
                        pets: List[Pet])

(its for migrations purposes, I want to move those tables to be a collection of mongodb, which the collection documents would be OwnerAndPets objects)

I have two issues:

1. when I use join with quill on Owner & Pet I get list of tuples [(Owner, Pet)] and if I have few pets for an owner I will get:

   [(Owner(1, "john", 30), Pet(3,1,"dog","max")), (Owner(1, "john", 30), Pet(4,1,"cat","snow"))]

   I need it as (Owner(1, "john", 30), [Pet(3,1,"dog","max"), Pet(4,1,"cat","snow")])

   how can I make it like this?

2. when I use join with quill on Owner & Pet I will not get owners that dont have pets and its fine cause this is what it supposed to be, but in my script in this case I would want to create object like:

   OwnerAndPets(Owner(2, "mark", 30), List[])

Would appreciate any help, thanks

this is my join query:

query[Owner].join(query[Pet]).on((o, p) => o.id == p.o_id)

Answer 1

Your question highlights one of the major differences between FRM (Functional Relational Mapping) systems like Quill and Slick as opposed to ORMs like Hibernate. The purpose of FRM systems is not to build a particular domain-specific object hierarchy e.g. OwnersAndPets, but rather, to be able translate a single database query into some set of objects that can reasonably be pulled out of that single query's result set - this is typically a tuple. This means it is up to you to join the tuples (Owner_N, Pet_1-N) object into a single OwnersAndPets object in memory. Typically this can be done via groupBy and map operators:

run(query[Owner].join(query[Pet]).on((o, p) => o.id == p.o_id))
  .groupBy(_._1)
  .map({case (owner,ownerPetList) => 
    OwnerAndPets(
      owner.id,owner.name,owner.age+"", // Not sure why you made 'age' a String in OwnerAndPets
      ownerPetList.map(_._2))
  })

That said, there are some database vendors (e.g. Postgres) that internally implement array types so in some cases you can do the join on the database-level but this is not the case for MySQL and many others.