SLICK 3.0 - multiple queries depending on one another - db.run(action)

Question

I am new to Slick 3 and so far I have understood that db.run are asynchronous call. the .map or .flatMap is run once the Future is returned.

The problem in my code below is that all the sub queries do not work (nested db.run).

Conceptually speaking, what am I not getting? Is it valid to do this kind of code as below? basically in the .map of the first query I do some actions depending on the first query.

I see everywhere for loops with yield, is it the only way to go? Is the problem in my code related to the returned Future value?

val enterprises = TableQuery[Enterprise]
val salaries = TableQuery[Salary]

//Check if entered enterprise exists 
val enterpriseQS = enterprises.filter(p => p.name.toUpperCase.trim === salaryItem.enterpriseName.toUpperCase.trim).result

val result=db.run(enterpriseQS.headOption).map(_ match 
{
    case Some(n) => {
      //if an enterprise exists use the ID from enterprise (n.id) when adding a record to salary table
      val addSalary1 = salaries += new SalaryRow(0, n.id, salaryItem.worker)
      db.run(addSalary1)
    }
    case None =>  {
        //if an enterprise with salaryItem.enterpriseName doesn't exist, a new enterprise is inserted in DB
        val enterpriseId = (enterprises returning enterprises.map(_.id)) += EnterpriseRow(0, salaryItem.enterpriseName)
        db.run(enterpriseId).map{
            e => {
                val salaryAdd2 = salaries += new SalaryRow(0, e, salaryItem.worker)
                db.run(salaryAdd2)
            }
        }
    }
})

Answer 1

The problem in my code below is that all the sub queries do not work (nested db.run)

I suspect you're ending up with nested Future[R] results. I've not investigated that though. Because...

Conceptually speaking, what am I not getting?

The way I'd tackle this is to look at combining DBIO[R]. That might be the concept that helps.

What you're doing is trying to run each action (query, insert...) individually. Instead, combine the individual actions into a single action and run that.

I'd re-write the main logic as this:

  val action: DBIO[Int] = for {
    existingEnterprise <- enterpriseQS.headOption
    rowsAffected       <- existingEnterprise match {
      case Some(n) => salaries += new SalaryRow(0, n.id, salaryItem.worker)
      case None    => createNewEnterprise(salaryItem)
    }
  } yield rowsAffected

For the None case I'd create a helper method:

  def createNewEnterprise(salaryItem: SalaryItem): DBIO[Int] = for {
    eId          <- (enterprises returning enterprises.map(_.id)) += EnterpriseRow(0, salaryItem.enterpriseName)
    rowsAffected <- salaries += new SalaryRow(0, eId, salaryItem.worker)
  } yield rowsAffected

Finally, we can run that:

  val future: Future[Int] = db.run(action)
  // or db.run(action.transactionally)    

  val result = Await.result(future, 2 seconds)

  println(s"Result of action is: $result")

The second half of a blog post I've written talks more about this.

The code I've used is: https://github.com/d6y/so-31471590