This post comes from a question I wrote and answered myself in Stack Overflow about the same topic. I will reorder it here and try to explain the solution I came across.
Initial situation
First of all I will explain what I had so you can understand the problem.
I had the following Categories table:
|
1 2 3 4 5 6 7 8 9 |
create table "CATEGORIES" ( "id" character varying(50) NOT NULL, "name" character varying(50) NOT NULL, "idParent" character varying(50), CONSTRAINT "CATEGORIES_pkey" PRIMARY KEY (id), CONSTRAINT "CATEGORIES_idParent_fkey" FOREIGN KEY ("idParent") REFERENCES "CATEGORIES" (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ); |
There you have categories. A category usually has an id and a name. In case it is a subcategory, it has also the attribute idParent, which is a foreign key pointing to the id of the parent category.
Having the following data:
|
1 2 3 4 |
id name idParent parent Parent child1 Child1 parent child2 Child2 parent |
I wanted to get the result in a map grouped by the parent category as key and having the sequence of subcategories as value, like this:
|
1 2 3 |
Map( (parent,Parent,None) -> Seq[(child1,Child1,parent),(child2,Child2,parent)] ) |
Scala Slick 3 Relationship to self
Now lets see how I managed to get this with Scala and Slick.
First of all, I defined the schema, with a query definition for subcategories, to help me later with the navigation between objects.
|
1 2 3 4 5 6 7 8 9 10 |
class CategoriesTable(tag: Tag) extends Table[Category](tag, "CATEGORIES") { def id = column[String]("id", O.PrimaryKey) def name = column[String]("name") def idParent = column[Option[String]]("idParent") def * = (id, name, idParent) <> (Category.tupled, Category.unapply) def categoryFK = foreignKey("category_fk", idParent, categories)(_.id.?) def subcategories = TableQuery[CategoriesTable].filter(id === _.idParent) } |
Having this, in the DAO I defined a TableQuery for the categories and the method findChildrenWithParents, which retrieves what I wanted:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
private val categories = TableQuery[CategoriesTable] def findChildrenWithParents() = { val result = db.run((for { c <- categories s <- c.subcategories } yield (c,s)).sortBy(_._1.name).result) result map { case categoryTuples => categoryTuples.groupBy(_._1).map{ case (k,v) => (k,v.map(_._2)) } } } |
What am I doing?
- I join the categories with their subcategories and sort everything by the parent category name in result.
- I group the result by the parent category.
- As the product of the groupBy also contains the parent category in the list, I map the value to only have subcategories.
Disclaimer
Now, this solution isn’t 100% perfect for me. If you pay attention, in result I have already told Slick to execute the query in the database with db.run. For me it would be better to have the groupBy already in the Slick query and then execute it. But I was having trouble with that, because Slick complained about the projection.
Now it is your turn!
As you saw, I am not 100% happy with the solution. It is working… yes I know, call me perfectionist… but I am not 100% satisfied with it. Now it is your turn… Can you help me to get that groupBy inside the Slick query? If you have a solution for that or an improvement, leave me a comment. If you liked this post, please subscribe and share it!