Distinct values from query and join with other tables - how to make it work in one DBIO chain/txn

[Peter Coppens]

Hello

Assume

• order_line table with a product_id column pointing to a product_master table and other order_line related information (e.g date, amount, ....)
• a product_master table with some general information and a product_detail table with for each product_master row multiple rows (1 -> many)

I am trying to construct a query such that

• order_line rows are returned between a given date range (that's easy :) )
  
• for all distinct product_id's in the result of the previous query, return related product_master, product_detail rows (actually that is also not difficult)
• Both of these using only on query on the order_line table all wrapped in a single transaction - that part I can't get working

Currently I have something like ('pseudocode')

val orderLines = OrderLines.filter( ol => ol.timestamp between(start,end) )

val distinctProductIds = orderLines.map(_.product_id).distinct.result

val products = distinctProductIds.flatMap ( pids => DBIO.sequence ( distinctProductIds.map ( pid => getProductDetails(pid)))

After 'running' the combined orderLines and products DBIO's the results are fine, but unfortunately this hits the order_line table twice. Is it possible to avoid that but still run within a single transaction

I guess procedural/jdbc it would come down to the following steps

start transaction
select ...,product_id from order_line where timestamp beween ...
fetch resultset in memory
get distinct product_id's from instantiated result_set
for each pid,  select .. from product_master, product_detail where product_master.product_id=pid ....
commit transaction

I know it's easy enough to achieve the same by instantiating the orderLines slick query  but that would terminate the dbio chain (transaction) and I need to avoid that

All tips welcome!

Peter