Sub Query in select clause with Squeryl

Question

I'm trying to replicate the following query usine Squeryl.

SELECT c.order_number,p.customer,p.base,( 
  SELECT sum(quantity) FROM "Stock" s where s.base = p.base  
) as stock 
FROM "Card" c, "Part" p WHERE c."partId" = p."idField";

I have the following code for selecting the Cards and Parts but I cannot see a way to add a sumation into the select clause.

from(cards, parts)((c,p) => 
where(c.partId === p.id)    
select(c,p)

Any help is much appreciated!

Answer 1

In Squeryl, you can use any Queryable object in the from clause of your query. So, to create a subquery, something like the following should work for you:

def subQuery = from(stock)(s => groupBy(s.base) compute(sum(s.quantity)))

from(cards, parts, subquery)((c, p, sq) =>
  where(c.partId === p.idField and sq.key === p.base)
  select(c.orderNumber, p.customer, sq.measures))

Of course the field names may vary slightly, just guessing at the class definitions. If you want the whole object for cards and parts instead of the single fields from the original query - just change the select clause to: select(c, p, sq.measures)