Slick 2.0 Efficient Group By and Retrieve Multiple Columns

[Luke Knight]

Hello!

I've been experiencing troubles getting Slick 2.0 to generate efficient SQL when trying to do a LEFT JOIN, COUNT, and GROUP BY when specifically attempting to retrieve more columns than just the GROUP BY column and the COUNT which most examples show. 

Example of the desired SQL (where a.* might be 10-20 manually specified columns):

SELECT a.*, count(b.id)

FROM tableA a LEFT JOIN tableB b ON a.id = b.foreign_id

WHERE a.id = '1234'

GROUP BY a.id

Various attempts at the equivalent Slick code have been as follows:

Using DSL functions (this one includes suggested workaround .max to obtain first row of the group for Slick 2.0):

    tableA leftJoin tableB on (_.id === _.foreign_id) groupBy (_._1.id) map {

         case (id, group) =>

             (id, group.map(_._1.someDesiredColumn).max, group.map(_._2).length)

     }

However this produced some nested SELECT queries and is awkward to write for many columns as it requires repeating that 'group.map(_._1.someDesiredColumn).max' call for each desired column to add.

Join using for comprehensions:

 (for {

       a <- tableA if a.id === "bla".bind

     } yield (a, (for (b <- tableB if b.foreign_id === a.id) yield b).length)).firstOption.map {

       case (rows, count) =>

         SomeCaseClass(rows, count)

     }

However this also produced a nested query to obtain the count, which I suppose is fairly expected by the nested for comprehension.

Any help would be appreciated.

Regards,

Luke

[virtualeyes]

SELECT a.*, count(b.id)

FROM tableA a LEFT JOIN tableB b ON a.id = b.foreign_id

WHERE a.id = '1234'

GROUP BY a.id

Before Slick that could be written as:

for {
  (a, b) <- A join B on (_.foreignKey) if a.id === '1234'
       _ <- Query groupBy a.id
} yield (a, b.id.count)

but Slick dropped fast and loose handling of group by clause (in this case though above statement is valid SQL99+). Pretty common in query DSLs, JOOQ and Quill among others do exactly the same, require that all non-aggregate columns in select statement be included in group by.