Subqueries, Having and GroupBy in Slick

Question

I am currently learning Slick. I was trying to translate this query from SQL into Scala:

SELECT name FROM Passenger
WHERE ID_psg in
(SELECT ID_psg FROM Pass_in_trip
GROUP BY place, ID_psg
HAVING count(*)>1)

But I have only managed to write something like this, and it gives compile errors:

PassengerTable.table.filter(_.idPsg in (PassInTripTable.table.map(_.idPsgFk)))
  .filter(PassengerTable.table.count(_.name) > 1)
  .map(_.name)

I do not really know how to apply count and having on queries in Slick. So I would be really grateful for some help.

Answer 1

Try

  val subquery = PassInTripTable.table.groupBy(p => (p.place, p.idPsgFk))
    .map { case ((place, id), group) => (id, group.length) }
    .filter { case (id, count) => count > 1 }
    .map { case (id, count) => id }

  val query = PassengerTable.table.filter(_.idPsg in subquery).map(_.name)

  val action = query.result

  db.run(action)

http://slick.lightbend.com/doc/3.0.0/sql-to-slick.html#groupby

http://slick.lightbend.com/doc/3.0.0/sql-to-slick.html#subquery

http://slick.lightbend.com/doc/3.0.0/sql-to-slick.html#having