Scala Slick 3.0.1 Relationship to self

Question

I have an entity called Category which has a relationship to itself. There are two types of categories, a parent category and a subcategory. The subcategories have in the idParent attribute, the id from the parent category.

I defined the Schema this way

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)
}

And I have this data:

   id      name      idParent
------------------------------
 parent   Parent
 child1   Child1      parent
 child2   Child2      parent

Now I want to get the result in a map grouped by the parent category like

Map(
 (parent,Parent,None) -> Seq[(child1,Child1,parent),(child2,Child2,parent]
)

For that I tried with the following query:

def findChildrenWithParents() = {
  db.run((for {
   c <- categories
   s <- c.subcategories
  } yield (c,s)).sortBy(_._1.name).result)
}

If at this point I execute the query with:

categoryDao.findChildrenWithParents().map {
  case categoryTuples => categoryTuples.map(println _)
}

I get this:

(Category(child1,Child1,Some(parent)),Category(parent,Parent,None))
(Category(child2,Child2,Some(parent)),Category(parent,Parent,None))

Here there are two facts that already disconcerted me:

It is returning Future[Seq[Category, Category]] instead of the Future[Seq[Category, Seq[Category]]] that I would expect.
The order is inverted, I would expect the parent to appear first like:

(Category(parent,Parent,None),Category(child1,Child1,Some(parent))) (Category(parent,Parent,None),Category(child2,Child2,Some(parent)))

Now I would try to group them. As I am having problems with nested queries in Slick. I perform a group by on the result like this:

categoryDao.findChildrenWithParents().map {
    case categoryTuples => categoryTuples.groupBy(_._2).map(println _)
}

But the result is really a mess:

(Category(parent,Parent,None),Vector((Category(child1,Child1,Some(parent)),Category(parent,Parent,None),(Category(child2,Child2,Some(parent)),Category(parent,Parent,None))))

I would have expected:

(Category(parent,Parent,None),Vector(Category(child1,Child1,Some(parent)),Category(child2,Child2,Some(parent))))

Can you please help me with the inverted result and with the group by?

Thanks in advance.

I changed the subcategories condition to: def subcategories = TableQuery[CategoriesTable].filter(c => id === c.idParent) And now I only have the problem with the Parent being repeated on the group by result. Could anyone please tell me how can I get rid of the parent after group by? — Alberto Almagro

Alberto Almagro Alberto Almagro · Accepted Answer · 2015-09-04T11:51:57

Ok I managed to fix it by myself. Here the answer if someone wants to learn from it:

  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))
      }
    }

  }

The solution isn't perfect. I would like to make the group by already in Slick, but this retrieves what I wanted.

Scala Slick 3.0.1 Relationship to self

1 Answers