agregation subqueries in slick

Question

I want to use slick-way to subqueries like this:

   select suppliers.*, 
          (select count(1) from cofees 
            where cofees.sup_id == suppliers.sup_id 
          ) 
     from suppliers

and map one to type Seq[(Supplier, Int)]

and also similar request

   select suppliers.*
    where cofees.sup_id == suppliers.id) 
     from suppliers where not exists (select 1 from cofees 
            where cofees.sup_id == suppliers.sup_id)

I found only plain-sql way:

   db.run {sql"""select sup_id, name
    where cofees.sup_id == suppliers.id) 
     from suppliers where not exists (select 1 from cofees 
            where cofees.sup_id == suppliers.sup_id)
   """}

but I want something like

db.run{ 
  for {
    s <- suppliers
    c <- counts(???)
  } yield( s, c)
}

db.run{
  for {
   s <- suppliers if not exists (???)
  } yield(s) 
}

Is it possible?

Answer 1

the first one should work like this - use a left join to also get zero coffees (if that's what you wanted)

  for {
    (s, c) <- suppliers joinLeft coffees on (_.sup_id===_.id)
  } yield(s, c)
  .groupBy(_._1)
  .map { case (s, c) => (s, c.length) }

for second, guess you can add if c.length === 0

Edited as per Adel's suggestion - it was missing a groupBy