gistfile1.scala

  def testIssue146 {

    case class Record(id: Int, text: String, categoryId: Int)
    case class Category(id: Int, name: String, toolId: Int)
    case class Tool(id: Int, name: String)

    class RecordTable(tag: Tag) extends Table[Record](tag, "RECORDS") {
      def id = column[Int]("id", O.PrimaryKey)
      def text = column[String]("text")
      def categoryId = column[Int]("category_id")
      def * = (id, text, categoryId) <> (Record.tupled, Record.unapply)
      def category = foreignKey("fk_record_category", categoryId, CategoryTable)(_.id)
    }
    lazy val RecordTable = TableQuery[RecordTable]

    class CategoryTable(tag: Tag) extends Table[Category](tag, "CATEGORIES") {
      def id = column[Int]("id", O.PrimaryKey)
      def name = column[String]("name")
      def toolId = column[Int]("tool_id")
      def * = (id, name, toolId) <> (Category.tupled, Category.unapply)
      def tool = foreignKey("fk_category_tool", toolId, ToolTable)(_.id)
    }
    lazy val CategoryTable = TableQuery[CategoryTable]

    class ToolTable(tag: Tag) extends Table[Tool](tag, "TOOLS") {
      def id = column[Int]("id", O.PrimaryKey)
      def name = column[String]("name")
      def * = (id, name) <> (Tool.tupled, Tool.unapply)
    }
    lazy val ToolTable = TableQuery[ToolTable]

    (ToolTable.ddl ++ CategoryTable.ddl ++ RecordTable.ddl).create
    println("Created tables")

    val toolA = Tool(1, "A")
    val toolB = Tool(2, "B")
    val toolC = Tool(3, "C")

    ToolTable ++= Seq(toolA, toolB, toolC)

    val catA = Category(11, "A", toolA.id)
    val catB = Category(12, "B", toolB.id)
    val catC = Category(13, "C", toolC.id)
    val catX = Category(14, "X", toolA.id)
    val catY = Category(15, "Y", toolB.id)
    val catZ = Category(16, "Z", toolC.id)

    CategoryTable ++= Seq(catA, catB, catC, catX, catY, catZ)

    RecordTable ++= Seq(
      Record(1, "cool things", catA.id),
      Record(2, "uncool", catB.id),
      Record(3, "apples", catA.id),
      Record(4, "oranges", catZ.id),
      Record(5, "bananas", catB.id),
      Record(6, "pears", catX.id),
      Record(7, "limes", catY.id),
      Record(8, "cantelope", catC.id))

    // works fine
    {
      val allRecordsQuery = for {
        r <- RecordTable
        c <- r.category
        t <- c.tool
      } yield (r, c, t)

      allRecordsQuery.run foreach {
        case (r, c, t) => println(s"  r = $r, c = $c, t = $t")
      }
    }

    // works fine
    {
      val q1 = (for {
        r <- RecordTable
        c <- r.category
      } yield (r, c)).groupBy { case (r, c) => c.id }.map {
        case (cid, q) =>
          (cid, q.length)
      }
      q1.run foreach {
        case (cid, count) => println(s"  Category $cid has $count records")
      }
    }

    // should be functionally the same as the previous, but dies with an exception:
    // Unsupported query shape containing .groupBy without subsequent .map
    {
      val q1 = for {
        r <- RecordTable
        c <- r.category
      } yield (r, c)
      val grouped = q1.groupBy { case (r, c) => c.id }
      val query = for {
        (cid, q) <- grouped
      } yield (cid, q.length)
      query.run foreach {
        case (cid, count) => println(s"  Category $cid has $count records")
      }
    }

    // Unexpected node Select _1 -- SQL prefix: select
    {
      val query = (for {
        r <- RecordTable
        c <- r.category
      } yield (r, c)).groupBy { case (r, c) => (c.id, c.name) }.map {
        case ((cid, cname), q) =>
          (cid, cname, q.length)
      }

      query.run foreach {
        case (cid, cname, count) => println(s"  Category $cname ($cid) has $count records")
      }
    }

    // Unexpected LetDynamic after Inliner
    {
      val query = (for {
        r <- RecordTable
        c <- r.category
        t <- c.tool
      } yield (r, c, t)).groupBy { case (r, c, t) => (c.id, c.name, t.id, t.name) }.map {
        case (groupInfo, q) => (groupInfo, q.length)
      }

      query.run foreach {
        case ((cid, cname, tid, tname), count) => println(
          s"  Tool($tid, $tname), Category($cid, $cname) => $count")
      }
    }

    // Unexpected node Apply Function count(*) -- SQL prefix: select x2."id",
    {
      val q1 = (for {
        r <- RecordTable
        c <- r.category
        t <- c.tool
      } yield (r, t, c)).groupBy { case (r, c, t) => t.id }.map {
        case (tid, q) => (tid, q.length)
      }

      q1.run foreach {
        case (id, count) => println(s"  Tool $id has $count records")
      }
    }
  }