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:

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:

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:

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.

Having this, in the DAO I defined a TableQuery for the categories and the method findChildrenWithParents, which retrieves what I wanted:

What am I doing?

  1. I join the categories with their subcategories and sort everything by the parent category name in result.
  2. I group the result by the parent category.
  3. 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!