Slick - Update query with multiple tables

Question

I'm currently facing an issue with my update query in my scala-slick3 project. I have a Report-Class, which contains multiple Products and each Product contains multiple Parts. I want to implement a function that marks every Part of every Product within this Report as assessed.

I thought about doing something like this:

 def markProductPartsForReportAsAssessed(reportId: Int) = {
     val query = for {
         (products, parts) <- (report_product_query filter(_.reportId === reportId) 
                               join (part_query filter(_.isAssessed === false)) 
                               on (_.productId === _.productId))
      } yield parts.isAssessed

      db.run(query.update(true))
 }

Now, when I run this code slick throws this exception: SlickException: A query for an UPDATE statement must resolve to a comprehension with a single table. I already looked at similiar problems of which their solutions (like this or this) weren't really satisfying to me.

Why does slick throw this excpetion or why is it a problem to begin with? I was under the impression that my yield already takes care of not "updating multiple tables".

Thanks in advance!

Bohun Tak Bohun Tak · Accepted Answer · 2019-07-22T01:31:47

I guess it's because the UPDATE query requires just one table. If you write SQL for the above query, it can be

UPDATE parts a SET isAccessed = 'true' 
WHERE a.isAccessed = 'false' and 
exists(select 'x' from products b 
where a.productId = b.producId and b.reportId = reportId)

Therefore, you can put conditions related with 'Product' table in the filter as follows.

val reportId = "123" // some variable
val subQuery = (reportId:Rep[String], productId:Rep[String]) =>
 report_product_query.filter(r => r.report_id === reportId && r.product_id === productId)
val query = part_query.filter(p => p.isAccesssed === false:Rep[Boolean] && 
subQuery(reportId, p.productId).exists).map(_.isAccessed)
db.run(query.update(true))

Slick - Update query with multiple tables

1 Answers