Skinny group by sample

QueryDSL.scala

    val c = Challenge.syntax("c")
    val cr = ChallengeResult.syntax("cr")
    val list = withSQL {
      select(
        c.result.*,
        sqls"count(cr.id) as count"
      ).from(Challenge as c)
        .leftJoin(ChallengeResult as cr)
        .on(c.id, cr.challengeId)
        .where(buildCondition(user.id, publicOnly))
        .groupBy(c.id)
        .orderBy(c.id).desc
        .limit(limit)
        .offset(offset)
    }.map { rs =>
      val challenge = Challenge.extract(rs, c.resultName)
      val cnt = rs.int("count")
      (challenge, cnt)
    }.list.apply

Raw.scala

    val condition = buildCondition(user.id, publicOnly)
    val c = Challenge.syntax("c")
    val cr = ChallengeResult.syntax("cr")
    val list = sql"""
      SELECT
        ${c.result.*},
        count(cr.id) as count
      FROM challenges c
      LEFT OUTER JOIN challenge_results cr ON (c.id = cr.challenge_id)
      WHERE ${condition}
      GROUP BY c.id
      ORDER BY c.id desc
      LIMIT ${limit} OFFSET ${offset}
    """.map { rs =>
      val challenge = Challenge.extract(rs, c.resultName)
      val cnt = rs.int("count")
      (challenge, cnt)
    }.list.apply