title: Recursive tree-like table query with Slick
body: <p>My table data forms a tree structure where one row can reference a parent row in the same table.</p>
  
  <p>What I am trying to achieve, using Slick, is to write a query that will return a row and all it's children. Also, I would like to do the same, but write a query that will return a child and all it's ancestors.</p>
  
  <p>In other words:</p>
  
  <p><code>findDown(1)</code> should return </p>
  
  <pre><code>List(Group(1, 0, "1"), Group(3, 1, "3 (Child of 1)"))
  </code></pre>
  
  <p><code>findUp(5)</code> should return</p>
  
  <pre><code>List(Group(5, 2, "5 (Child of 2)"), Group(2, 0, "2"))
  </code></pre>
  
  <p>Here is a fully functional worksheet (except for the missing solutions ;-).</p>
  
  <pre><code>package com.exp.worksheets
  
  import scala.slick.driver.H2Driver.simple._
  
  object ParentChildTreeLookup {
  
    implicit val session = Database.forURL("jdbc:h2:mem:test1;", driver = "org.h2.Driver").createSession()
  
    session.withTransaction {
      Groups.ddl.create
    }
  
    Groups.insertAll(
      Group(1, 0, "1"),
      Group(2, 0, "2"),
      Group(3, 1, "3 (Child of 1)"),
      Group(4, 3, "4 (Child of 3)"),
      Group(5, 2, "5 (Child of 2)"),
      Group(6, 2, "6 (Child of 2)"))
  
    case class Group(
      id: Long = -1,
      id_parent: Long = -1,
      label: String = "")
  
    object Groups extends Table[Group]("GROUPS") {
      def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
      def id_parent = column[Long]("ID_PARENT")
      def label = column[String]("LABEL")
      def * = id ~ id_parent ~ label &lt;&gt; (Group, Group.unapply _)
      def autoInc = id_parent ~ label returning id into {
        case ((_, _), id) =&gt; id
      }
  
      def findDown(groupId: Long)(implicit session: Session) = { ??? }
  
      def findUp(groupId: Long)(implicit session: Session) = { ??? }
    }
  
  }
  </code></pre>
  
  <p>A really bad, and static attempt at <code>findDown</code> may be something like:</p>
  
  <pre><code>private def groupsById = for {
    group_id &lt;- Parameters[Long]
    g &lt;- Groups; if g.id === group_id
  } yield g
  
  private def childrenByParentId = for {
    parent_id &lt;- Parameters[Long]
    g &lt;- Groups; if g.id_parent === parent_id
  } yield g
  
  
  def findDown(groupId: Long)(implicit session: Session) = { groupsById(groupId).list union childrenByParentId(groupId).list }
  </code></pre>
  
  <p>But, I'm looking for a way for Slick to recursively search the same table using the id and id_parent link. Any other good ways to solve the problem is really welcome. Keep in mind though, that it would be best to minimise the number of database round-trips.</p>