How would I perform this SQL query with subselect in Slick?

Question

select
    *,
    (select count(1) from variants as v where v.product_id = p.id) as variants
from
    products as p
;

The query gives the results I want, so I just need to be able to do the same from Slick now. I have Product and Variant models with corresponding Products and Variants tables and products and variants TableQuerys set up.

This is what I ended up with:

products.map(product =>
            (product, variants.filter(_.productId === product.id).length)
        ) map (_ <> (ProductAndVariantCount.tupled, ProductAndVariantCount.unapply))

Answer 1

The basic form would be:

products.map(row => 
  (row.id, row.otherColumnsHere, variants.filter(_.product_id === row.id).length)
)

The way I approach these problems is to turn each part of a query into a Slick expression, and see how they might combine.

In your case, the thinking was:

• the sub-select is, something like table.filter(...).length
• the main select is table.map(...)
• and then see if I can get them to combine in some way.